Part 1 – Azure SQL Database with Azure Active Directory Authentication
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.
- Open the azure portal (https://portal.azure.com)
- Click on “New” and search for SQL Database.
- Click on SQL Database.
- In the next blade that appears click “Create”
- In the “New” SQL Database blade fill in the correct properties. For this example you could use the AdventureWorks database.
- Wait for the Azure SQL Database deployment to be done.
Enable Azure Active Directory Authentication
- 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.
- In the Azure SQL Server blade click on “Active Directory admin” under “Settings”
- Click on " “Set admin” in the “Active Directory Admin” blade.
- Add the Active Directory user that you want to use as admin and click on “Select”.
- 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:
- .NET Framework 4.6 or later from https://msdn.microsoft.com/library/5a4x27ek.aspx.
- Azure Active Directory Authentication Library for SQL Server (ADALSQL.DLL) is available in multiple languages (both x86 and amd64) from the download center at Microsoft Active Directory Authentication Library for Microsoft SQL Server.
You can meet these requirements by:
- Installing either SQL Server 2016 Management Studio or SQL Server Data Tools for Visual Studio 2015 meets the .NET Framework 4.6 requirement.
- SSMS installs the x86 version of ADALSQL.DLL.
- SSDT installs the amd64 version of ADALSQL.DLL.
- The latest Visual Studio from Visual Studio Downloads meets the .NET Framework 4.6 requirement, but does not install the required amd64 version of ADALSQL.DLL.
If you have installed the software and you met the requirements take the following steps to add other users.
- 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)
- Open SQL Server Management Studio as Administrator.
- In the connect window fill in the Azure SQL Server and select “Active Directory Universal Authentication” as the authentication method.
- 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.
- Open a new Query window in SQL Management studio and perform the following query.
Create user [User] from external provider
- 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;
- 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]
- 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.
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).
In the next post we will create a Azure API Application to query the Azure SQL Database.