Microsoft Azure Synapse SQL connection properties

Prerequisites

Azure Active Directory authentication

Import a server certificate

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.

Managed Identity authentication

1. 1Create an Azure virtual machine.
2. 2Install the Secure Agent on the Azure virtual machine.
3. 3Enable system assigned identity or user assigned identity for the Azure virtual machine.
If you enable both and do not specify the client ID, the system assigned identity is used for authentication.
4. 4After you add or remove a managed identity, restart the Azure virtual machine.

Connect to Microsoft Azure Synapse SQL

Before you begin

Connection details

Azure storage types

Advanced settings

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.
• •When you use managed identity authentication to connect to Microsoft Azure Synapse SQL, grant permissions to the virtual machine and user identity. For example, GRANT CONTROL TO <virtual machine name>; and GRANT CONTROL TO <user identity name>;