CODEWITHSUNDEEP
azureazure-sql-databasessmssqlcmdazure-service-principal
Varghese
Varghese

Reputation: 21

Azure SQL authentication - Service Principal not working in SSMS/SqlCmd

We have a requirement to connect to Azure SQL Database using Service Principal Details. We are using SSMS(v19) and sqlcmd to connect.

What we done so far:

  1. Create service principal and secret
  2. Assign API Permissions to the service principal
  3. Added service principal to the sql database user as External Provider CREATE USER [******] FROM EXTERNAL PROVIDER ALTER ROLE db_owner ADD MEMBER *****;
  4. Even tried adding the service principal as Entra admin to the Azure SQL Database

After all these , when we try to connect to the database with Service principal details ( name/clientid, secret) using SSMS or SqlCmd. we are getting login failed for user error

Could someone help with this

The background of this requirement is to to create a GitHub action that pulls schema and permissions information from a yaml file and then automatically creates these schemas in the SQL DB with the configured rights.So we need the right to login into the db.

Upvotes: 1

Views: 870

Answers (1)

Bhavani
Bhavani

Reputation: 5297

According to this, it is not possible to connect to an Azure SQL database with a Service Principal in SSMS.

You can use the script below to connect to an Azure SQL database with a Service Principal after successfully creating a Service Principal user and adding the required role to the user:

Import-Module MSAL.PS

$tenantId = "<tenantId>"
$clientId = "<clientId>"      
$clientSecret = "<clientSecret>"   
$scopes = "https://database.windows.net/.default" 
$result = Get-MsalToken -RedirectUri $uri -ClientId $clientId -ClientSecret (ConvertTo-SecureString $clientSecret -AsPlainText -Force) -TenantId $tenantId -Scopes $scopes
$Tok = $result.AccessToken
$SQLServerName = "<servereName>"    
$DatabaseName = "<databaseName>"    
$conn = New-Object System.Data.SqlClient.SQLConnection 
$conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $Tok

$conn.Open() 
$ddlstmt = 'SELECT @@version'
$ddlstmt
$command = $conn.CreateCommand()
$command.CommandText = $ddlstmt

Write-Host "Results:"
$result = $command.ExecuteReader()
while ($result.Read()) {
    $version = $result[0]
    Write-Host "SQL Server Version: $version"
}

Open PowerShell locally and run the script below to check if the Service Principal is able to log into the database. It will connect to the database successfully as shown below:

enter image description here

Upvotes: 0

Related Questions