Use a Azure Virtual Machine System-Assigned Managed Identity to access Azure SQL from Powershell

Question

I need to run some bulk operations overnight on an azure sql database.

I have a PowerShell script which uses invoke-sql commands using sql credentials for a service account. Here's an example:

$params = @{
      'Database' = 'test-db'
      'ServerInstance' = 'my-db.database.windows.net'
      'Username' = 'service_account'
      'Password' = 'my exposed password'
      'OutputSqlErrors' = $true
      'Query' = 'SELECT top 10 * FROM dbo.CaseHistory'
}
Invoke-Sqlcmd @params

I want to set this up as a windows scheduled task to run from my VM each night however I don't like having the SQL credentials there in the PowerShell script.

The VM in question has a system-assigned managed identity which I'm already using to access blob storage.

I've already set up a sql user for this identity in azure sql:

CREATE USER [myVmName] FROM EXTERNAL PROVIDER

And I've granted all the required permissions.

How do I use the managed identity from the PowerShell script?

P.S. I'm not precious about sticking with Invoke-Sqlcmd.

since posting the question I found this page with a section right at the bottom on using PowerShell with managed identities docs.microsoft.com/en-us/azure/active-directory/… — Twisted
Hi, Twisted, congratulations you have found the solution. I help you post is as answer, you can think about mark it as answer. This can be beneficial to other community members. You also could post by yourself, I will delete mine, Thank you. — Leon Yue

Leon Yue Leon Yue · Accepted Answer · 2020-04-23T02:01:20

Congratulations Twisted have found the solution:

"I found this page with a section right at the bottom on using PowerShell with managed identities: Tutorial: Use a Windows VM system-assigned managed identity to access Azure SQL"

I help him post it as answer and this can be beneficial to other community members.

Use a Azure Virtual Machine System-Assigned Managed Identity to access Azure SQL from Powershell

1 Answers