A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/sql-authentication below:

SQL Authentication in Azure Synapse Analytics - 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 authenticate to Synapse SQL, you can use two options:

SQL authentication enables legacy applications to connect to Azure Synapse SQL in a familiar way, with a user name and password. However, Microsoft Entra 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 Microsoft Entra-only authentication, see Disabling local authentication in Azure Synapse Analytics.

Administrative accounts

There are two administrative accounts (SQL admin username and Microsoft Entra 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.

The SQL admin username and Microsoft Entra admin accounts have the following characteristics:

Note

If a user is configured as an Microsoft Entra admin and Synapse Administrator, and then removed from the Microsoft Entra admin role, then the user will lose access to the dedicated SQL pools in Synapse. They must be removed and then added to the Synapse Administrator role to regain access to dedicated 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.

Administrator access path

When the workspace-level firewall is properly configured, the SQL admin username and the SQL Microsoft Entra admin can connect using client tools such as SQL Server Management Studio or SQL Server Data Tools. Only the latest tools provide all the features and capabilities.

The following diagram shows a typical configuration for the two administrator accounts:

When using an open port in the server-level firewall, administrators can connect to any SQL Database.

Database creators

One of these administrative roles is the dbmanager role. Members of this role can create new databases. To use this role, you create a user in the master database and then add the user to the dbmanager database role.

To create a database, the user must be a user based on a SQL Server login in the master database or contained database user based on a Microsoft Entra user.

  1. Using an administrator account, connect to the master database.

  2. Create a SQL Server authentication login, using the CREATE LOGIN statement. Sample statement:

    CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';

    Note

    Use a strong password when creating a login or contained database user. For more information, see Strong Passwords.

    To improve performance, logins (server-level principals) are temporarily cached at the database level. To refresh the authentication cache, see DBCC FLUSHAUTHCACHE.

  3. Create a databases user by using the CREATE USER statement. The user can be a Microsoft Entra authentication contained database user (if you've configured your environment for Microsoft Entra 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.)

    Sample statements:

    Create a user with Microsoft Entra ID:

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

    Create a SQL Database contained database user:

    CREATE USER Ann WITH PASSWORD = '<strong_password>';

    Create a SQL Server user based on a SQL Server authentication login:

    CREATE USER Mary FROM LOGIN Mary;

  4. Add the new user, to the dbmanager database role in master using the sp_addrolemember procedure (note that ALTER ROLE statement is not supported in SQL provisioned). Sample statements:

    EXEC sp_addrolemember 'dbmanager', 'Mary'; 
EXEC sp_addrolemember 'dbmanager', 'mike@contoso.com]';

    Note

    The dbmanager is a database role in master database so you can only add a database user to the dbmanager role. You cannot add a server-level login to database-level role.

  5. If necessary, configure a firewall rule to allow the new user to connect. (The new user might be covered by an existing firewall rule.)

Now the user can connect to the master database and can create new databases. The account creating the database becomes the owner of the database.

Login managers

The other administrative role is the login manager role. Members of this role can create new logins in the master database. If you wish, you can complete the same steps (create a login and user, and add a user to the loginmanager role) to enable a user to create new logins in the master. Usually logins aren't necessary as Microsoft recommends using contained database users, which authenticate at the database-level instead of using users based on logins. For more information, see Contained Database Users - Making Your Database Portable.

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 a Microsoft Entra authentication contained database user (if you have configured your environment for Microsoft Entra 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.

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:

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

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4