SQL Authentication in Azure Synapse Analytics

Azure Synapse Analytics has two SQL form-factors that enable you to control your resource consumption. This article explains how the two form-factors control the user authentication.

To authorize to Synapse SQL, you can use two authorization types:

  • Azure Active Directory authorization
  • SQL authorization

SQL authorization enables legacy applications to connect to Azure Synapse SQL in a familiar way. However, Azure Active Directory authentication allows you to centrally manage access to Azure Synapse resources, such as SQL pools. Azure Synapse Analytics supports disabling local authentication, such as SQL authentication, both during and after workspace creation. Once disabled, local authentication can be enabled at any time by authorized users. For more information on Azure AD-only authentication, see Disabling local authentication in Azure Synapse Analytics.

Administrative accounts

There are two administrative accounts (SQL admin username and SQL Active Directory admin) that act as administrators. To identify these administrator accounts for your SQL pools open the Azure portal, and navigate to the Properties tab of your Synapse workspace.

SQL Server Admins

  • SQL admin username

    When you create an Azure Synapse Analytics, you must name a Server admin login. SQL server creates that account as a login in the master database. This account connects using SQL Server authentication (user name and password). Only one of these accounts can exist.

  • SQL Active Directory admin

    One Azure Active Directory account, either an individual or security group account, can also be configured as an administrator. It's optional to configure an Azure AD administrator, but an Azure AD administrator must be configured if you want to use Azure AD accounts to connect to Synapse SQL.

    • The Azure Active Directory admin account controls access to dedicated and serverless SQL pools, while Synapse RBAC roles can be used to additionally control access to serverless pools, for example, with the Synapse Administrator and Synapse SQL Administrator role.

The SQL admin username and SQL Active Directory admin accounts have the following characteristics:

  • Are the only accounts that can automatically connect to any SQL Database on the server. (To connect to a user database, other accounts must either be the owner of the database, or have a user account in the user database.)
  • These accounts enter user databases as the dbo user and they have all the permissions in the user databases. (The owner of a user database also enters the database as the dbo user.)
  • Don't enter the master database as the dbo user, and have limited permissions in the master database.
  • Are not members of the standard SQL Server sysadmin fixed server role, which is not available in SQL Database.
  • Can create, alter, and drop databases, logins, users in the master database, and server-level IP firewall rules.
  • Can add and remove members to the dbmanager and loginmanager roles.
  • Can view the sys.sql_logins system table.

Note

If a user is configured as an Active Directory admin and Synapse Administrator, and then removed from the Active Directory admin role, then the user will lose access to the dedicated and serverless SQL pools in Synapse. They must be removed and then added to the Synapse Administrator role to regain access to SQL pools.

To manage the users having access to serverless SQL pool, you can use the instructions below.

To create a login to serverless SQL pool, use the following syntax:

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
-- or
CREATE LOGIN [Mary@domainname.net] FROM EXTERNAL PROVIDER;

When the login exists, you can create users in the individual databases within the serverless SQL pool endpoint and grant required permissions to these users. To create a user, you can use the following syntax:

CREATE USER Mary FROM LOGIN Mary;
-- or
CREATE USER Mary FROM LOGIN Mary@domainname.net;
-- or
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Once login and user are created, you can use the regular SQL Server syntax to grant rights.

Non-administrator users

Generally, non-administrator accounts don't need access to the master database. Create contained database users at the database level using the CREATE USER (Transact-SQL) statement.

The user can be an Azure Active Directory authentication contained database user (if you have configured your environment for Azure AD authentication), or a SQL Server authentication contained database user, or a SQL Server authentication user based on a SQL Server authentication login (created in the previous step.)

To create users, connect to the database, and execute statements similar to the following examples:

CREATE USER Mary FROM LOGIN Mary;
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Initially, only one of the administrators or the owner of the database can create users. To authorize additional users to create new users, grant that selected user the ALTER ANY USER permission, by using a statement such as:

GRANT ALTER ANY USER TO Mary;

To give additional users full control of the database, make them a member of the db_owner fixed database role.

In Azure SQL Database or synapse serverless, use the ALTER ROLE statement.

ALTER ROLE db_owner ADD MEMBER Mary;

In dedicated SQL pool use EXEC sp_addrolemember.

EXEC sp_addrolemember 'db_owner', 'Mary';

Note

One common reason to create a database user based on a server login is for users that need access to multiple databases. Since contained database users are individual entities, each database maintains its own user and its own password. This can cause overhead as the user must then remember each password for each database, and it can become untenable when having to change multiple passwords for many databases.

Groups and roles

Efficient access management uses permissions assigned to groups and roles instead of individual users.

  • When using Azure Active Directory authentication, put Azure Active Directory users into an Azure Active Directory group. Create a contained database user for the group. Place one or more database users into a database role and then assign permissions to the database role.

  • When using SQL Server authentication, create contained database users in the database. Place one or more database users into a database role and then assign permissions to the database role.

The database roles can be the built-in roles such as db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, and db_denydatareader. db_owner is commonly used to grant full permission to only a few users. The other fixed database roles are useful for getting a simple database in development quickly, but are not recommended for most production databases.

For example, the db_datareader fixed database role grants read access to every table in the database, which is usually more than is strictly necessary.

It's far better to use the CREATE ROLE statement to create your own user-defined database roles and carefully grant each role the least permissions necessary for the business need. When a user is a member of multiple roles, they aggregate the permissions of them all.

Permissions

There are over 100 permissions that can be individually granted or denied in SQL Database. Many of these permissions are nested. For example, the UPDATE permission on a schema includes the UPDATE permission on each table within that schema. As in most permission systems, the denial of a permission overrides a grant.

Because of the nested nature and the number of permissions, it can take careful study to design an appropriate permission system to properly protect your database.

Start with the list of permissions at Permissions (Database Engine) and review the poster size graphic of database engine permissions.

Considerations and restrictions

When managing logins and users in SQL Database, consider the following points:

  • You must be connected to the master database when executing the CREATE/ALTER/DROP DATABASE statements.
  • The database user corresponding to the Server admin login can't be altered or dropped.
  • Server admin will be disabled if Azure AD-only authentication is enabled.
  • US-English is the default language of the Server admin login.
  • Only the administrators (Server admin login or Azure AD administrator) and the members of the dbmanager database role in the master database have permission to execute the CREATE DATABASE and DROP DATABASE statements.
  • You must be connected to the master database when executing the CREATE/ALTER/DROP LOGIN statements. However, using logins is discouraged. Use contained database users instead. For more information, see Contained Database Users - Making Your Database Portable.
  • To connect to a user database, you must provide the name of the database in the connection string.
  • Only the server-level principal login and the members of the loginmanager database role in the master database have permission to execute the CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements.
  • When executing the CREATE/ALTER/DROP LOGIN and CREATE/ALTER/DROP DATABASE statements in an ADO.NET application, using parameterized commands isn't allowed. For more information, see Commands and Parameters.
  • When executing the CREATE USER statement with the FOR/FROM LOGIN option, it must be the only statement in a Transact-SQL batch.
  • When executing the ALTER USER statement with the WITH LOGIN option, it must be the only statement in a Transact-SQL batch.
  • CREATE/ALTER/DROP LOGIN and CREATE/ALTER/DROP USER statements are not supported when Azure AD-only authentication is enabled for the Azure Synapse workspace.
  • To CREATE/ALTER/DROP a user requires the ALTER ANY USER permission on the database.
  • When the owner of a database role tries to add or remove another database user to or from that database role, the following error may occur: User or role 'Name' does not exist in this database. This error occurs because the user isn't visible to the owner. To resolve this issue, grant the role owner the VIEW DEFINITION permission on the user.

Next steps

For more information, see Contained Database Users - Making Your Database Portable.