Prerequisites

Azure Active Directory authentication

Create an Azure Active Directory administrator

1. 1Log on to the Microsoft Azure portal using your credentials.
The Dashboard page appears.
2. 2From the All Resources page, select the Microsoft SQL Server that hosts Microsoft Azure Synapse SQL.
3. 3Under Settings displayed for Microsoft SQL Server, select the Active Directory admin option.
The image shows the Active Directory admin settings:

4. 4Click Set admin.
The Add admin page appears.
5. 5Enter the email ID that you want to use as admin, and then click Select.
6. 6Click Save.

Create an Azure Active Directory user

1. 1Connect to Microsoft Azure Synapse SQL using the Azure Active Directory administrator created in the previous steps.
You can use Microsoft SQL Server Management Studio to connect to the Microsoft Azure Synapse SQL.
2. 2In a new query window in Microsoft SQL Server Management Studio, run the following command to create an AAD user:
create user [user@foobar.com] from external provider;
3. 3Assign the following privileges to the user:
CREATE USER [username] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [username]
ALTER ROLE db_datawriter ADD MEMBER [username]
GRANT EXECUTE TO [username]
grant ALTER ANY EXTERNAL DATA SOURCE to [username];
grant create table to [username];
grant create schema to [username];
grant select to [username];
grant update to [username];
grant insert to [username];
grant delete to [username];
grant create view to [username];
grant select on schema :: sys to [username];
grant control to [username];
EXEC sp_addrolemember 'db_owner','[username]';
ALTER ROLE db_owner ADD MEMBER [username]

Service Principal authentication

1. 1Register a service principal application.
2. 2Configure a service principal user for a dedicated SQL pool.

Permissions

• •Ensure that a default schema is present at the account level or user or group level in Microsoft Azure SQL Data Warehouse.
• •Verify that either the db_owner privilege or the following more granular privileges are granted to the user to connect to Microsoft Azure SQL Data Warehouse and perform operations successfully:
• - EXEC sp_addrolemember 'db_datareader', '<user>'; // Alternately assign permission to the individual table.
• - EXEC sp_addrolemember 'db_datawriter', '<user>'; // Alternately assign permission to the individual table.
• - GRANT ALTER ANY EXTERNAL DATA SOURCE TO <user>;
• - GRANT ALTER ANY EXTERNAL FILE FORMAT TO <user>;
• - GRANT CONTROL TO <user>; // To grant all permissions on the database.
or
GRANT ALTER ANY SCHEMA TO <user>;// To grant permissions only on the schema.
• - GRANT CREATE TABLE TO <user>;
• - Assign required privileges for tasks performed through Pre-SQL and Post-SQL commands.
• •If you configure the staging schema name in the connection properties, ensure the following additional privileges are granted to the user:
• - ALTER ROLE db_datareader ADD MEMBER <user>;
• - GRANT ALTER ANY EXTERNAL DATA SOURCE TO <user>;
• - GRANT ALTER ANY EXTERNAL FILE FORMAT TO <user>;
• - GRANT CREATE TABLE TO <user>;
• - GRANT ALTER ON SCHEMA::<staging_schema_name> TO <user>;// Ensures this user does not have access to create or drop an external table in another schema.
• - GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::<db_credential_name> TO <user>;// Ensures this user does not have access to drop the credentials.
For example, GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::db_creds1 TO srvls;
• •If you have the ALTER ANY SCHEMA permissions, you must create the Master Key, Database Scoped Credential, and External Data Source in Microsoft Azure Synapse SQL that require the CONTROL permission on the database and specify the external data source when you create a connection.
Also, Microsoft Azure Synapse SQL Connector does not delete the Database Scoped Credential and External Data Source. You must manually delete the Database Scoped Credential and External Data Source.