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

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

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.

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)

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.

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.

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.

I list below the differences between Login and User:

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

Result:

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.

Example:

Script to add this Service Principal to the database