Azure AD Service Principal authentication to SQL DB - Code Sample

odelmotte You can use plain SqlConnection/SqlCommand to run SQL script with service principal, however in such case you can only use 'normal' SQL syntax, and you cannot have 'GO' keyword in your script since it is a special syntax that only SQLCMD understands. If you need to use 'GO' keyword, you can still use SQL Server Management classes in Powershell. The example below should help (you of course need to provide all required variables). Note we use some Azure CLI commands there for simplicity.

Import-Module -Name SqlServer

# Use Azure CLI to get token, much easier than using Powershell for that
az login --service-principal -u $AppId -p $Secret --tenant $TenantId --allow-no-subscriptions | Out-Null
$accessToken = az account get-access-token --resource https://database.windows.net/ --query "accessToken" --output tsv
$connectionString = "Server=tcp:$ServerName,1433;Initial Catalog=$DatabaseName;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;" 

try {
   # Create the connection object 
   $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
   $connection.AccessToken = $accessToken

   # Opens connection to Azure SQL Database and return connection
   $connection.Open() 

   # Running query with SQL SMO library rather than plain SqlCommand because our scripts contain
   # multiple batches separated with 'GO' keyword, which is not allowed normally in SqlCommand. More info:
   # https://smehrozalam.wordpress.com/2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/
   $svrConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($connection)
   $server = New-Object Microsoft.SqlServer.Management.Smo.Server($svrConnection)
   $server.ConnectionContext.ExecuteNonQuery($CmdText)
   $connection.Close()
} catch {
   Write-Error $_.Exception.ToString()
   throw
}