Tags: Azure, Managed Identity, SQL Server Access Token, Managed Service Identity, SQL Server Authentication, App Registrations
This document will outline the concepts needed to connect to a "Secure" Azure SQL Server database, both from within Azure and outside of Azure. It also provides code snippets to demonstrate what is involved.
Today's best practice is to access SQL databases with OAuth tokens. To connect to an Azure SQL DB, you get an OAuth token, then supply it when connecting to the SQL Database. Getting the OAuth token is different whether you're accessing it in Azure or outside Azure.
Today the use of SQL Server credentials from outside the database is frowned upon due to security issues.
The current model now requires connecting to an OAuth endpoint, receiving an Authentication Token, and supply the token when connecting to the database.
This allows authentication and authorization to occur separate from the database, which has important security ramifications.
When setting up applications to authenticate to a database in Azure, there are two authentication resources you should understand.
- Managed Identities - used when authenticating inside Azure
- App Registration - used when accessing resources from outside Azure
To understand permissions in Azure, you should understand what a "Service Principal" is.
Think of a Service Principal as a "Service Account", or a 'user' account which is used by machines.
Both "Managed Identities" and "App Registrations" will be creating and using Service Principals under the hood. The two resources just provide different additional functionality.
A common challenge running applications in the cloud is how to manage credentials for an application to access other resources. The solution is to store credentials in a Key Vault. But how does the application access the Key Vault!?!? Its a classic bootstrap problem. (Reference 1, Reference 2)
This is where a 'managed idenity' in Azure comes into play. When a resource is created, say a Function (a Lambda in AWS), it can be assigned a 'managed idenity'. That identity can then be given access to a set of resources - say a SQL Database.
When the Function starts, it can make an HTTP call to an internal Azure IP address and get an OAuth token. That token can then be used to authenticate to the Key Vault or SQL Server database.
The reason this is possible is that Azure knows the origin of the HTTP request, and so can safely return a token based on the requestor's access rights.
There are two types of Managed Identities:
- System Assigned Identity
- User Assigned Identity
The simplest way of setting up a Managed Identity is to configure a resource to have a "System Assigned" identity.
You can think of a "System Assigned" resource as if a user explicitly created for that resource (Function), then that 'user' can be given access to any resource it needs.
There are two important considerations with a System Assigned identity:
- There is a one to many relationship for this assignment - one resource gets access to multiple resources.
- When the resource (Function) is deleted, the system assigned managed identity is deleted also.
As an example, to configure an Azure Function:
- Open the Function in the Azure portal
- Under Settings, select the "Identity" option
- Select "System Assigned" tab
- Change "Status" to "On"
- Save
This resource now has a Service Principal which can be given access to other resources.
Here's an example of how to give the Function access to a Key Vault:
- Open the KeyVault in Azure Portal
- Open "Access control (IAM)"
- Under "Check access" enter the name of the function, you should see your function there
- To add rights, select the "Add" button and follow the instructions
Your function now has rights to access the Key Vault.
A "User Assigned" identity is slightly more complicated, but also more flexible.
A "Managed Identity" resource is created in Azure Active Directory. That resource now can be assigned to multiple resources (Functions) to represent their identity. Also the Managed Identity can be given access to access other resources, such as a Key Vault.
This process involves more pieces: creation of managed identity, assigning to resources, giving access to managed identity.
But the benefits are:
- managed identity resource does not get deleted when a resource is deleted and
- it supports a many to many authorization pattern, so many resources (Functions) can be tied to one managed identity and it can be given permission to many resources (Key Vault)
To create a User Assigned identity:
- Open the Azure portal
- Search for "Managed Identities"
- Select "Add"
- Supply a name and the Resource Group
- Create the Identity
The following code illustrates using Managed Identity to get an access token, and opening a database connection with that token.
using System.Data;
using Microsoft.Data.SqlClient;
using System.Net.Http;
using System.Text.Json;
class Program
{
async static void Main(string[] args)
{
var dbServer = "<Database Server>";
var dbName = "<Databse Name>";
var conn = $"Server={dbServer};Initial Catalog={dbName};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False";
// Get Access Token
var http = new HttpClient();
// This apparently is a fixed IP in Azure - should not change
var url = "http://169.254.169.254/metadata/identity/oauth2/token?api-version=2019-03-11&resource=https://database.windows.net/";
var request = new HttpRequestMessage(HttpMethod.Get, url);
request.Headers.Add("Metadata", "true");
var response = await http.SendAsync(request, HttpCompletionOption.ResponseContentRead);
var responseContent = await response.Content.ReadAsStringAsync();
if (response.StatusCode != System.Net.HttpStatusCode.OK)
{
throw new Exception($"Failed to receive identity access token. HttpStatus: '{response.StatusCode}', HttpBody: '{responseContent}'");
}
var jsonDictionary = JsonSerializer.Deserialize<Dictionary<string, string>>(responseContent);
var token = jsonDictionary["access_token"];
// Make Database Request
var dbConnection = new SqlConnection();
dbConnection.ConnectionString = conn;
dbConnection.AccessToken = token;
using (var con = dbConnection)
{
try
{
con.Open();
Console.WriteLine($"Opened");
}
catch (SqlException e)
{
if (e.Message == "Login failed for user '<token-identified principal>'.")
{
Console.WriteLine($"Error: '{e.Message}'. Problem: 'This error mean the user is invalid, usually related to a AAD user that does not have user created on SQL DB that you are trying to connect (User DB or Master DB) or that the user is not the AAD Server Admin'. Source: https://techcommunity.microsoft.com/t5/azure-database-support-blog/aad-auth-error-login-failed-for-user-lt-token-identified/ba-p/1417535 ");
}
}
catch (Exception e)
{
Console.WriteLine($"Error: '{e.Message}', '{e.StackTrace}' ");
}
}
}
}There are two types of managed identities. There are management tradeoffs between them, but they provide the same functionality.
Key Point: Managed Identities are used INSIDE Azure.
App Registrations are used when configuring an Azure Resource to be available OUTSIDE of Azure.
There are many ways to configure an App Registration, but once created an App Registration allows you to request an OAuth bearer token, which can be used to access any resources that App Registration has been given access to.
In OAuth terminology, it is possible to create an App Registration with a grant type of "Client Credentials". This is generally whats used for Machine to Machine communication.
To create an App Registration:
- Go to Azure Active Directory in the Azure Portal
- Under Manage, select the "App registrations"
- Select "New Registrations"
- Provide a name (leave the defaults)
- Click "Register"
You now have an App Registration. To get an Authentication Token, you'll need some further information:
- Open the App Registration
- In the "Overview", find the "Application (client) ID" and "Directory (tenant) ID", save those values
- Under "Manage", select "Certificates & secrets"
- Click "New client secret"
- Create a new secret
- Once created, copy the secret (it will disappear once you leave the page)
You now should have a "Tenant Id", "Client Id", and "Client Secret". These are the primary components needed for OAuth "Client Credentials" grant flow.
To get a token, the following bash commands can be run - once you supply the required values.
TENANT_ID=<Tenant Id>
CLIENT_ID=<Client Id>
CLIENT_SECRET=<Client Secret>
# Needed when accessing an Azure Database
SCOPE=https%3A%2F%2Fdatabase.windows.net%2F.default
curl -i -XPOST \
-H "Content-Type: application/x-www-form-urlencoded" \
--data "client_id=$CLIENT_ID&scope=$SCOPE&client_secret=$CLIENT_SECRET&grant_type=client_credentials" \
https://login.microsoftonline.com/$TENANT_ID/oauth2/v2.0/tokenThat should return a JSON blob. Extract the access_token property. Supply the access_token, database server, and database name to the follwoing code.
using System.Data;
using Microsoft.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
var token = "<Access Token>";
var dbServer = "<Database Server>";
var dbName = "<Databse Name>";
var conn = $"Server={dbServer};Initial Catalog={dbName};Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False";
var dbConnection = new SqlConnection();
dbConnection.ConnectionString = conn;
dbConnection.AccessToken = token;
using (var con = dbConnection)
{
try
{
con.Open();
Console.WriteLine($"Opened");
}
catch (SqlException e)
{
if (e.Message == "Login failed for user '<token-identified principal>'.")
{
Console.WriteLine($"Error: '{e.Message}'. Problem: 'This error mean the user is invalid, usually related to a AAD user that does not have user created on SQL DB that you are trying to connect (User DB or Master DB) or that the user is not the AAD Server Admin'. Source: https://techcommunity.microsoft.com/t5/azure-database-support-blog/aad-auth-error-login-failed-for-user-lt-token-identified/ba-p/1417535 ");
}
}
catch (Exception e)
{
Console.WriteLine($"Error: '{e.Message}', '{e.StackTrace}' ");
}
}
}
}