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
Now is working , doublecheck using SSMS ...
*Remember to set default database because is not administrator
Now is connected as appid@tenantid
More information
Tutorial: Create Microsoft Entra users using Microsoft Entra applications
Microsoft Entra service principal with Azure SQL