Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW

 When working with Azure AD for Azure SQL DB and DW, you may sometimes encounter certain issues. The table below contains some of the Azure AD 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 descriptionTroubleshooting
1To download the latestMicrosoft Library (ADAL.dll), install the latest SSMS, ODBC or, OLEDB driverDownload links to install latest SSMS, ODCB and OLEDB driver that contain ADAL.dll library
1) Download SQL Server Management Studio (SSMS)
2) Downloading ODBC driver
3) Download OLEDB driver
2Service principal or application is not able to connect to SQL DBThe C# code below allows you to troubleshoot this problem in two steps:
1) Obtain an Azure AD token
2) Pass this token to SQL DBIf needed, the user token can also be available to the support team (see the blog below) https://techcommunity.microsoft.com/t5/Azure-SQL-Database/Azure-AD-Service-Principal-authentication-to-SQL-DB-Code-Sample/ba-p/481467
3Not 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
4Login fails when using
Azure AD OAuth2 (MSAL)
to get a token and connect to SQL DB
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”
5Login fails for Azure Integrated authentication (single sign-on) due to missing WS-Trust endpointWhen using Azure Integrated (single sign-on) authentication with SQL DB, the following error may appear due to 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 administrator to enable the WS-Trust endpoint
6Unable 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 sampleConnect to Azure SQL Database with Azure Multi-Factor Authentication
7Guest user login not workingAzure 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
8Not 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 
9Not finding Azure SQL Database application in Azure AD PortalThis is a known issue.Add “Azure SQL DB” application manually into a customer tenant.
It requires an Azure AD admin permission on the tenant.
See the section below:Add an Azure SQL DB application manually into Azure AD tenant
10Service principal not able to create a new Azure AD userCurrently, this operation is not supported. To help us evaluate this feature request, please add your comments to the blog indicated below:
https://feedback.azure.com/forums/169401-azure-active-directory/suggestions/39176089-support-service-principal-impersonation-so-that-sp
11Unable to create new SQL user when creating a guest user in Azure ADThis 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
12Unable 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.
See the section below:
Examples of Conditional Access application policies preventing or blocking access to create Azure AD users from external provider
13Unable to create user from external provider: principal cannot be resolved because access is denied due to CA policyError: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:
Examples of Conditional Access application policies preventing or blocking access to create Azure AD users from external provider

Not able to connect using an Azure AD user- troubleshooting guideline

Obtain Fiddler traces

  1. 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.

  1. Install Fiddler and add a root .
  2. Setup a “Fiddler Trace” with “Decrypt HTTPS Traffic” option checked in [Tools->Options->HTTPS].
  3. Launch SSMS
  4. Login with an Azure AD credential
  5. Stop capture in Fiddler traces

Debug Fiddler Trace?

  1. Open the Fiddler trace.
  2. Look for a call to Host “windows.net” or “login.microsoftonline.com” in the left pane.
  3. 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.
  4. 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……).
  5. 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

  1. Copy the Azure AD access token.
  2. 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.

  1. Paste the token in the following box:
  2. This will display ObjectID and groups information.
    1. Use ObjectID if a user is individually added to the server, or
    2. 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)

Capture10.PNG

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.

clipboard_image_1.png

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/provide..." -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/provide..." -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/provide..." -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)

clipboard_image_2.png

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).

Capture.11.PNG

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

clipboard_image_4.png

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).

clipboard_image_5.png

Enforcing CA (see below) is causing an error: “Principal xxx could not be resolved. Error message: AADSTS50079

clipboard_image_6.png

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”

clipboard_image_7.png

 

This article was originally published by Microsoft's Azure SQL Database Blog. You can find the original article here.