Click on the banner to learn about and purchase my database training on Azure

Azure SQL Database - How to create and manage users and logins with SQL authentication and Azure Active Directory (Azure AD)

Views: 1.012 views
Reading Time: 10 minutes

Speak guys!
In this post I would like to share with you how to create and manage users and logins. I will explain what is the Azure Active Directory admin user/group, what is the admin server, how to create it and what are the differences between a login created in the instance and a user created in the database.

It is worth remembering that the Management Studio interface does not yet support creating logins and users, so this must be done via T-SQL commands.

authentication methods

Click here to view content
The first choice you make regarding your Azure SQL DB users is regarding the authentication method during server creation/change.

This will define if your server will accept connections using Azure Active Directory (Azure AD) only, if it will only accept connections using SQL authentication or if it will accept both forms of authentication.

The safest way, and Microsoft's official recommendation, is to use the option to accept only Azure Active Directory (Azure AD) authentication, since the responsibility for managing passwords and credentials becomes Azure Active Directory and not the database. Dice. Additionally, you can set 2-factor authentication, for example, requiring the user to confirm login on an external device such as mobile phone or email.

Users with SQL authentication are also susceptible to brute force attacks as the database will not monitor and block excessive failed connection attempts by default, while Azure AD has this blocking by default, reports on connection failures, Intelligence Artificial analyzing all logins and blocking suspicious logins, etc.

Another positive point for this choice is that if that person leaves the company, just deactivate their login in Azure Active Directory and they will automatically lose access to all banks. Using SQL authentication, you will have to login bank by bank, verify that this user had access, and delete/deactivate that account.

However, we know that it is not always possible to block authentication using SQL credentials, especially when talking about legacy systems, some BI tools or some types of integrations. In these cases, SQL authentication ends up being necessary. If this is not your case, I suggest keeping only the authentication using Azure AD.

Depending on the authentication method chosen, the Azure Active Directory admin and Server admin options are shown/hidden.

Use SQL authentication

Use only Azure Active Directory (Azure AD) authentication

Use both SQL and Azure AD authentication

Azure Active Directory admin

Click here to view content
The first user you end up creating on a new Azure SQL Database server, if you chose the “Use only Azure Active Directory (Azure AD) authentication” or “Use both SQL and Azure AD authentication” authentication methods, is Azure Active Directory admin.

This user or group will have unrestricted access to the server's databases, with permission to manage users/logins, all databases and unrestricted access to data as well, and can be used to connect to the database.

The user assigned as Azure Active Directory admin cannot change any server properties through the Azure portal (if they do not have other permissions on the resource and/or Subscription/Resource Group) nor can they change the admin user. With this assignment alone, he will not even be able to open or view the resource through the Portal.

Your permissions are only at the instance level, connected to the database. From the moment this user connects, he will have access to do everything possible using T-SQL.

Some settings such as Firewall, Service Tier and creation of other databases, this user can do using T-SQL commands, but cannot do it through the Azure portal.

It is worth remembering that only ONE user can be assigned to be Azure Active Directory admin. In case you want to have more than one person with this level of access, create a group, add the users in that group and assign that group as the Azure Active Directory admin.

Important: Users who are the owner/contributor of the resource group or subscription have permission to manage the server and even change this user/group, but cannot connect to the database if they do not have permission directly from the database.

Alert: The user/group that is the Azure Active Directory admin automatically has access to the database.

After the server has been created, you can still enable/disable authentication via Azure Active Directory (Azure AD) and also manage the admin user.

If you try to enable the “Azure Active Directory authentication only” option, to only allow authentication using Azure AD, you will see a confirmation asking if you really want to enable this option, because it will DISABLE SQL authentication and all users using this option. authentication type, including user “sa” (Server admin)

Server admin (sa)

Click here to view content
Known as the “sa” user, the Server admin is a user that has basically the same permissions as the Azure Active Directory admin user, but using SQL authentication.

This means that this user has no access to the Azure portal and cannot change anything there or change any properties. However, it can be used to log into the instance and it will have access to all data, from all tables, in addition to being able to change/create/delete any user, login, permission or object in the database, in addition to managing database and server firewall via T-SQL commands.

As I already mentioned in this post, this alternative is the least secure possible, since the database is responsible for managing passwords and users. It does not support MFA and each user can have a different password, in different banks.

If you want to deactivate the same user in several banks, you will have to enter bank by bank to deactivate/delete the account (or create a script to automate this), instead of simply deactivating the account in AD.

It is not possible to change the Server admin name after the server is created, but it is possible to reset the password if necessary

This user is only available if you select the authentication method “Use SQL authentication” or “Use both SQL and Azure AD authentication”.

If you chose these options and then changed the authentication method and then changed it to only allow authentication with Azure Active Directory, that user will be disabled, as will all others using SQL authentication.

Creating Logins

Click here to view content
After defining the administrator users, it's time to create the other bank users. One of the ways you give access to a database is by creating a login.

Important: Logins can only be created if you are logged into the master bank

The login is a way to connect to the bank, where the initial bank will be the “master” and then he can change the context to the desired bank. In addition, the login can be added to some server role to be granted server-level permissions.

Important: Unlike SQL Server, a login cannot be given server-level permissions in Azure SQL Database, it can only be added to server roles that give server-level permissions, as the server concept is just logical and not physical.

Creating a login with SQL authentication

Simple. Fast. Practical. insecure. And one more user that you will have to keep the password updated, changing from time to time (and changing in apps), worrying about brute force attack attempts, password leaks, etc.

To create a login with SQL authentication, just follow the script below:

Creating a login with Azure Active Directory authentication

The first step for this is to access Azure Active Directory to get the “User Principal Name” (UPN) of the login I want to add to the database:

Now that I know the User Principal Name, I can create the login in the “master” bank

Notice that I didn't need to set any password to login. The password will be the Azure Active Directory password itself, a single, centralized location for managing users and passwords.

Alert: The CHECK_POLICY, CHECK_EXPIRATION, DEFAULT_DATABASE and DEFAULT_LANGUAGE options are not available when creating logins in Azure SQL Database. However, the CHECK_POLICY option is enabled internally, not allowing you to create weak passwords for logins (Azure SQL Password Policy).

Tip: If you are going to use SQL authentication, I suggest using password generating sites, such as LastPass and choose a very complex password (I usually use 50+ characters)

User Creation

Click here to view content
As with SQL Server, Azure SQL Database allows the creation of users within each of the databases, where that user has specific permissions in each of these databases. However, unlike SQL Server, you don't need to create a login to connect to Azure SQL, you can connect using just the user.

I list below the differences between Login and User:

Created in the "master" bankCreated in the bank where he will connect
The connection is made without having to specify the databaseThe connection is made necessarily having to specify the database
Login can be granted server-level permissions (via server roles)User CANNOT be granted server-level permissions
Login CANNOT be granted permissions on a databaseUser can be granted permissions on a database
Login CANNOT access any databaseUser can access the databases he has access to, even if he is not associated with a login
Login needs to be associated with a user to access some databaseUser does NOT need to be associated with a login (It's even better not to be)
Tip: For security reasons, I usually do not create logins and always create the user only in the database he will access. In this way, I need to specify the name of the database in the connection (otherwise it gives an error) and it will only be able to see that database (and the “master”), it does not see any other database. This is more secure, because if someone were to gain access to this credential, they would need to know the exact name of the database to be able to access the data.

If you choose to create the login and then create a user associated with a login, authentication will be done via login, without having to specify the database name. When connecting to the server, that person will see all the databases, making a possible attack easier.

Creating a User Using SQL Authentication

As I mentioned earlier, this option is the least secure, but it is the simplest, most practical and easiest to use, especially if you are using legacy systems or applications that do not support connection using Azure Active Directory.

Creating a User Associated with a Login

In this scenario, there is already a previously created login, either using SQL authentication or Azure Active Directory, and I will now create a user in the desired database, using this existing login. In this case, I will not specify a password when creating the user, as it will take advantage of the login authentication method.

Creating a user using Azure Active Directory (Azure AD)

As with creating a login, the first step in creating a user using Azure Active Directory (Azure AD) is to access Azure Active Directory to obtain the “User Principal Name” (UPN) of the user I want to add to the database. :

Now that I know the User Principal Name, I can create the user in the desired bank

Creating a user for an Azure Data Factory (Managed Identity)

Whenever possible, choose to use “Managed Identity” authentication in your Azure application or resource. This means that you only need to create the user in the database, without having to enter any user, password or KeyVault to access the database. To create the login/user in the database, it is the same way as any other login/user with Azure AD authentication, with the difference that you will enter the name of the resource instead of the User Principal Name.

Practical example
I have an Azure Data Factory resource and I want to add it to the database so it has access to read and modify data and structures.

I will add the resource name now as a user (it's up to you between login or user) in the database


Creating a User for an App Registration / Service Principal

To create a user for an App Registration / Service Principal using Managed Identity, the process is the same, just enter the name of the AppRegistration and add it normally to the database.


Script to add this Service Principal to the database

Tip: If you cannot or cannot use Managed Identity, you will have to obtain the access token and use the client secret. In that case, I suggest reading the article Query Azure SQL Database with PowerShell and Azure App Registration.

Another Tip: Need help implementing Managed Identity in your application? Give the article a read Passwordless connection string to Azure SQL database using Azure managed identity

That's it folks!
Hope you enjoyed this article and see you next time!