How to manage Synapse Serverless SQL Pool AAD accounts using Azure DevOps pipelines

Question

I'm trying to automate a Synapse deployment where the applications could connect to Synapse Serverless SQL Pool databases using managed identities. I have verified that the process works if I login to the database using an AAD account who belongs to the Synapse Administrators group. With that account, I'm able to create users using FROM EXTERNAL PROVIDER syntax. But how could I use DevOps yaml deployment tasks to achieve the same thing?

I've tried using a service principal belonging to the Synapse Administrators group, and then fetching an access token using Powershell, but to no avail.

$request = Invoke-RestMethod -Method POST -Uri $tokenUrl -Body @{ resource="https://database.windows.net"; grant_type="client_credentials"; client_id=$ClientId; client_secret=$ClientSecret } -ContentType $contentType

$access_token = $request.access_token

Invoke-Sqlcmd -ServerInstance "$synapseServerless-ondemand.sql.azuresynapse.net" -Database $Database -AccessToken $access_token -query $query

Response from Invoke-Sqlcmd command:

Invoke-Sqlcmd: Cannot open database

Answer 1

Hi

Thanks for reaching out to Microsoft Q&A.

Yes you will need to add the SP into the database as a user in order to access the synapse database.

https://thomasthornton.cloud/2020/10/06/query-azure-sql-database-using-service-principal-with-powershell/

Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.