"Assign an Azure AD identity to your server and add Directory Reader permission to your identity" - Error

Question

I am writing a little thing in YAML to assign permissions to a newly built SQL Server.

az account set --subscription $(SubscriptionId)
        Install-Module -Name SqlServer -Force
        $sqlCmd = "CREATE LOGIN [Users-PreProd2] FROM EXTERNAL PROVIDER"
        Write-Host $env:tenantId
        $request = Invoke-RestMethod -Method POST  -Uri "https://login.microsoftonline.com/$env:tenantId/oauth2/token" -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$env:servicePrincipalId; client_secret=$env:servicePrincipalKey } -ContentType "application/x-www-form-urlencoded"
        $access_token = $request.access_token
        Invoke-Sqlcmd -ServerInstance database.windows.net -AccessToken $access_token -query $sqlCmd

I have a group called User-PreProd which i need adding as a 'CREATE LOGIN' in SQL. I have added the Service Prinicipal in the Azure AD Admin group. I can get the token fine and when i assign the token to the invoke-Sqlcmd i get an error saying :

Error message: 'Server identity is not configured. Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity"

I guess the service principal i running this on needs the directory readers role. I am not sure as i have not seen this error before. I need to be correct before passing the request on to my team members.

Is this SQL Server on an Azure VM, or an Azure SQL Database Managed Instance? — David Browne - Microsoft, Jan 11 '22 at 16:25
Now I'm confused. SQL Server on a VM doesn't support AAD auth (yet), and ServerInstance says "database.windows.net". — David Browne - Microsoft, Jan 11 '22 at 17:29
@DavidBrowne-Microsoft - i have removed the server name... I have used a token instead and I can make simple SQL commands work like create user 'jason' passwird 'hello' and it works fine. — Jason_Hough, Jan 11 '22 at 17:38
So it's not SQL Server on an Azure VM. Is it Managed Instance? — David Browne - Microsoft, Jan 11 '22 at 17:39

score 0 · Answer 1 · answered Jan 11 '22 at 18:00

Before you can add AAD identities, the managed instance identity must have the "Directory Readers" role in AAD.

For SQL Managed Instance, the Directory Readers role must be assigned to managed instance identity before you can set up an Azure AD admin for the managed instance.

Directory Readers role in Azure Active Directory for Azure SQL

"Assign an Azure AD identity to your server and add Directory Reader permission to your identity" - Error

1 Answers1