Lesson Learned #49: Does Azure SQL Database support Azure Active Directory connections using Service Principals?

[Jose_Manuel_Jurado]

First published on MSDN on May 10, 2018
The answer is Yes!.

Just finishing a service request following the instructions placed on this URL but with a limitation:

• Service Principal of the Managed Service Identity is not currently supported.


• It is supported if you register an application in Azure portal > Azure Active Directory > Application registration.

Just to mention that there it not possible to use SQL SERVER Management Studio to connect using Service Principals and you need to use a C# to be able to connect using it.

In summary, once you have created, the service principal just only need to add as a Azure Active Directory User.

1. I created a service principal with name JMAppl

2. Using SQL Server Management Studio and connected with the Azure Active Directory Admin user of my Azure SQL Server I executed the following TSQL in the database that I wanted to add, and it worked correctly.

Example code in PowerShell:

[code language="PowerShell"]
Add-Type -Path "..\Microsoft.IdentityModel.Clients.ActiveDirectory.4.5.0\lib\net45\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
$TenantId = "xxxxx"
$ServicePrincipalAppId = "xxxxx"
$ServicePrincipalPwd = "xxxxxx"
$SqlServerName = "server"
$SqlDatabaseName = "database"

function Get-AzureAcessToken()
{
$Token = $null
Try {
$ResourceAppIdURI = 'https://database.windows.net/'
$Authority = 'https://login.windows.net/' + $TenantId
$ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($ServicePrincipalAppId, $ServicePrincipalPwd)
$AuthContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($Authority)
$AuthResult = $AuthContext.AcquireTokenAsync($ResourceAppIdURI, $ClientCred)
Write-Host $AuthResult.Result.AccessToken
if ($null -eq $AuthResult.Result)
{
$ErrorMsg = "Failed to aquire Azure AD token. {0}" -f $AuthResult.Exception
Write-Error -Message $ErrorMsg
}
$Token = $AuthResult.Result.AccessToken
}
Catch [System.SystemException] {
$ErrorMsg = "Failed to aquire Azure AD token. {0}" -f $PSItem.ToString()
Write-Error -Message $ErrorMsg
throw
}
return $Token
}

function GetSqlQuery(
[string] $Token
)

{
Try {
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = "Server=tcp:$SQLServerName.database.windows.net,1433;Initial Catalog=$SqlDatabaseName;Connect Timeout=30"
$conn.AccessToken = $Token
$conn.Open()
$query = 'SELECT TOP 1 * FROM Table'
$command = $conn.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteNonQuery()
$conn.Close()

}
Catch [System.SystemException] {
Write-Error -Message $PSItem.ToString()
throw
}
}
$Token = Get-AzureAcessToken
GetSqlQuery $Token

[/code]

Example code in C#:

[code language="csharp"]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Configuration;
using Microsoft.IdentityModel.Clients.ActiveDirectory;

namespace DotNetExample
{
class ClsAADToken

{
public void Inicia(int nRows)
{
try
{

string connectionString = GetConnectionString();
//string connectionString="Server=tcp:server.database.windows.net,1433;Initial Catalog=Example;Connect Timeout=30";

using (SqlConnection awConnection = new SqlConnection(connectionString))
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
string Token = GetAccessToken("TenantID", "AppId", "Pwd");

awConnection.StatisticsEnabled = true;

string productSQL = "SELECT top " + nRows.ToString() + "* FROM Table";
SqlDataAdapter productAdapter = new SqlDataAdapter(productSQL, awConnection);

DataSet awDataSet = new DataSet();

awConnection.AccessToken = Token;
awConnection.Open();
productAdapter.Fill(awDataSet, "Table");

IDictionary currentStatistics = awConnection.RetrieveStatistics();

Console.WriteLine("Total Counters: " + currentStatistics.Count.ToString());
Console.WriteLine();

long bytesReceived = (long)currentStatistics["BytesReceived"];
long bytesSent = (long)currentStatistics["BytesSent"];
long selectCount = (long)currentStatistics["SelectCount"];
long selectRows = (long)currentStatistics["SelectRows"];
long ExecutionTime = (long)currentStatistics["ExecutionTime"];
long ConnectionTime = (long)currentStatistics["ConnectionTime"];

Console.WriteLine("BytesReceived: " + bytesReceived.ToString());
Console.WriteLine("BytesSent: " + bytesSent.ToString());
Console.WriteLine("SelectCount: " + selectCount.ToString());
Console.WriteLine("SelectRows: " + selectRows.ToString());
Console.WriteLine("ExecutionTime: " + ExecutionTime.ToString());
Console.WriteLine("ConnectionTime: " + ConnectionTime.ToString());

stopWatch.Stop();

TimeSpan ts = stopWatch.Elapsed;

// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);

Console.WriteLine("RunTime " + elapsedTime);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Press any key to close");
Console.ReadLine();
awConnection.ResetStatistics();
}
}
catch (Exception e)
{
Console.WriteLine("Ups!!" + e.Message);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("Press any key to close");
Console.ReadLine();
}

}
private static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["AADConnectionToken"].ToString();
}
public static string GetAccessToken(string tenantId, string clientId, string clientSecret)
{
var authContextUrl = "https://login.windows.net/" + tenantId;
var authenticationContext = new AuthenticationContext(authContextUrl);
var credential = new ClientCredential(clientId, clientSecret);
var result = authenticationContext.AcquireTokenAsync(resource: "https://database.windows.net/", clientCredential: credential).Result;

if (result == null)
{
throw new InvalidOperationException("Failed to obtain the JWT token");
}

var token = result.AccessToken;
return token;
}
}
}
[/code]
Enjoy!!