Access Azure Database using Azure Active Directory authentication

To enable Active Directory users/apps for accessing SQL Database, SQL Managed Instance, or Azure Synapse, we can follow the steps given below to create AD users in the database and give required permissions to tables, views, external tables, etc.

In this blog, we will use one app registration to configure database access and also see how to authorize it with a bearer token.

Pre-requisite:

1. Azure SQL server and SQL database created with some views, tables.

2. Valid Azure active directory app registration for token based access.

Steps:

1. Create User:

   • Go to SQL server in Azure portal and navigate to Active Directory Admin tab.

   • Set valid user as admin which will need to login to database server in SSMS.

     

   • Login to database server with the assigned admin account (using AD credentials).

     

   • Execute below command to create login object in master database. In this statement, user_name can be name of AD registered app or any AD user email Id. note that this step is optional and required if you need to provide any server level roles.

       use master;
       -- login is required only if we need to provide server level role
       CREATE LOGIN [user_name] FROM EXTERNAL PROVIDER;
     

   • Execute following command to create user object in specific database.

       use mydb;
       --if you have created login then use below
       CREATE USER [user_name] FOR LOGIN [user_name];
       --if not created login and directly adding user then use below
       CREATE USER [user_name] FROM EXTERNAL PROVIDER;
     

2. Provide access to external data source:

   • If your database has some external tables, views and this user need to be provided access on them, then first we need to provide access on external data source scoped credentials. Execute following SQL statement.

       use mydb;
     
       GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[scoped_credential_name] TO [user_name];
     

3. Provide database permissions to user:

   • Execute following SQL command to assign databse role to user. Here [role_name] can be built in roles such as db_owner, db_datareader or can be a custom role.

       use mydb;
     
       ALTER ROLE <role_name> ADD MEMBER [user_name];
     

   • If we want to provide table level specific access then run below commands instead of assigning role.

       use mydb;
       --provide table/object specific read access to user
       GRANT SELECT ON [dbo].[table1] TO [user_name];  
       GRANT SELECT ON [dbo].[view1] TO [user_name];
     

4. Connect AD user from a client application:

   The following are ways to create a connection string to connect AD users from client application (using c#).

   • Active Directory integrated authentication

     To connect to a database using integrated authentication and an Azure AD identity, set the Authentication keyword in the database connection string to Active Directory Integrated. Below is code sample in c# -

       string ConnectionString = @"Data Source=servername.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=mydb;";
       SqlConnection conn = new SqlConnection(ConnectionString);
       conn.Open();
     

   • Active Directory password authentication

     To connect to a database using Azure AD cloud-only identity user accounts, set Authentication keyword to Active Directory Password.

       string ConnectionString =
       @"Data Source=servername.database.windows.net; Authentication=Active Directory Password; Initial Catalog=mydb;  UID=testuser@xyz.com; PWD=MyPassWord!";
       SqlConnection conn = new SqlConnection(ConnectionString);
       conn.Open();
     

   • Azure AD token

     We can use Azure AD token to create connection string and connect to database as per below code sample.

       string ConnectionString = @"Data Source=servername.database.windows.net; Initial Catalog=mydb;";
       SqlConnection conn = new SqlConnection(ConnectionString);
       conn.AccessToken = "Your JWT token";
       conn.Open();
     

     To get AD token, you can follow this blog and use Resource parameter as https://database.windows.net to get token for database resource.

Conclusion

In this blog, we understood how to create Azure Active Directory user in the Azure database and enable database access from client application.

Thanks for reading.

Keep learning!