You can configure Microsoft SQL Server, Azure Active Directory, Managed Identity, and Service Principal authentication types to access Microsoft Azure Synapse SQL.
You can also connect to a serverless SQL pool when you read data from Microsoft Azure Synapse SQL. When you connect to a serverless SQL pool, you can configure Microsoft SQL Server, Azure Active Directory, and Managed Identity authentication types to access Microsoft Azure Synapse SQL. For more information on the authentication details, see the Informatica How-To Library article, Prerequisites to connect to a serverless SQL pool.
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 serverless SQL pool.
3Configure a service principal user for a dedicated SQL pool.
Managed Identity authentication uses managed identities in Azure Active Directory to authenticate and authorize access to Azure resources securely.
When you use managed identity authentication to connect to Microsoft Azure Synapse SQL, the user for the system assigned identity is the virtual machine for which you enable the identity. The user for the user assigned identity is the user identity that you create in the Azure portal.
Before you use managed identity authentication to connect to Microsoft Azure Synapse SQL or Microsoft Azure Data Lake Storage Gen2, be sure to complete certain prerequisites.
1Create an Azure virtual machine.
2Install the Secure Agent on the Azure virtual machine.
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.
4After you add or remove a managed identity, restart the Azure virtual machine.
Serverless SQL pool
You can configure a Microsoft Azure Synapse SQL connection to connect to a serverless SQL pool. A serverless SQL pool does not store data or require any preconfigured infrastructure. You can connect to a serverless SQL pool when you want to query external tables that reference data stored in Microsoft Azure Data Lake Storage Gen2 or when you want to use queries with the OPENROWSET function.
To connect to a serverless SQL pool, specify the Azure DW JDBC URL connection string for a serverless SQL pool in the Microsoft Azure Synapse SQL connection.
Before you connect to a serverless SQL pool to read from Microsoft Azure SQL Data Warehouse, be sure to complete the following prerequisites:
1Configure an Azure Analytics serverless pool workspace.
2Create an SQL database in the serverless pool workspace.
3Get the JDBC URL for the following authentication types:
- Microsoft SQL Server authentication
- Azure Active Directory (AAD) authentication
- Managed Identity authentication
4To use Service Principal authentication to connect to Microsoft Azure Data Lake Storage Gen2 to stage the files, get credentials for Service Principal.
5Configure steps to read data from a file using the OPENROWSET query or by creating an external table.
