How to connect Azure SQL server database using managed identity in Powershell

Question

Connecting to Azure SQL database in C# is working fine, but i need to connect Azure SQL server database using managed identity in PowerShell.

I don't helpful commands to achieve this. Any help would be greatly appreciated.

Answer 1

Try using Invoke-SqlCmd cmdlet from the SqlServer module with an Azure AD access token.

1. Ensure the Azure SQL Database is Configured for Azure AD Authentication
   • Enable Azure AD authentication on your Azure SQL Database.
   • Add the Managed Identity (System-assigned or User-assigned) as a user in the database with the necessary permissions.
2. Authenticate the Managed Identity
   Use the Connect-AzAccount cmdlet to ensure your PowerShell session is running under the Managed Identity or obtain the token directly using Get-AzAccessToken.
3. Ensure Required Modules are Installed
   Install the Az and SqlServer modules if not already installed:

      Install-Module -Name Az -Scope CurrentUser -Force
      Install-Module -Name SqlServer -Scope CurrentUser -Force
   

4. Generate an Access Token for Azure SQL
   Use the following command to fetch an access token for Azure SQL Database:

      $token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
   

5. Use the Access Token with Invoke-SqlCmd
   Pass the token as part of the connection string:

      Invoke-SqlCmd -ServerInstance "<your_server_name>.database.windows.net" `
                    -Database "<your_database_name>" `
                    -AccessToken $token `
                    -Query "SELECT TOP 10 * FROM YourTable"
   

Ensure the Managed Identity has been granted Database User Access with roles like db_datareader, db_datawriter, or db_owner, as needed.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

Adding to top of what Marcin stated via Token,

you can directly use Az Connect command as stated below :

Assuming you have an Azure offering with powershell as your language like Azure function or Azure automation with managed idnetity enabled,

You can use below command :

Connect-AzAccount -Identity -SubscriptionName "<<name>>"

And then access your database and use any powershell commands/REST APIs