Continuing from the first step of a legacy SSIS migration project – exporting MSDB packages – the next is to export SSISDB projects. As I mentioned in the previous post, Microsoft has included a handy wizard to do this for you since SQL 2016, however it might not be appropriate for you to use, as it wasn’t at my company – we were ditching a lot of of the existing projects, editing some of the others, and wanted a clean slate.
SSISDB projects, unlike their MSDB ancestors, are relatively sophisticated beasts and exported as .ispac (project) files. They can be exported easily via SSMS one at a time – but if you need to export hundreds, it’s not as simple as dumping out a VARBINARY conversion of column data.
Thanks to the SSIS .NET API it’s still pretty easy, though. We can access the SSIS instance, enumerate the folders and projects, then call the GetProjectBytes() function on each to get the .ispac data.
The below Powershell function from the Powershell SSIS repo I’m working on takes an instance name and output directory and outputs all of the projects as .ispac files, creating a subfolder structure in the output directory mirroring the SSISDB folders.
For example, for an SSISDB with three folders – QuarterlyReports, ChurnDataPointlessly, AwfulQueryGreatestHits, each with a Project called IntegrationServicesProject1 – and given an output dir of D:\DBA\, the result would be the following subfolders and files created;
I was recently faced with the task of a fairly massive migration of SSIS packages to SQL 2017, mostly from 2012/2014 but including a few 2008. Cool! 2017 is new, not ‘shiny shiny new’ but ‘still quite shiny, not so many catastrophic early-life bugs’ new, and that’s good. But we decided against the ‘SSISDB upgrade’ feature present in SQL2016+ for reasons, so, ouch, that’s a lot of work. Obviously, I immediately did what any lazy sysadmin worth their salt would do – try to automate the crap out of it.
A few Powershell scripts later and mission mostly complete. So I’ll share them for the handful loads of DBAs potentially having to do this kind of job because their SSIS servers were running critical, bespoke software locked to the server on an ‘if it ain’t broke don’t fix it’ adage from above.
The first one is for exporting packages from MSDB. I feel dirty saying ‘package’ and ‘MSDB’ in the same sentence. Hopefully this blog post is the last time I ever do.
The script below is a function that takes the instance name and either an output directory or DisplayOnly switch. Using the DisplayOnly switch means it just returns a list of folders and package names. Specifying an output directory exports all packages on the instance as .dtsx files by pulling the binary package data from the relevant MSDB tables.
function Export-SSISPackageMSDB
{
param
(
[Parameter(position=0, mandatory=$true)][string]$Instance,
[Parameter(position=1, mandatory=$false)][string]$OutputDir,
[Parameter(position=2, mandatory=$false)][switch]$DisplayOnly
)
Import-Module SqlServer
# Exit if OutputDir not specified without DisplayOnly switch
if(!$DisplayOnly)
{
if(!$OutputDir)
{
Write-Output "Error - must specify OutputDir unless using DisplayOnly switch"
return } if(!(Test-Path $OutputDir)) { Write-Output "Error - invalid path specified in OutputDir" return }
}
# Sanitise path input
if(!$OutputDir.EndsWith('\'))
{
$OutputDir = $OutputDir + '\'
}
$versionquery = "SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(4000))) - 1) AS DECIMAL(9,1)) AS 'Version'"
# Get SQL version, exit if cannot connect to instance to perform this query
try
{
$version = (Invoke-Sqlcmd -ServerInstance $Instance -Query $versionquery -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop).Version
}
catch
{
Write-Output "Error connecting to server."
Write-Output $error[0]
return
}
# Set SSIS table names dependent on version
if ($version -gt 9)
{
Write-Debug "Version -gt 9"
$SSISFoldersTable = 'sysssispackagefolders'
$SSISPackageTable = 'sysssispackages'
}
else
{
Write-Debug "Version -le 9"
$SSISFoldersTable = 'sysdtspackagefolders90'
$SSISPackageTable = 'sysdtspackages90'
}
$PackagesQuery = "WITH cte AS
(
SELECT CAST(foldername AS VARCHAR(MAX)) AS 'FolderPath', folderid
FROM msdb.dbo.$($SSISFoldersTable)
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
UNION ALL
SELECT CAST(c.folderpath + '\' + f.foldername AS VARCHAR(MAX)), f.folderid
FROM msdb.dbo.$($SSISFoldersTable) f
INNER JOIN cte c
ON c.folderid = f.parentfolderid
)
SELECT c.FolderPath, p.name, CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as 'pkg'
FROM cte c
INNER JOIN msdb.dbo.$SSISPackageTable p
ON c.folderid = p.folderid
WHERE c.FolderPath NOT LIKE 'Data Collector%'
UNION
SELECT NULL, p.name, CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS 'pkg'
FROM msdb.dbo.$($SSISFoldersTable) f
INNER JOIN msdb.dbo.$SSISPackageTable p
ON f.folderid = p.folderid
WHERE f.folderid = '00000000-0000-0000-0000-000000000000'"
$PackagesQueryDisplayOnly = "WITH cte AS
(
SELECT CAST(foldername AS VARCHAR(MAX)) AS 'FolderPath', folderid
FROM msdb.dbo.$($SSISFoldersTable)
WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
UNION ALL
SELECT CAST(c.FolderPath + '\' + f.foldername AS VARCHAR(MAX)), f.folderid
FROM msdb.dbo.$($SSISFoldersTable) f
INNER JOIN cte c
ON c.folderid = f.parentfolderid
)
SELECT c.FolderPath, p.name
FROM cte c
INNER JOIN msdb.dbo.$SSISPackageTable p
ON c.folderid = p.folderid
WHERE c.FolderPath NOT LIKE 'Data Collector%'
UNION
SELECT NULL, p.name
FROM msdb.dbo.$($SSISFoldersTable) f
INNER JOIN msdb.dbo.$SSISPackageTable p
ON f.folderid = p.folderid
WHERE f.folderid = '00000000-0000-0000-0000-000000000000'"
Write-Output "SSIS Packages being retrieved;"
if($DisplayOnly)
{
try
{
$packages = Invoke-Sqlcmd -ServerInstance $Instance -Database msdb -Query $PackagesQueryDisplayOnly -QueryTimeout 10 -ConnectionTimeout 10 -ErrorAction Stop
$i = 0 foreach($package in $packages) { Write-Host $package.Folderpath "/" $package.Name $i++ } Write-Output "Total $($i) packages." } catch { Write-Output "Error retrieving packages." Write-Output $error[0] return }
}
else
{
try
{
$packages = Invoke-Sqlcmd -ServerInstance $Instance -Database msdb -Query $PackagesQuery -MaxBinaryLength 100000 -MaxCharLength 10000000
try { $i = 0 foreach($package in $packages) { $package.pkg | Out-File -Force -Encoding ASCII -FilePath ("" + $($OutputDir) + $($package.Name) + ".dtsx") $i++ } Write-Output "$($i) packages successfully written to $($OutputDir)." } catch { Write-Output "Error writing .dtsx files to specified location;" Write-Output $error[0] return } } catch { Write-Output "Error retrieving packages from MSDB;" Write-Output $error[0] return }
}
<#
.SYNOPSIS
Retrieves all SSIS packages from MSDB database.
.DESCRIPTION
Retrieves all SSIS packages from MSDB database, optionally saving the .dtsx package files to
a designated output.
.PARAMETER Instance
Specifies the SQL instance name
.PARAMETER OutputDir
Specifies the full output directory for SSIS packages to be exported to.
.PARAMETER DisplayOnly
Switch parameter that causes function to just output a list of SSIS package folders and names.
.OUTPUTS
No direct outputs from fuction - returns list of SSIS packages or writes .dtsx files.
.EXAMPLE
PS> Export-SSISPackageMSDB -Instance MYSQL2008SERVER -OutputDir "C:\DBA\SSIS\Export\"
Exports all SSIS packages from MYSQL2008SERVER as .dtsx files to C:\DBA\SSIS\Export\
.EXAMPLE
PS> Export-SSISPackageMSDB -Instance MYSQL2008SERVER -DisplayOnly
Displays list of SSIS packages on MYSQL2008SERVER
#>
}
On-prem, connecting to SQL Server with AD authentication from Powershell or .NET code is an easy and long-established task – Invoke-SqlCmd just works and .NET SqlConnections support Integrated Security=SSPI in a connection string – just run your code with an authorised service account et voila. With Azure SQL DB, although SQL authentication remains simple, Azure Active Directory introduces additional complexity.
I recently spent a not-insignificant amount of time figuring out the methods and limitations of doing this as part of the never-ending DBA quest for automation, not helped by somewhat patchy documentation and not being able to find a central resource on the topic. So I thought I’d try and distil this information into one post covering everything. I’ll demo examples for connecting via AAD with either Powershell or .NET (using C#). Note – I’ve omitted try/catch logic from the code snippets for clarity, of course they should be used when acquiring tokens, connecting, running queries etc.
Auth Type
Powershell
NET (C#)
SQL
Yes, with SqlServer module
Yes, with System.Data.SqlClient
AAD – User Account
Yes, with System.Data.SqlClient
Yes, with System.Data.SqlClient
AAD – Service Principal
Yes, with ADAL.PS* and System.Data.SqlClient
Yes, with ADAL.NET*
AAD – User w/ MFA
No…well, kinda
Yes, with Microsoft.Data.SqlClient
*I know, ADAL has been superceded by MSAL, I’ll cover that next time.
SQL Authentication
I won’t go over this as it’s not AAD and has long been well-documented. It’s also long been a security risk compared to (A)AD authentication. Which is why I find it disappointing MS have made SQL logins compulsory for Azure SQL DB (exporting/importing databases), frequently used them as primary examples of connectivity to Azure SQL DB and lagged on AAD support in aspects like the SqlServer Powershell module or lack of sysadmin server role…. but I digress. I daresay the ‘virtual master’ underlying architecture will have made things complicated in this regard.
AAD – User account
If you have an AAD user account without MFA enabled, we can use the existing System.Data.SqlClient class. We just need to specify Authentication=Active Directory Password and pass a User ID and Password in the connection string. I’ll only show a Powershell example for this as usage of the .NET classes is identical.
We can use a Service Principal as a pseudo-service account – secured by either a password (‘Client Secret’) or certificate. Instructions on the ‘App Registration’ process are here – just follow the steps to create an App Registration and a Client Secret to go with it (make note of this before saving!), skipping the RBAC section as we don’t need to give the account RBAC permissions as we are just connecting to SQL DB. Make note of the Display Name, Application (client) ID, Client Secret and Directory (tenant) ID.
Then we need to add the account as a SQL DB user, using the EXTERNAL PROVIDER syntax. Example below assumes the Display Name of the service principal is MyTestServicePrincipal;
CREATE USER [MyTestServicePrincipal] FROM EXTERNAL PROVIDER
Now to connecting…
Powershell
To connect using a Service Principal with Powershell you’ll need to install the ADAL.PS module, a community-developed wrapper for the ADAL.NET library. Github and Powershell Gallery links.
Import-Module ADAL.PS
Import-Module SQLServer
# Variables for Azure AD Authentication.
# The Tenant ID is available in 'Tenant Properties' in Azure Portal or retrievable via Get-ADSubscription for the required subscriptions.
# The Client ID and Client Secret are taken from the Service Principal under App Registrations in the Portal.
# The SQLResourceURL is standard for any Azure SQL DB connection, at least for now.
$TenantID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
$ClientID = 'cccccccc-cccc-cccc-cccc-cccccccccccc'
$ClientSecret = 'verylongsecretkey'
$SQLResourceURL = 'https://database.windows.net/'
# Combine the standard AAD authorisation URL with Tenant ID to get the correct URL for authentication
$AADAuthorityURL = 'https://login.windows.net/' + $TenantID
# Client Secret needs to be in secure form, so convert from plain text
$ClientSecretSecure = ConvertTo-SecureString $ClientSecret -AsPlainText -Force
# Create a standard ConnectionString
$SQLServerURL = "mytestazuresqldb.database.windows.net"
$Database = "testdb"
$ConnectionString = "Server=tcp:$($SQLServerURL),1433;Initial Catalog=$($Database);Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False"
# Acquire an AccessToken
$AuthResult = Get-AdalToken -Authority $AADAuthorityURL -Resource $SQLResourceURL -ClientId $ClientID -ClientSecret $ClientSecretSecure
$token = $AuthResult.AccessToken
# Create a SqlConnection using the connection string and our AccessToken
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$SQLConnection.AccessToken = $token
# Connect to Azure SQL DB
$SQLConnection.Open()
$query = "SELECT * FROM sys.databases"
$command = New-Object System.Data.SqlClient.SqlCommand($query, $SQLConnection)
$reader = $command.ExecuteReader()
while($reader.Read())
{
Write-Output $reader[0]
}
C#
To connect with C# we can just use the ADAL.NET library itself. Github and NuGet links.
ADAL.NET as it appears in NuGet Package Manager.
using System;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Data.SqlClient;
namespace AzureSQL_ServicePrincipal_Test
{
class Program
{
static void Main(string[] args)
{
string authorityURL = "https://login.windows.net/";
string resourceURL = "https://database.windows.net/";
// SQL DB and Service Principal variables
string serverName = "mytestazuresqldb.database.windows.net";
string databaseName = "testdb";
string clientID = "cccccccc-cccc-cccc-cccc-cccccccccccc";
string tenantID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
string clientSecret = "verylongsecretkey";
// Add Tenant ID to authority URL
authorityURL += tenantID;
string sqlConnectionString = String.Format("Data Source=tcp:{0},1433;Initial Catalog={1};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False", serverName, databaseName);
// Create an AuthenticationContext from our authorisation URL, and a ClientCredential from our Client ID and Secret
AuthenticationContext authenticationContext = new AuthenticationContext(authorityURL);
ClientCredential clientCredential = new ClientCredential(clientID, clientSecret);
Console.WriteLine("Trying to acquire token...");
// Call AcquireTokenAsync to get our token
AuthenticationResult authResult = authenticationContext.AcquireTokenAsync(resourceURL, clientCredential).Result;
Console.WriteLine("Token acquired.");
// Now try to connect to Azure SQL
using (var sqlConn = new SqlConnection(sqlConnectionString))
{
sqlConn.AccessToken = authResult.AccessToken;
sqlConn.Open();
var sqlCmd = new SqlCommand("SELECT * FROM sys.databases;",sqlConn);
var reader = sqlCmd.ExecuteReader();
while(reader.Read())
{
string result = reader.GetString(0);
Console.WriteLine(result);
}
}
Console.WriteLine("Success!");
Console.ReadKey();
}
}
}
AAD with MFA
Multi-factor authentication introduces a prompt to login to your Azure tenancy with some sort of additional authentication measure like a text to a mobile phone.
Powershell
I’ve not found an easy way to connect via Powershell using MFA. It’s not supported by the SqlServer module in Invoke-SqlCmd nor in System.Data.SqlClient. It appears to be possible to use the new Microsoft.Data.SqlClient assembly (see C# example below), but there is no method of easily referencing this library due to its dependencies.
Microsoft.Data.SqlClient as it appears in NuGet Package Manager
Note that at the point of opening the connection, a browser window should open where we can enter MFA credentials. Once this is complete the rest of the code will execute.
using System;
using Microsoft.Data.SqlClient;
using System.Text;
namespace ConnectToAzureSQLDB
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("ConnectToAzureSQLDB");
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "mytestazuresqldb.database.windows.net";
builder.InitialCatalog = "master";
builder.Authentication = SqlAuthenticationMethod.ActiveDirectoryInteractive;
builder.ConnectTimeout = 30;
Console.WriteLine(builder.ConnectionString);
Console.WriteLine("Connecting to server - authentication prompt should open in browser window...");
using (SqlConnection sqlconn = new SqlConnection(builder.ConnectionString))
{
SqlCommand sqlcmd = new SqlCommand("SELECT * FROM sys.databases", sqlconn);
sqlconn.Open();
var reader = sqlcmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
}
Console.WriteLine("Success");
Console.ReadKey();
}
}
}
And that’s enough connecting to Azure SQL for today. I’ll update/create a further post on this topic about using the new Active Directory authentication library MSAL.NET – and its Powershell wrapper module – once I’ve taken a proper look at it.
