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.

 

 

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.

2. Install Fiddler and add a root certificate.
3. Setup a “Fiddler Trace” with “Decrypt HTTPS Traffic” option checked in [Tools->Options->HTTPS].
4. Launch SSMS
5. Login with an Azure AD credential
6. 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.

3. Paste the token in the following box:
4. 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)

 

 

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”