When working with Azure AD authentication for Azure SQL DB and DW, you may sometimes encounter certain issues. The table below contains some of the Azure AD authentication problems that may appear when accessing SQL DB/DW, as well as how to troubleshoot them.
Please note that this document is subject to be updated as we detect other issues in this area.
|
|
Problem description |
Troubleshooting |
|
1 |
To download the latest Microsoft Active Directory Authentication Library (ADAL.dll), install the latest SSMS, ODBC or, OLEDB driver |
Download links to install latest SSMS, ODCB and OLEDB driver that contain ADAL.dll library 1) Download SQL Server Management Studio (SSMS)
|
|
2 |
Service principal or application is not able to connect to SQL DB
|
The C# code below allows you to troubleshoot this problem in two steps: 1) Obtain an Azure AD token 2) Pass this token to SQL DB If needed, the encrypted user token can also be available to the support team (see the blog below)
|
|
3 |
Not able to connect to SQL DB using an Azure AD user.
|
Use the following guideline for troubleshooting this issue. See the section below: Not able to connect using an Azure AD user- troubleshooting guideline
|
|
4 |
Login fails when using
|
Currently when using Oauth2, the scope must be set as below "scope": "https://database.windows.net//.default", note "//" here. This is a known issue that will be fixed in the future. Once the fix is available, we will update this entry indicating the right “scope” |
|
5 |
Login fails for Azure Active Directory Integrated authentication (single sign-on) due to missing WS-Trust endpoint
|
When using Azure Active Directory Integrated (single sign-on) authentication with SQL DB, the following error may appear due to network configuration: Could not discover endpoint for Integrated Windows Authentications. Check your ADFS setting. It should support Integrated Windows Authentication for WS-Trust 1.3 or WS-Trust 2005 (System Data).
Ask your network administrator to enable the WS-Trust endpoint |
|
6 |
Unable to connect using Azure Authentication Password when an Azure AD user is setup for multi-factor authentication (MFA)
|
This is by design. When authenticating in SSMS using Azure Active Directory Password or Azure Active Directory Integrated authentication for an Azure AD user setup for MFA, the following error occurs: AADSTS50079: Due to a configuration change made by your administrator, or because you moved to a new location, you must enroll in multi-factor authentication to access … To solve the problem, the authentication method “Azure Active Directory - Universal with MFA support” must be used. For non-SSMS access, see below for a C# code sample Connect to Azure SQL Database with Azure Multi-Factor Authentication
|
|
7 |
Guest user login not working
|
Azure AD user setting for external collaboration in the directory restricts guest users in this directory. Users are allowed in the directory, but general user setup may prevent adding guest users or limit their abilities. See the section below: Grant Azure AD permission rights to guest users
|
|
8 |
Not able to add Azure AD admin for SQL DB from portal as server_name is invalid since it contains upper case letters
|
This is a known issue that will be fixed in the future. Once the fix is available, we will update this entry. To mitigate this, execute the following ARM-based PowerShell script to add an Azure AD admin. See the section below: Not able to add Azure AD admin from portal - invalid server name
|
|
9 |
Not finding Azure SQL Database application in Azure AD Portal
|
This is a known issue. Add “Azure SQL DB” application manually into a customer tenant. See the section below: Add an Azure SQL DB application manually into Azure AD tenant
|
|
10 |
Service principal not able to create a new Azure AD user
|
Currently, this operation is not supported. To help us evaluate this feature request, please add your comments to the blog indicated below:
|
|
11 |
Unable to create new SQL user when creating a guest user in Azure AD |
This is currently not supported. To create an Azure AD guest user in SQL DB, a guest user must be part of an Azure AD group that is created as a SQL user. The same applies when setting up an Azure AD guest user as an Azure AD admin for SQL Server. Guest user must be part of an Azure AD group that can be setup as a SQL Azure AD admin. We are working on the fix for allowing guest users to be added individually, and not as part of a group
|
|
12 |
Unable to create user from external provider: principal cannot be resolved because of Azure AD Conditional Access (CA) policy admin configurations or changed location require(s) re-enrollment in MFA.
|
Error: Principal 'user1@aadoutlook.onmicrosoft.com' could not be resolved. Error message: ….AADSTS50079: Due to a configuration change made by your administrator, or because you moved to a new location, you must enroll in multi-factor authentication to access '00000002-0000-0000-c000-000000000000'
Try using MFA to sign into SQL, then try again. If that does not work, contact your Azure AD admin to change CA policies and allow traffic to the Application ID.
|
|
13 |
Unable to create user from external provider: principal cannot be resolved because access is denied due to CA policy
|
Error: Principal 'user1@aadoutlook.onmicrosoft.com' could not be resolved. Error message: AADSTS53003: Blocked by conditional access
Contact your Azure AD admin to change CA policies and allow traffic to the Application ID. Azure Active Directory admin has created CA policies, but these block the connection outright. See the section below:
|
Not able to connect using an Azure AD user- troubleshooting guideline
Obtain Fiddler traces
- Download Fiddler from here https://www.telerik.com/download/fiddler
(*) Disclaimer: use of this tool is a recommendation to help troubleshoot and is not administered by
Microsoft. Please use at your own risk.
- Install Fiddler and add a root certificate.
- Setup a “Fiddler Trace” with “Decrypt HTTPS Traffic” option checked in [Tools->Options->HTTPS].
- Launch SSMS
- Login with an Azure AD credential
- Stop capture in Fiddler traces
Debug Fiddler Trace?
- Open the Fiddler trace.
- Look for a call to Host “windows.net” or “login.microsoftonline.com” in the left pane.
- Select the frame and look to the right. The upper panel contains the request. You can expand the request by clicking on “Raw” and view the request packet being sent to Azure AD.
- Look for the corresponding response value. The responses from Azure AD are usually very specific and will help guide the customer on what is missing in the authentication request (e.g. "error":"interaction_required","error_description: AADSTS50079. The user is required to use multi-factor authentication……).
- If the Fiddler trace contains a “seemingly legitimate” access token, copy the token from the trace and debug it. Please note that a valid token could reveal user information and is a subject for the privacy compliance, therefore before debugging it wait for the token to expire. If needed, before sharing this token with support team to continue working on the problem, make sure that the token does not contain relevant user information.
Debug Azure AD Token
- Copy the Azure AD access token.
- Open a browser of your choice and go to https://jwt.io/
(*) Disclaimer: this is only a recommendation and opening this link is not required nor owned by
Microsoft.
- Paste the token in the following box:
- This will display ObjectID and groups information.
- Use ObjectID if a user is individually added to the server, or
- Use Guids in groups if a user is logged in as a member of the group.
Obtain an ObjectID of the user or group trying to login.
This information can be obtained from the Azure AD portal for a user or group
(see a screenshot below, indicating in red an Azure AD ObjectID: 25c8820a-xxxx-xxxx-xxxx-fe2fd914e144
for user1@sqlxxx.onmicrosoft.com)
Obtain a SID from Azure SQL DB
Login to a database and execute a SELECT statement from sys.database_principals to find the right SID for a given
Azure AD user or a group.
Using the example above for Azure AD user1@sqlxxx.onmicrosoft.com, the following SID is derived from the below SELECT statement
select name, type, type_desc, SID from sys.database_principals where name='user1@sqlxxx.onmicrosoft.com'
Output
name type type_desc SID
user1@sqlxxx.onmicrosoft.com E EXTERNAL_USER 0x0XXXXXXXXXFE2FD914E144
Compare the ObjectID and SID
The last six digits for these two should match. If not, there’s is a mismatch between a user registered in Azure AD and an Azure AD user created in SQL DB. This mismatch has to be resolved.
Based on the example above for user1@sqlxxx.onmicrosoft.com, the last 6 digits for the ObjectID in Azure AD, and the last six digits for the SID match and represent the same user (see the 6 digits 14E144 indicated in blue).
Obtain SQL connection id
https://azure.microsoft.com/en-us/blog/sql-azure-and-session-tracing-id/
Grant Azure AD permission rights to guest users
Review the Azure portal snapshot for User setting - External collaboration setting (see below).
The current setting below prevents guest users to execute certain directory tasks.
To remove this limitation the current setting (in the red circle) must be changed to NO.
Not able to add Azure AD admin from portal - invalid server name
The following PowerShell script using ARM interface can be used to mitigate this problem.
Please note that this script can also be used to set up an Azure AD admin for SQL DB in normal conditions
(the required impute parameters in this script are indicated in blue).
In addition, this script can be used to set up an Azure AD admin for SQL DB in case an admin is a Service Principal.
Currently, the Azure portal search blade displays the Service Principals for the admin setup.
Import-Module Azure
$tenantId = 'xxxxxe29-xxxxc-4b64-90ac-287b977xxxxx'; # the tenant ID for the Azure AD directory
$administratorType="ActiveDirectory";
$login="user1@aad.onmicrosoft.com"; # the new Azure AD admin for SQL DB (user or group)
$sid="6ac4xxxx-d34c-4XX1-bb03-xxxxfb73xxxx";# the ObjectID of the new Azure AD admin
$clientId = "1950xxxx-xxxx-4e31-a9cf-xxxx9594xxxx" # Microsoft fixed client ID; do not change
$subscriptionId = 'xx77xxxx-a286-xxxx-b6f0-29c42acxxxx' #the server subscription ID
$uri = "urn:ietf:wg:oauth:2.0:oob"
$authUrl = "https://login.windows.net/$tenantId"
$serverName = "SQLxx" # the server name only w/o suffix database.windows.net
$resourceGroupName="sqlxxxx" # the resource name
Login-AzureRmAccount -tenantId $tenantId
$AuthContext = [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext]$authUrl
$result = $AuthContext.AcquireToken("https://management.core.windows.net/",
$clientId,
[Uri]$uri,
[Microsoft.IdentityModel.Clients.ActiveDirectory.PromptBehavior]::Auto)
$authHeader = @{
'Content-Type'='application\json; '
'Authorization'=$result.CreateAuthorizationHeader()
}
$body = @{
properties = @{
administratorType = $administratorType
login = $login
sid = $sid
tenantId = $tenantId
}
} | ConvertTo-Json
# Execute the following commands
# Display an old AAD admin (called B2B in the example below)
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/administrators/ActiveDirectory?api-version=2018-06-01-preview" -Method GET -Headers $authHeader;
[output]
properties id----------@{administratorType=ActiveDirectory; login=B2B; sid=27xxxxxxx-c2xx-48xx-ac14-ff887xxxxxxx; tenantId=xxxxxe29-xxxxc-4b64-90ac-287b977xxxxx} /subscriptions/xx77xxxx-a286-xxxx-b6f0-29c42acxxx...
# Change AAD admin ( in this example from B2B to user1@aad.onmicrosoft.com )
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/administrators/ActiveDirectory?api-version=2018-06-01-preview" -Method PUT -Headers $authHeader -Body $body -ContentType "application/json";
[output]
operation startTime
--------- ---------
UpdateActiveDirectoryAdministrator 2019-11-22T19:37:38.777Z
start-sleep -s 5;
# Display a new AAD admin
#( in the example below a new Azure Ad admin user1@aad.onmicrosoft.com
Invoke-RestMethod -Uri "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/administrators/ActiveDirectory?api-version=2018-06-01-preview" -Method GET -Headers $authHeader;
[output]
properties id--------
@{administratorType=ActiveDirectory; login=user1@aad.onmicrosoft.com; sid=6ac4xxxx-d34c-4XX1-bb03-xxxxfb73xxxx; tenantId=xxxxxe29-xxxxc-4b64-90ac-287b977xxxx /subscri...
Add an Azure SQL DB application manually into Azure AD tenant
/This execution requires an Azure AD admin permission on the tenant/
Step 1.
Check if the Azure SQL Database has already been added to the Azure AD directory (see below)
Azure SQL Database API permissions must also be part of created applications
Below, we indicate the API permissions required for a user created application permission
Go to App registration->myapp-> API my organization uses, and check if “Azure SQL Database” is listed for your tenant) allowing you to grant it the necessary permission (see below).
Step 2.
In Case Azure SQL DB is not part of the tenant, the following two options can be used to add it
Option 1: Execute one of the commands below comment to create an “Azure SQL Database” application in the tenant
- Connect-AzureAD -TenantId "[The tenant Id]"
- New-AzureADServicePrincipal -AppId “022907d3-0f1b-48f7-badc-xxxxx” -DisplayName “Azure SQL Database”
- New-AzureADServicePrincipal -AppId “022907d3-0f1b-48f7-badc-xxxxx
New-AzureADServicePrincipal -DisplayName “Azure SQL Database”
Option 2: Re-register for Microsoft.SQL
- Register a subscription for Microsoft.SQL by executing the command indicated below and retry.
- Register-AzResourceProvider -ProviderNamespaceSQL
Examples of Conditional Access (CA) application policies preventing or blocking access to create Azure AD users from external provider
Defining MFA CA policy applying to all cloud apps
Even though Azure SQL Database is excluded from application requiring MFA (see below), an external Azure AD user cannot be created because the Azure AD graph API requires MFA (see also next snapshot).
Enforcing CA (see below) is causing an error: “Principal xxx could not be resolved. Error message: AADSTS50079
In the snapshot below, the CA policy blocks the connection outright, causing an error
“Principal xxx could not be resolved. Error message: AADSTS53003: Blocked by conditional access”
Microsoft
