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 '[email protected]' 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 '[email protected]' 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 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 [email protected])     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 [email protected], the following SID is derived from the below SELECT statement select name, type, type_desc, SID from sys.database_principals where name='[email protected]'   Output name                                                             type       type_desc                                                              SID [email protected]               E              EXTERNAL_USER     0x0XXXXXXXXXFE2FD914E144   Compare the ObjectID and SIDThe 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 [email protected], 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="[email protected]"; # 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 [email protected] ) 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   [email protected] 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; [email protected]; 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-xxxxxNew-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”