We all know that we can use SQL authentication or Azure AD authentication to log on Azure SQL DB. We can also use Azure AD Token authentication or certificate-based authentication, but we will not explore these ones here. In this article we will explore Managed Service Identity (MSI) authentication or system-assigned identity, and how to use it on Azure VM (Using Powershell) or on an Azure Function (.NET).
There are two types of managed identities:
Internally, managed identities are service principals of a special type, which are locked to only be used with Azure resources. When the managed identity is deleted, the corresponding service principal is automatically removed.
Your code can use a managed identity to request access tokens for services that support Azure AD authentication. Azure takes care of rolling the credentials that are used by the service instance.
The following diagram shows how managed service identities work with Azure virtual machines (VMs):
Azure Resource Manager receives a request to enable the system-assigned managed identity on a VM.
Azure Resource Manager creates a service principal in Azure AD for the identity of the VM. The service principal is created in the Azure AD tenant that's trusted by the subscription.
Azure Resource Manager configures the identity on the VM by updating the Azure Instance Metadata Service identity endpoint with the service principal client ID and certificate.
After the VM has an identity, use the service principal information to grant the VM access to Azure resources. To call Azure Resource Manager, use role-based access control (RBAC) in Azure AD to assign the appropriate role to the VM service principal. To call Key Vault, grant your code access to the specific secret or key in Key Vault.
Your code that's running on the VM can request a token from the Azure Instance Metadata service endpoint, accessible only from within the VM: http://169.254.169.254/metadata/identity/oauth2/token
resource=https://management.azure.com/
.A call is made to Azure AD to request an access token (as specified in step 5) by using the client ID and certificate configured in step 3. Azure AD returns a JSON Web Token (JWT) access token.
Your code sends the access token on a call to a service that supports Azure AD authentication.
To assign an Identity to an Azure VM you can just go to Azure Portal and enable it
You will be able to find this identity on Azure Active Directory > Enterprise applications
You can also get and Identity on an Azure Webapp or Azure Function like sample below
DROP USER IF EXISTS [VMSQLCLient]
GO
CREATE USER [VMSQLCLient] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [VMSQLCLient];
ALTER ROLE db_datawriter ADD MEMBER [VMSQLCLient];
GRANT EXECUTE TO [VMSQLCLient]
GO
---------------------------------------------------
DROP USER IF EXISTS [test123fonsecanet]
GO
CREATE USER [test123fonsecanet] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [test123fonsecanet];
ALTER ROLE db_datawriter ADD MEMBER [test123fonsecanet];
GRANT EXECUTE TO [test123fonsecanet]
GO
$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata="true"}
$content = $response.Content | ConvertFrom-Json
$AccessToken = $content.access_token
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=tcp:ServerName.database.windows.net,1433;Initial Catalog=sandbox;Persist Security Info=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
$SqlConnection.AccessToken = $AccessToken
try
{
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT SERVERNAME = @@SERVERNAME, SUSER_SNAME = SUSER_SNAME();"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
Write-Host "Success Connection @@SERVERNAME = $($DataSet.Tables[0].Rows[0].SERVERNAME) / SUSER_SNAME = $($DataSet.Tables[0].Rows[0].SUSER_SNAME)" -ForegroundColor Green
$SqlConnection.Close()
}
catch
{
Write-Host "An error occurred:" -ForegroundColor Red
Write-Host $_ -ForegroundColor Red
}
Success Connection @@SERVERNAME = XXXX / SUSER_SNAME = 96c85223-6377-4f2f-8a6c-8e773fa3b1b2@72f988bf-86f1-41af-91ab-2d7cd011db
* SUSER_SNAME() first part is the Application ID we found on Azure AD
* SUSER_SNAME() second part is your Azure AD Tenant ID
I've create an Azure Function sample app (Function_ConnectSQL_MSI) that you can get it from https://github.com/FonsecaSergio/AzureFunctionAppTestConnectivity
I've used a NuGet called Microsoft.Azure.Services.AppAuthentication to simplify that connection, like sample below:
var azureServiceTokenProvider = new AzureServiceTokenProvider();
string accessToken = await azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/");
sqlConnection.AccessToken = accessToken;
And was able to connect to SQL without the need to provide any login or password
Be aware of some limitations as documented at Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW.
|
|
|
There is now in public preview a solution that was previously limited as seen above. Please check more information on the required steps so you can add new AAD users using a Service Principal
( https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal )
References:
Tks also to Renato Augusto from CSS (AAD team) that worked with me on a case related to this issue
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.