Azure SQL with Managed Service Identity Sandbox

Introduction

Are you moving from OnPremises to ? Using Managed Identity may help with your legacy applications .

In a previous post, we saw how to use SSO with your current domain by leveraging AD Connect synchronization of your Active Directory with AAD.

But if your AD is not yet synchronized and your application connects with a domain service account? Use SQL ? Use AAD with password?

Following the great post from Sergio Fonseca, Using Managed Service Identity (MSI) to authenticate on Azure SQL DB, explaining in details how Managed Service Identity works with , here's how to set a sandbox and try them in 15 minutes.

Subscriptions

In this example, we'll use a MSDN subscription, providing an Azure Active Directory Free license.

If using a different subscription, ensure that you'll have sufficient administration rights at the Azure Active Directory level.

If you don't have a subscription, you can create a free account here

Provisioning Resources

We'll start by creating the resources required for this sandbox. The following AzCli commands can be executed in your Azure Shell or with Windows Terminal

LOCATION=westeurope
RESOURCEGROUP=ManagedIdentityRG
ADMINNAME=AzureUser
ADMINPWD=

# Resource Group Creation
az group create –name $RESOURCEGROUP –location $LOCATION

# Client1 VM Creation
az vm create –resource-group $RESOURCEGROUP –name Client1 –image win2016datacenter –size Standard_B4ms –admin-username $ADMINNAME –admin-password $ADMINPWD –no-wait

# Client2 VM Creation
az vm create –resource-group $RESOURCEGROUP –name Client2 –image win2016datacenter –size Standard_B4ms –admin-username $ADMINNAME –admin-password $ADMINPWD –no-wait

SERVER=”server-$RANDOM”
DATABASE=”AdventureWorksLT”
ALLOWAZURESERVICE=0.0.0.0

# Azure SQL DB logical server Creation
az sql server create –name $SERVER –resource-group $RESOURCEGROUP –location $LOCATION –admin-user $ADMINNAME –admin-password $ADMINPWD

# Allow Azure Service to access Azure SQL
az sql server firewall-rule create –resource-group $RESOURCEGROUP –server $SERVER -n AllowAZServices –start-ip-address $ALLOWAZURESERVICE –end-ip-address $ALLOWAZURESERVICE

# Azure SQL DB Creation
az sql db create –resource-group $RESOURCEGROUP –server $SERVER –name $DATABASE –sample-name $DATABASE –service-objective Basic

echo “Server Name : $SERVER”

  • If you want to add your public IP

MYIP=X.X.X.X

az sql server firewall-rule create –resource-group $RESOURCEGROUP –server $SERVER -n MyIP –start-ip-address $MYIP –end-ip-address $MYIP

  • Add an Azure Active Directory admin to the Azure SQL

AADADMIN=”XXXXXX@XXXXXXXX.onmicrosoft.com”
AADADMINOBJECTID=$(az ad user show –id $AADADMIN –query objectId -o tsv)

# Set AAD admin for Azure SQL
az sql server ad-admin create –display-name $AADADMIN –object-id $AADADMINOBJECTID –resource-group $RESOURCEGROUP –server $SERVER

  • And finally configure Client1 and Client2

Managed Identities can be used starting with the ODBC driver 17.6, JDBC driver 7.2, OLEDB 18.3

https://docs.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?view=sql-server-ver15

Installing Visual C++ 2017 Redistributable for Visual Studio 2017 is also required.

https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads

# Install VSC++ Redist, ODBC 17.6 Driver on Client1
az vm run-command invoke –command-id RunPowerShellScript –name Client1 –resource-group $RESOURCEGROUP –scripts “New-Item -Path C:Install -ItemType Directory”
“cd /Install”
“Invoke-WebRequest -Uri ‘https://aka.ms/vs/16/release/vc_redist.x64.exe' -OutFile ‘vc_redist.x64.exe'”
“.vc_redist.x64.exe /install /quiet /norestart | Out-Null”
“Invoke-WebRequest -Uri ‘https://go.microsoft.com/fwlink/?linkid=2137027' -OutFile ‘msodbcsql.msi'”
“.msodbcsql.msi /quiet /norestart IACCEPTMSODBCSQLLICENSETERMS=YES”

# Install VSC++ Redist, ODBC 17.6 Driver on Client2
az vm run-command invoke –command-id RunPowerShellScript –name Client2 –resource-group $RESOURCEGROUP –scripts “New-Item -Path C:Install -ItemType Directory”
“cd /Install”
“Invoke-WebRequest -Uri ‘https://aka.ms/vs/16/release/vc_redist.x64.exe' -OutFile ‘vc_redist.x64.exe'”
“.vc_redist.x64.exe /install /quiet /norestart | Out-Null”
“Invoke-WebRequest -Uri ‘https://go.microsoft.com/fwlink/?linkid=2137027' -OutFile ‘msodbcsql.msi'”
“.msodbcsql.msi /quiet /norestart IACCEPTMSODBCSQLLICENSETERMS=YES”

Activating System Assigned Managed Identity

To activate System Assigned Managed identity, navigate to your Client1 VM and click on the Identity in the left pane. On the System Assigned, Set the Status to ON and Save.

image001.png

az vm identity assign –resource-group $RESOURCEGROUP –name Client1

You'll now see the identity assigned to the Client1 VM.

image003.png

In your AAD, a new identity has been created

image005.png

This identity is correlated with your VM lifecycle. If you delete the VM, this identity will be deleted too.

At this point, your VM Client1 has now its own identity and we need to give access to the Azure SQL DB. To do so :

  • Connect to your Azure SQL DB with your AAD admin account to create a user for your VM Client1.

sqlcmd -S “$SERVER.database.windows.net” -G -U $AADADMIN -d master -Q “CREATE USER Client1 FROM EXTERNAL PROVIDER”

sqlcmd -S “$SERVER.database.windows.net” -G -U $AADADMIN -d $DATABASE -Q “CREATE USER Client1 FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER Client1”

  • From Client1 VM, you can test using Powershell

$AzServerName = “”

$AzSqlCn = New-Object System.Data.Odbc.OdbcConnection
$AzSqlCn.ConnectionString = “DRIVER={ODBC Driver 17 for SQL Server};Server=$AzServerName;Database=AdventureWorksLT;Authentication=ActiveDirectoryMsi;”
$AzSqlCn.Open()
$Query = “SELECT @@SERVERNAME AS AzureSQLSrv, DB_NAME() AS AzureSQLDB, HOST_NAME() AS RemoteClient, SYSTEM_USER AS CurrentIdentity”
$OdbcCmd = New-object System.Data.Odbc.OdbcCommand($Query,$AzSqlCn)
$Ds = New-Object System.Data.DataSet
$Da = New-Object System.Data.Odbc.OdbcDataAdapter
$Da.SelectCommand = $OdbcCmd
$Da.Fill($Ds)
$AzSqlCn.Close()
$Ds.Tables[0]

  • If you prefer to continue using Azure Shell

# Create Powershell script
cat <<‘EOF'> script.ps1
param(
[Parameter(Mandatory=$true)][string]$AzServerName
)

$AzSqlCn = New-Object System.Data.Odbc.OdbcConnection
$AzSqlCn.ConnectionString = “DRIVER={ODBC Driver 17 for SQL Server};Server=$AzServerName;Database=AdventureWorksLT;Authentication=ActiveDirectoryMsi;”
$AzSqlCn.Open()
$Query = “SELECT @@SERVERNAME AS AzureSQLSrv, DB_NAME() AS AzureSQLDB, HOST_NAME() AS RemoteClient, SYSTEM_USER AS CurrentIdentity”
$OdbcCmd = New-object System.Data.Odbc.OdbcCommand($Query,$AzSqlCn)
$Ds = New-Object System.Data.DataSet
$Da = New-Object System.Data.Odbc.OdbcDataAdapter
$Da.SelectCommand = $OdbcCmd
$Da.Fill($Ds)
$AzSqlCn.Close()
$Ds.Tables[0]
EOF

# Remotely execute Powershell script on Client1
az vm run-command invoke –command-id RunPowerShellScript –name Client1 –resource-group $RESOURCEGROUP –scripts @script.ps1 –parameters “AzServerName=$SERVER.database.windows.net” –query ‘value[0].message' -o tsv

*Using Run Command increases the execution time, but using it is more convenient here than RDP.

Note, that you don't have to specify Login/Password in the connection string. The authentication option is ActiveDirectoryMsi

The driver will acquire accessToken for establishing a secure connection to the Azure SQL DB

If your application required a user datasource, or if you are not familiar with Powershell, you can try to connect via the ODBC driver UI.

  • Test using the ODBC driver UI. (Create a User Data Source via odbcad32.exe)

image007.png

image009.png

Login ID must be empty.

image011.png

  • Now, Remove the user on Azure SQL DB, on master and AdventureWorksLT.

sqlcmd -S “$SERVER.database.windows.net” -G -U $AADADMIN -d master -Q “DROP USER Client1”

sqlcmd -S “$SERVER.database.windows.net” -G -U $AADADMIN -d $DATABASE -Q “DROP USER Client1”

Activating User Assigned Managed Identity

Another possibility is to create a User Assigned Managed Identity. This lifecycle of this kind of identity is not correlated with your resources.

  • Select Managed Identity in the search bar

image013.png

  • Add one named Client-UAMI

image015.png

az identity create –name Client-UAMI –resource-group $RESOURCEGROUP –location $LOCATION

  • Click Review and Create

Once done, you'll see the newly created identity in your AAD. Client1 is still present as we don't have remove the System Assigned Identity yet

image017.png

We'll now update the Client1 identity.

  • Go to the Client1 on your Azure portal and Click on the Identity on the left pane.
  • On System Assigned, set Status to Off. Then Save. The System Assigned managed Identity will be removed and deleted in your AAD.

image019.png

az vm identity remove –resource-group $RESOURCEGROUP -n Client1

  • Then on User Assigned block, you'll be able to select Client-UAMI. Select it and Click Add

image021.png

az vm identity assign –resource-group $RESOURCEGROUP –name Client1 –identities Client-UAMI

  • Connect to your Azure SQL DB with your AAD admin account to create a user for your VM Client1.

sqlcmd -S “$SERVER.database.windows.net” -G -U $AADADMIN -d master -Q “CREATE USER [Client-UAMI] FROM EXTERNAL PROVIDER”

sqlcmd -S  “$SERVER.database.windows.net” -G -U $AADADMIN -d $DATABASE -Q “CREATE USER [Client-UAMI] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER [Client-UAMI]”

But as this identity is User Managed, you can use it on another client as well.

So, you can assign it to Client2

az vm identity assign –resource-group $RESOURCEGROUP –name Client2 –identities Client-UAMI

and you can now try to connect to your database from Client1 and Client2

# Remotely execute Powershell script on Client1
az vm run-command invoke –command-id RunPowerShellScript –name Client1 –resource-group $RESOURCEGROUP –scripts @script.ps1 –parameters “AzServerName=$SERVER.database.windows.net” –query ‘value[0].message' -o tsv

# Remotely execute Powershell script on Client2
az vm run-command invoke –command-id RunPowerShellScript –name Client2 –resource-group $RESOURCEGROUP –scripts @script.ps1 –parameters “AzServerName=$SERVER.database.windows.net” –query ‘value[0].message' -o tsv

Hope this helps.

Ryad B

Customer Engineer – Data & AI

 

This article was originally published by Microsoft's Core Infrastructure and Security Blog. You can find the original article here.