Providing Access to Azure SQL Database

This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.

Once you have deployed your database and know what different roles exist, the next thing you are going to do is provide access to users and service accounts.

Providing access to Azure SQL Database is somewhat similar to doing the same in your regular SQL Server, you create a Login and then Users to provide them access on database level. With the only difference that you cannot create Logins and User using GUI in Azure SQL Database and have to create one using T-SQL only.

As you already know there are two different accounts in Azure SQL Database SQL Authenticated and AAD Authenticated. We will see how to create a SQL Login and AD Login.

Creating SQL Logins

Creating SQL Logins is similar to doing it on any SQL Server, i.e. provide a name and password.

CREATE LOGIN Test2
	WITH PASSWORD = 'T#$t2O20' 
GO

A SQL Login can be created by logging in either by SQL Authenticated account or AD Admin account.

Once you have your login created you need to create it’s user. Remember you cannot use “Use DatabaseName” in Azure SQL Database, so either change the context of already opened Query window or open another query with the context of the database in which you’ll be creating your database

CREATE USER Test2
	FOR LOGIN Test2
	WITH DEFAULT_SCHEMA = Test2
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'Test2'
GO
SQL Authenticated user and login

Creating AD Logins

There is some difference in providing access using AAD authentication,
1. You cannot create a Login but a contained database user only.
2. You cannot create an AD Login using SQL Admin account, you need to login using the AD Admin only otherwise you’ll see the below error.

If you try creating a AAD contained user using SQL Admin like below, you will see the error following the syntax.

CREATE USER [sqluser@ershwetanksingh9gmail.onmicrosoft.com] FROM EXTERNAL PROVIDER

Error Msg 33159, Level 16, State 1, Line 1
Principal ‘sqluser@ershwetanksingh9gmail.onmicrosoft.com’ could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

To resolve this connect using AD admin and re-run the same script.

CREATE USER [sqluser@ershwetanksingh9gmail.onmicrosoft.com] FROM EXTERNAL PROVIDER

You’ll be able to see your user now.

AD Authenticated user

Once you have your user created you can assign them the role as per the requirement.

2 thoughts on “Providing Access to Azure SQL Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s