Tag: Azure SQL DB

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

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.

Powershell

$server = 'mytestazuresqldb.database.windows.net'
$database = 'testdb'
$user = 'username@domain.onmicrosoft.com'
$pass = 'password'

$conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$server,1433;Initial Catalog=$database;`
Persist Security Info=False;Authentication=Active Directory Password;User ID=$user;Password=$pass;MultipleActiveResultSets=False;`
Encrypt=False;TrustServerCertificate=True;Connection Timeout=10;")

$conn.Open()

$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM sys.databases", $conn)

$dataset = New-Object System.Data.DataSet
$dataadapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)

[void]$dataadapter.fill($dataset)

Write-Output $dataset.Tables | Format-Table

$conn.Close()

AAD – Service Principal

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.

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.

C#

To connect with C# we need to use Microsoft.Data.Sqlclient package.

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.