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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

   

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)      Downloading ODBC driver

3)      Download OLEDB driver

 

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)

https://techcommunity.microsoft.com/t5/Azure-SQL-Database/Azure-AD-Service-Principal-authentication-to-SQL-DB-Code-Sample/ba-p/481467

 

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
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”

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

 

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:

https://feedback.azure.com/forums/169401-azure-active-directory/suggestions/39176089-support-service-principal-impersonation-so-that-sp

 

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.
See the section below:
Examples of Conditional Access application policies preventing or blocking access to create Azure AD users from external provider

 

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:
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 certificate.
  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/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)

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.