You can configure Microsoft SQL Server, Azure Active Directory, and Service Principal authentication types to access Microsoft Azure Synapse SQL.
Before you configure the connection properties, you need to keep the authentication details handy based on the authentication type that you want to use.
Azure Active Directory authentication
To connect to Microsoft Azure Synapse SQL using Azure Active Directory (AAD) authentication, you need to create an Azure Active Directory administrator and an Azure Active Directory user.
Import a server certificate
If a trust store file isn't configured for your organization and you want to use AAD authentication with Active Directory Federation Services in Azure, you need to import the server certificate. For more information, contact your organization administrator.
Import the server certificate to the following location:
To add new users to your Azure Active Directory, you must have an administrator role.
To set up an Azure Active Directory administrator for AAD and Microsoft SQL Server that hosts your Microsoft Azure Synapse SQL, perform the following steps:
1Log on to the Microsoft Azure portal using your credentials.
The Dashboard page appears.
2From the All Resources page, select the Microsoft SQL Server that hosts Microsoft Azure Synapse SQL.
3Under Settings displayed for Microsoft SQL Server, select the Active Directory admin option.
The image shows the Active Directory admin settings:
4Click Set admin.
The Add admin page appears.
5Enter the email ID that you want to use as admin, and then click Select.
6Click Save.
Create an Azure Active Directory user
Create an AAD user and use the AAD user credentials when you configure a Microsoft Azure Synapse SQL connection with AAD authentication.
Perform the following steps to create an AAD user:
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.
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;
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
Service Principal authentication involves the use of a service principal identity to authenticate and authorize access to Azure resources. Before you use service principal authentication to connect to Microsoft Azure Synapse SQL, be sure to complete certain prerequisites.
1Register a service principal application.
2Configure a service principal user for a dedicated SQL pool.
