How to create service principal Azure SQL database user from service principal login (which is an AAD admin of the Azure SQL Server)

Question

We are creating an Azure SQL Server and the database using ARM templates. We have set a AAD group as AAD admin on the SQL Server. That group contains a service principal which we have created under Application registrations.

The deployment succeeds and I can see the AAD admin set correctly for the SQL Server. However when I try to run scripts from the deployment pipeline from that service principal, like create user, it fails.

e.g. this powershell scripts successfully fetches the token and opens the connection, but fails with an error.

$tenant = "xxxx-xxxx-xxxxxx"
$applicationId = $(Get-AzADServicePrincipal -DisplayName "MyServicePrincipal").ApplicationId
$clientSecret = "yyyy-yyyyy-yyyy" 
$subscriptionId = "xyxy-xyxy-xyxy-xyxy"

$secstr = New-Object -TypeName System.Security.SecureString
$clientSecret.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $applicationId, $secstr

Connect-AzAccount -Tenant $tenant -SubscriptionId $subscriptionId -ServicePrincipal -Credential $cred


$resourceAppIdURI = 'https://database.windows.net/'

$authority = ('https://login.windows.net/{0}' -f $tenant)
$ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($applicationId, $clientSecret)
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
$Token = $authResult.Result.AccessToken

$conn = New-Object System.Data.SqlClient.SQLConnection 
$SQLServerName = "sql-myApplicationDbServer-dev.database.windows.net"
$DatabaseName = 'sqldb-myApplicationDb-dev'

$conn.ConnectionString = "Data Source=$SQLServerName;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($Token)
$conn.Open() 
$query = @"
            IF NOT EXISTS (
                SELECT  [name]
                FROM    sys.database_principals
                WHERE   [name] = 'myAppServiceName'
            )
            BEGIN
                CREATE USER [myAppServiceName] FROM EXTERNAL PROVIDER;
                ALTER ROLE db_datareader ADD MEMBER [myAppServiceName];
                ALTER ROLE db_datawriter ADD MEMBER [myAppServiceName];
                ALTER ROLE db_ddladmin ADD MEMBER [myAppServiceName];
            END
        "@
$command = $conn.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteNonQuery()
$result
$conn.Close() 

the error

...
PS C:\Users\TJ> $conn.Open()
PS C:\Users\TJ> $command = $conn.CreateCommand()
PS C:\Users\TJ> $command.CommandText = $query
PS C:\Users\TJ> $result = $command.ExecuteNonQuery()
MethodInvocationException: Exception calling "ExecuteNonQuery" with "0" argument(s): "Principal 'myAppServiceName' could not be resolved. Error message: ''
Cannot add the principal 'myAppServiceName', because it does not exist or you do not have permission.
Cannot add the principal 'myAppServiceName', because it does not exist or you do not have permission.
Cannot add the principal 'myAppServiceName', because it does not exist or you do not have permission."

But, if I add my own AAD user in the SQL Server AAD Administrator group, which the Service principal is part of, I can successfully run the script.

I think service principal needs an associated user and proper rights before i run any create user scripts. how to do that from the service principal login(remember its an admin as its a member of the SQL Server AAD admin group). This would be part of a pipeline so I don't want to run any script with manual intervention.

Answer 1

update(May-2021):it seems that Microsoft has fixed this issue and the workaround is no longer necessary.

So it seems that a service principal does not have enough rights to create users. Microsoft states that they are planning to fix it. For now, there is a workaround.

the trick is to use the syntax

CREATE USER [myAppServiceName] WITH SID=<0xappServiceServicePrincipalSid>, TYPE=E

and it also turns out that the SID that database understands is different from the objectId we get in azure(like in case of MSI). It is a transform of the application service principal that gets created for the appService in my case.

so i need to run this first to get the app Service service principal

$appServiceServicePrincipal = (Get-AzADServicePrincipal -SearchString myAppServiceName).ApplicationId

now we have to convert it to a SID which the db can understand, so run the follwoing in the DB

SELECT CONVERT(VARCHAR(1000), CAST(CAST('$appServiceServicePrincipal' AS UNIQUEIDENTIFIER) AS VARBINARY(16)),1);

and use that value in the SID=<0xappServiceServicePrincipalSid> i mentioned previously

then it works.here is the full sample.

$tenant = "xxxx-xxxx-xxxxxx"
$applicationId = $(Get-AzADServicePrincipal -DisplayName "AspBuildApps").ApplicationId
$clientSecret = "yyyy-yyyyy-yyyy" 
$subscriptionId = "xyxy-xyxy-xyxy-xyxy"
$appServiceName = 'myAppServiceName' #get it from ARM output

$secstr = New-Object -TypeName System.Security.SecureString
$clientSecret.ToCharArray() | ForEach-Object {$secstr.AppendChar($_)}
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $applicationId, $secstr

Connect-AzAccount -Tenant $tenant -SubscriptionId $subscriptionId -ServicePrincipal -Credential $cred

$resourceAppIdURI = 'https://database.windows.net/'

$authority = ('https://login.windows.net/{0}' -f $tenant)
$ClientCred = [Microsoft.IdentityModel.Clients.ActiveDirectory.ClientCredential]::new($applicationId, $clientSecret)
$authContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]::new($authority)
$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $ClientCred)
$Token = $authResult.Result.AccessToken

$conn = New-Object System.Data.SqlClient.SQLConnection 
$SQLServerName = "sql-myApplicationDbServer-dev.database.windows.net"
$DatabaseName = 'sqldb-myApplicationDb-dev'
$conn.ConnectionString = "Data Source=$SQLServerName;Initial Catalog=$DatabaseName;Connect Timeout=30"
$conn.AccessToken = $($Token)

$appServiceServicePrincipal = (Get-AzADServicePrincipal -SearchString $appServiceName).ApplicationId

$conn.Open() 

$query = @"
SELECT CONVERT(VARCHAR(1000), CAST(CAST('$($appServiceServicePrincipal)' AS UNIQUEIDENTIFIER) AS VARBINARY(16)),1);
"@

$command = $conn.CreateCommand()
$command.CommandText = $query
$appServiceServicePrincipalSid  = $command.ExecuteScalar()
Write-Host "appServiceServicePrincipalSid="$appServiceServicePrincipalSid

$query = @"
            IF NOT EXISTS (
                SELECT  [name]
                FROM    sys.database_principals
                WHERE   [name] = '$($appServiceName)'
            )
            BEGIN
                CREATE USER [$($appServiceName)] WITH SID=$($appServiceServicePrincipalSid), TYPE=E
                ALTER ROLE db_datareader ADD MEMBER [$($appServiceName)];
                ALTER ROLE db_datawriter ADD MEMBER [$($appServiceName)];
                ALTER ROLE db_ddladmin ADD MEMBER [$($appServiceName)];
            END
        "@

$command = $conn.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteNonQuery()
Write-Host $result
$conn.Close() 

Thanks @LeonYue for pointing me in the right direction. I want to answer it with a bit of explanation.

Links that helped me:

https://blog.bredvid.no/handling-azure-managed-identity-access-to-azure-sql-in-an-azure-devops-pipeline-1e74e1beb10b

https://roadtoalm.com/2020/01/31/automatically-provision-a-azure-sql-db-with-a-managed-service-identity-msi/