Part 1 – Azure SQL Database with Azure Active Directory Authentication

Azure SQL

This post is the first post in a series of three posts and will help you with the creation of identity pass-through authentication from a client application to an API and then to an Azure SQL Database. In this post we setup an Azure SQL Database and enable Azure Active Directory authentication on the database.

Create Azure SQL Database

First step is to create a new Azure SQL Database.

  1. Open the azure portal (https://portal.azure.com)
  2. Click on “New” and search for SQL Database.

Azure SQL Database

  1. Click on SQL Database.
  2. In the next blade that appears click “Create”
  3. In the “New” SQL Database blade fill in the correct properties. For this example you could use the AdventureWorks database.
  4. Wait for the Azure SQL Database deployment to be done.

Enable Azure Active Directory Authentication

  1. With the Azure SQL Database that is created you also create an Azure SQL Server or you have chosen to use an existing one. Within the portal navigate to the Azure SQL Server.
  2. In the Azure SQL Server blade click on “Active Directory admin” under “Settings”


  1. Click on ” “Set admin” in the “Active Directory Admin” blade.

Active Directory Admin

  1. Add the Active Directory user that you want to use as admin and click on “Select”.
  2. In the Active Directory Admin blade click on “Save” to save the settings.

Add user to the Azure SQL Database

With the Active Directory Admin set for the Azure SQL Server you are able to login to the SQL server with SQL Server Management Studio. On all client machines, from which your applications or users connect to Azure SQL Database or Azure SQL Data Warehouse using Azure Active Directory  users / applications, you must install the following software:

You can meet these requirements by:

If you have installed the software and you met the requirements take the following steps to add other users.

  1. Make sure your Client IP address is added to the allowed addresses within the firewall of the Azure SQL Server. (Adding your Client IP to the Azure SQL server firewall)
  2. Open SQL Server Management Studio as Administrator.
  3. In the connect window fill in the Azure SQL Server and select “Active Directory Universal Authentication” as the authentication method.

Connect to Server

  1. Clicking on “Connect” will make a authentication dialog appear, fill in the right credentials (the credentials for the Active Directory Admin) and click on login.
  2. Open a new Query window in SQL Management studio and perform the following query.
Create user [User] from external provider
  1. This query will add a User, Application or Group out of Azure Active Directory to the SQL Server Users. You can use the below SQL query to check if the user is added.
SELECT * FROM sys.sysusers;
  1. By adding the user it  does not have read rights on the Azure SQL Database. Perform the next query to give the user the data reader role.
ALTER ROLE db_datareader ADD MEMBER [User]
  1. You can now login with the added user by using SQL Server Management studio but make sure you set the initial database for the connection by clicking options.

Initial Database

If you do not configure the initial database you will get the following exception: Login failed for user “NT AUTHORITY\ANONYMOUS LOGIN”. (Microsoft SQL Server, Error: 18456).

SQL Authentication Error

In the next post we will  create a Azure API Application to query the Azure SQL Database.

Related Posts

Azure DevOps Automation A couple of weeks ago the rename / rebranding of Visual Studio Team Services to Azure DevOps was announced. The rebranding is a great step forward int...
AKS (Kubernetes) and no connection could be made because the target machine acti... A client of my had an error while connecting to different resources within their Kubernetes cluster in Azure (AKS). Kubectl error On the kubectl com...
Kubernetes (AKS) attached to Azure Storage (Files) Kubernetes (AKS) can be used for many situations. For a client we needed to make files available trough a Kubernetes Pod. The files needed to be share...
Resource Group deployment via ARM templates When deploying an Azure Resource Manager (ARM) template you have to create a resource group within Azure. To deploy a template via script your script ...
Azure Managed Service Identity and Local Development Instead of storing user credentials of an external system in a configuration file, you should store them in the Azure Key Vault. Before MSI (Managed S...
The securitydata Azure resource group Most of the times companies have rules in place for managing their Azure environment. The main rules that should be in place are “Azure Policies” and ...


  1. Thanks for very useful post!
    Do you know if I can add permissions for an app-service-user instead of “User” in script “Create user [User] from external provider”?
    I mean “Managed service identity” which can be set in Azure Portal for every App Service.

    • Hi Andre,

      You can set persmissions for an Application in Azure SQl. If you need any assistance please let me know.

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.