Using Managed Identity to read from Azure SQL Database from Azure DevOps PowerShell task

Question

I want to run queries against Azure Sql Database from an Azure DevOps Azure PowerShell task without having to take a trip to Key Vault to get credentials. I am wondering if it's possible to connect in the same way that ADF does using a System Managed Identity?

I've added my AAD credential as an Admin on the Azure Sql Server.

I'm hoping I can do the following:

Obtain the AD user account (managed identity) for Azure DevOps - how do I know which identity to use?

Then I hope I can just run:

CREATE USER [{AZURE_DEVOPS_MANAGED_IDENTITY}] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [{AZURE_DEVOPS_MANAGED_IDENTITY}];

Is this possible?

I have already created a Service Connection in the Azure DevOps project for the subscription where the Azure Sql Database resides in and tried to connect with this code in the Azure PowerShell task:

Import-Module SqlServer
$connString = 'Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=marktestsrvr.database.windows.net;Initial Catalog=test-db;'
Invoke-Sqlcmd -ConnectionString $connString -Query "select count(*) as [NumRows] from staging.TestTable"

When I run that task I get this error:

Login failed for user ''.

Answer 1

@Mark Allison Welcome to Microsoft Q&A Forums.

You will need to acquire and pass the AccessToken as a parameter.
Please check this article for more details.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.