azuresynapse-configure-connection-profile
Connection Profile Name: Enter a name that uniquely identifies the external resource.
Namespace:.Select the namespace where the profile will be stored. All users who will use this profile must have READ and SELECT permissions on the namespace.
Endpoint Type: Select Azure Synapse.
Authentication Type: Select the type of authentication you want to use, then set the properties for that type as described below.
Setting Entra ID properties
Entra ID was formerly known as Azure Active Directory.
Connection URL: Enter the connection string to connect to Azure Synapse. You can find this in the Azure Synapse UI at Settings > Connection String > JDBC.
After specifying the connection URL, click Sign in using Entra ID. Log in with an Entra ID organization (work) account that has the db_owner role or the bulkadmin, db_datareader, db_datawriter, and db_ddladmin roles. Once you log in successfully, close the browser window, return to the connection profile page, and test the connection.
The Entra ID refresh token will expire in 90 days. Before it expires, sign in again to get a new refresh token, and applications using this connection profile will automatically switch to the new token. If you fail to do this, applications using this connection profile will halt when the refresh token expires.
Setting Microsoft Entra Service Principal properties
To use an Entra Service Principal to connect to Azure Synapse:
Register an application with the Microsoft identity platform as described in Learn / Microsoft Entra / Microsoft identity platform / Quickstart: Register an application with the Microsoft identity platform.
Go to the registered app, select API permissions, and add the Azure SQL Database (user_impersonation) permission.
Click Certificates and secrets on the left panel and click Add client secret. Make note of the client secret value as you will need to provide it in the connection profile in Striim.
In the Synapse query editor, assign the app to the Synapse database, replacing
<service principal name>with the same of the service principal. The service principal must have the db_owner role or the bulkadmin, db_datareader, db_datawriter, and db_ddladmin roles.CREATE USER <service principal name> FROM EXTERNAL PROVIDER;
Assign the service principal the
db_ownerprivilege:EXEC sp_addrolemember 'db_owner', '<service principal name>';
Or alternatively the
bulkadmin,db_datareader,db_datawriter, anddb_ddladminprivileges:EXEC sp_addrolemember 'bulkadmin', '<service principal name>'; EXEC sp_addrolemember 'db_datareader', '<service principal name>'; EXEC sp_addrolemember 'db_datawriter', '<service principal name>'; EXEC sp_addrolemember 'db_ddladmin', '<service principal name>';
Set the following properties in your connection profile:
Client ID: Enter the Application (client) ID from your application's Overview page.
Client Secret: Enter the Client Secret.
Connection URL: Enter the connection string to connect to Azure Synapse.
When you update the Client ID and Cliient Secret, applications using this connection profile will automatically use them.
Setting SQL Password properties
Username: Enter the user name Stirim will use to log in to the Azure Synapse instance specified in Connection URL.
Password: Enter the password for the specified user name.
Connection URL: Enter the connection string to connect to Azure Synapse.
When you update the password, applications using this connection profile will automatically switch to the new password.