Failing to connect to Azure SQL database (PowerShell) via a service principal account

Question

I'm trying to establish a connection to an Azure SQL Database via a service principal (through a PowerShell script).

I believe I have followed the guidance issued by Microsoft but the connection cannot be established. My current configuration is:

• Azure SQL Server has an AAD administrator set up (AAD group)
• The AAD Group contains a service principal account (manually created)
• Azure SQL Server has a system managed identity (and has the Directory Reader role assigned to it)

It's my understanding that I do not need to explicitly execute CREATE USER for the service principal as it is already a member of the AAD Group which is an admin on the Azure SQL Server.

I've tried Azure CLI and AZ module commands, both to no avail. As an example:

az login --service-principal -u "***" -p="***" --tenant "***"
$accessToken = az account get-access-token --resource https://database.windows.net --query accessToken
Invoke-Sqlcmd -ServerInstance "***.database.windows.net" -Database "***" -AccessToken $accessToken -Query "SELECT GETUTCDATE()"

The above generates an error on Invoke-Sqlcmd Invoke-Sqlcmd: Login failed for user '<token-identified principal>'

I've also tried sanitising the access token (as noticed it started/ended with "), and in doing so the error was broadly the same, however it did report the actual service principal (by ID)

$accessToken = $accessToken -replace """", ""

Produced the error Invoke-Sqlcmd: Login failed for user '***@***'

Answer 1

Issue was caused because of the role assignments "Reader role" to the service principal

While establish a connection to an Azure SQL Database via a service principal two things we need to do

1. Add contributor role to Service Principal Id

2. Allow port 1433 from the sql server firewall with allowed network IP range.

3. Upon role assignment tested with Azure Data Studio. Able to connect with out any issues. NOTE: Make sure firewall rule need to allow along with 1433 port.

Updates:

4. Need to create a user with service principal name on database along with db_owner role

   create user from EXTERNAL PROVIDER ALTER ROLE DB_OWNER ADD MEMBER servicePrincipalName>

5. Service Principal App Permissions needs to provide from Azure Active Directory to access via service principal account

Please refer to Thomas Thornton article.

Query Azure SQL Database using Service Principal with PowerShell – Thomas Thornton