Qtip: Connect Windows Azure VM to Azure SQL DB using Managed Identity
Published Jun 28 2024 08:54 AM 1,813 Views

In this guide I am going to show steps to connect Windows Azure VM to Azure SQL DB using Managed Identity covering create user in Azure SQL DB , connect using SSMS and connect using powershell

 

Requirements:

Windows 10 or 11 Azure Virtual Machine with system managed identity enabled and admin privileges to run powershell scripts
Azure SQL DB server with entra admin access and database for testing
SQL Server Management Studio (SSMS) latest version

 

Get required information from VM and managed identity:

jaigarcimicrosoft_0-1719561942254.png

Use Object (principal) ID to get Application ID

Go to Entra ID and search Object (principal) ID

jaigarcimicrosoft_1-1719562120663.png

Select result to get Application ID

jaigarcimicrosoft_2-1719562204424.png

 

Provide access to Azure SQL DB:

Connect to server/database using Entra user with admin privileges and create user in this case is the name of the computer

jaigarcimicrosoft_3-1719562387699.png

-- DROP USER [managediddemo] --remove user if exists
CREATE USER [managediddemo] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [managediddemo];

 

Connect from Azure VM:

Connect using SQL Server Management Studio SSMS ...

Open SSMS and provide server name , select authentication Microsoft Entra Managed Identity and user assigned Identity will be Application ID from VM

jaigarcimicrosoft_0-1719562858931.png

In connection properties provide database name otherwise you will receive an error if user is not administrator and finally connect

jaigarcimicrosoft_1-1719562955130.png

jaigarcimicrosoft_2-1719563048606.png

Now is connected

 

Connect using powershell...

 

To be able to connect using powershell you need to Install modules required for Azure

Open powershell as administrator and run commands below

Set-ExecutionPolicy unrestricted
Install-Module -Name PowerShellGet -Force
Install-Module -Name Az -AllowClobber -Scope CurrentUser -force
Install-module SQLServer -force

jaigarcimicrosoft_0-1719563316562.png

Once modules are installed you can close powershell and open again as administrator

 

Get token

Connect-AzAccount -Identity 
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
write-host $access_token

jaigarcimicrosoft_1-1719563516753.png

*In some scenarios token string can be provided directly to avoid round trip each time

 

Test with invoke-sqlcmd

Connect-AzAccount -Identity
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
Invoke-Sqlcmd -ServerInstance <servername>.database.windows.net -Database <dbname> -AccessToken $access_token -query 'select top 10 name from sys.tables'

 -query is the query to run in this case only gets a list of tables in database

jaigarcimicrosoft_2-1719563730859.png

 

Test using Microsoft.Data.SQLClient

import-module Az.Accounts
import-module Microsoft.PowerShell.Security
import-module Microsoft.WSMan.Management
import-module SqlServer
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token
$connectionstring="Server=tcp:<servername>.database.windows.net,1433; Database=<dbname>; Encrypt=True;"
$connection= New-Object Microsoft.Data.SqlClient.SqlConnection
# you can get connection string from azure portal in database overview
$connection.ConnectionString = $connectionstring
$connection.AccessToken=$access_token
$connection.Open()
$command= $connection.CreateCommand()
$command.CommandText = "select top 10 name from sys.tables"
$dataSet = New-Object system.Data.DataSet
$adapter = New-Object microsoft.Data.SqlClient.SqlDataAdapter $command
$adapter.Fill($dataSet) | Out-Null
$connectionid=$connection.clientconnectionid
write-output $connectionid
$dataSet.Tables

jaigarcimicrosoft_0-1719564052692.png

Now your Windows Azure VM is able to connect using different methods

 

More Information

Provision Azure AD admin (SQL Database)
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-po...

 

What are managed identities for Azure resources?

https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview

 

Configure managed identities on Azure virtual machines (VMs)

https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/how-to-configure...

 

Co-Authors
Version history
Last update:
‎Jun 28 2024 01:53 AM
Updated by: