QTip: Connect to Azure SQL DB using Entra SPN (service principal)

Microsoft

Feb 02, 2024

Testing steps required to connect to Azure SQL DB using Entra SPN with SSMS and Powershell

In this guide, I am going to outline the steps on how to connect to an Azure SQL database using Entra SPN with tools such as SSMS and PowerShell. This demo covers detailed steps for using an existing user when the token is received correctly. Additionally, the steps cover creating a new user from scratch in case there are issues with the existing user.

Requirements:

Run latest powershell version as administrator
SQL Server Management Studio latest version
Entra SPN to be used

Go to Microsoft Entra -> App registrations and select user to use to connect

Get information required

and secret value

Test if token is received ...

Open powershell as administrator and Install module (this is needed only once)

Install-Module -Name MSAL.PS

Is possible to get token? ...

Import-Module MSAL.PS

$tenantId = "...917"   # tenantID (Azure Directory ID) were AppSP resides
$clientId = "779c12a9-dbd1-4c8a-838e-aa4c20d3071b"   # AppID also ClientID for AppSP     
$clientSecret = "..9"   # Client secret for AppSP 
$scopes = "https://database.windows.net/.default" # The end-point do not modify

$result = Get-MsalToken -RedirectUri $uri -ClientId $clientId -ClientSecret (ConvertTo-SecureString $clientSecret -AsPlainText -Force) -TenantId $tenantId -Scopes $scopes

$Tok = $result.AccessToken
Write-host "token"
$Tok

Token received

Next step is to register display name of SPN and assign required roles ...

* Close SSMS if is opened sometimes if changes are being applied fails to create user

Connect to SSMS using Entra user otherwise is not possible to create user

Drop user if exists

-- first drop user if previously was created is not possible to update information
drop user [demosqlspn]

Create user and assign role

CREATE USER [demosqlspn] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [demosqlspn];
ALTER ROLE db_datawriter ADD MEMBER [demosqlspn];

Confirm that user is created

SELECT name, type, type_desc, CAST(CAST(sid as varbinary(16)) as uniqueidentifier) as appId 
from sys.database_principals WHERE name = 'demosqlspn'

Go back to powershell and test if is possible to connect adding code to connect to Azure SQL DB and run a basic query list of databases from sys.databases

Import-Module MSAL.PS

$tenantId = "...99917"   # tenantID (Azure Directory ID) were AppSP resides
$clientId = "779c12a9-dbd1-4c8a-838e-aa4c20d3071b"   # AppID also ClientID for AppSP     
$clientSecret = "...~cq9"   # Client secret for AppSP 
$scopes = "https://database.windows.net/.default" # The end-point do not modify

$result = Get-MsalToken -RedirectUri $uri -ClientId $clientId -ClientSecret (ConvertTo-SecureString $clientSecret -AsPlainText -Force) -TenantId $tenantId -Scopes $scopes

$Tok = $result.AccessToken
Write-host "token"
$Tok

# test connection to Azure SQL DB using tocken
$AccessToken = $tok
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source =[servername].database.windows.net;Initial Catalog=demodataperf; Encrypt=True;"
$SqlConnection.AccessToken = $AccessToken
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT name, database_id, physical_database_name FROM sys.databases"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
$DataSet.tables

*Remember press enter after paste script