Azure Active Directory (Azure AD) is a central user ID management technology that operates as an alternative to SQL Server authentication.
As new Azure Active Directory managed identity authentication method was added in ODBC Driver version 17.3.1.1 for both system-assigned and user-assigned identities. Lot of our customers would like to know if it is supported to connect SQL database using managed identity in App Service.
First of all, we need to find out what is the ODBC(msodbcsql17.dll) version in App Service. It could be found via below steps:
- Go to Kudu site of your app service.
- Go to Debug console > CMD > System Drive
- Click on Windows > System32 folder
- You may get error that too many items in this directory. This can be accomplished by hitting F12 in your browser and execute the command: window.localStorage['maxViewItems'] = 3000 in the Console.
- Refresh the page and go back to System32 folder again. The error is gone.
- Find msodbcsql17.dll and download it to local.
- Right click and check the property details to know the version. Unfortunately, it is still in version 17.0.1.1 which doesn’t support MSI authentication yet.
- If we try to use the sample code here: https://docs.microsoft.com/en-us/sql/connect/php/azure-active-directory?view=sql-server-ver15#using-the-user-assigned-managed-identity-with-pdo_sqlsrv-driver, we will get error: SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]Invalid value specified for connection string attribute 'Authentication'. Root cause is listed above that ODBC version in App Service doesn't support managed identity authentication.
As an alternative solution, we could use managed identity generated Access Token to connect Azure SQL database. It can be achieved in below steps:
Setp1:
The Access Token can be obtained via REST API calls or Azure SDK (Microsoft.Azure.Services.AppAuthentication). Unfortunately, the SDK is currently available only for .NET, Java, Python and PowerShell apps. For PHP apps, we could use REST API calls.
- An app with a managed identity has two environment variables defined:
- IDENTITY_ENDPOINT - the URL to the local token service.
- IDENTITY_HEADER - a header used to help mitigate server-side request forgery (SSRF) attacks. The value is rotated by the platform.
To get a token for a resource, make an HTTP GET request to this endpoint, including the following parameters:
|
Parameter name |
In |
Description |
|
resource |
Query |
The Azure AD resource URI of the resource for which a token should be obtained. This could be one of the Azure services that support Azure AD authentication or any other resource URI. |
|
api-version |
Query |
The version of the token API to be used. Please use "2019-08-01" or later (unless using Linux Consumption, which currently only offers "2017-09-01" - see note above). |
|
X-IDENTITY-HEADER |
Header |
The value of the IDENTITY_HEADER environment variable. This header is used to help mitigate server-side request forgery (SSRF) attacks. |
|
client_id |
Query |
(Optional) The client ID of the user-assigned identity to be used. Cannot be used on a request that includes principal_id, mi_res_id, or object_id. If all ID parameters (client_id, principal_id, object_id, and mi_res_id) are omitted, the system-assigned identity is used. |
|
principal_id |
Query |
(Optional) The principal ID of the user-assigned identity to be used. object_id is an alias that may be used instead. Cannot be used on a request that includes client_id, mi_res_id, or object_id. If all ID parameters (client_id, principal_id, object_id, and mi_res_id) are omitted, the system-assigned identity is used. |
|
mi_res_id |
Query |
(Optional) The Azure resource ID of the user-assigned identity to be used. Cannot be used on a request that includes principal_id, client_id, or object_id. If all ID parameters (client_id, principal_id, object_id, and mi_res_id) are omitted, the system-assigned identity is used. |
- A successful 200 OK response includes a JSON body with "access_token" properties.
<?php
//get environement variables
$identityEndpoint = getenv("IDENTITY_ENDPOINT");
$identityHeader = getenv("IDENTITY_HEADER");
$tokenAuthURI = "$identityEndpoint?resource=https://database.windows.net&api-version=2019-08-01";
//Create a stream
$opts = array(
'http'=>array(
'method'=>"GET",
'header'=>"X-IDENTITY-HEADER: $identityHeader"
)
);
$context = stream_context_create($opts);
// Open the file using the HTTP headers set above
$file = file_get_contents($tokenAuthURI, false, $context);
if($file)
{
$array = json_decode($file, true);
$accToken = $array['access_token'];
}
?>
Step2:
Use the Access Token to connect SQL database.
<?php
// Using an access token to connect: do not use UID or PWD connection options
// Assume $accToken is the valid byte string extracted from an OAuth JSON response
$connectionInfo = array("Database"=>$azureAdDatabase, "AccessToken"=>$accToken);
$conn = sqlsrv_connect($azureAdServer, $connectionInfo);
if ($conn === false) {
echo "Could not connect with Azure AD Access Token.\n";
print_r(sqlsrv_errors());
} else {
echo "Connected successfully with Azure AD Access Token.\n";
sqlsrv_close($conn);
}
?>
Reference documents:
- How to use managed identity in App Service: https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=dotnet#obtain-tokens-for-azure-resources.
- Connecting SQL database using Access token: https://docs.microsoft.com/en-us/sql/connect/php/azure-active-directory?view=sql-server-ver15#example---connect-using-azure-ad-access-token.
