Part 1 – Azure SQL Database with Azure Active Directory Authentication

4 minute read

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”

image

  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.