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:
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.
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. |
<?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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.