How to set up Microsoft Azure Synapse Analytics

Atlan supports crawling the following with the Microsoft Azure Synapse Analytics package:

• Dedicated SQL pools (formerly SQL DW)
• Serverless SQL pools

Atlan supports the following authentication methods for fetching metadata from Microsoft Azure Synapse Analytics:

• Basic authentication — this method uses a username and password to fetch metadata.
• Service principal authentication — this method requires a client ID, client secret, and tenant ID to fetch metadata.

Basic authentication

Create a login

You must create a login within the master database for the new user.

To create a login for the new user:

CREATE LOGIN <login_name> WITH PASSWORD = '<password>';

• Replace <login_name> with the name of the login.
• Replace <password> with the password for the login.

Create a user

You will need to create a new user for integrating with Atlan.

To create a user for the newly created login:

CREATE USER <username> FOR LOGIN <login_name>;

• Replace <username> with the username to use when integrating Atlan.
• Replace <login_name> with the name of the login used in the previous step.

Crawl assets and mine view lineage

You will need to connect to the target database that you want to crawl in Atlan.

The following grant crawls all your Microsoft Azure Synapse Analytics assets and mines lineage for views.

To grant the minimum permissions required to crawl assets and mine view lineage from a SQL pool:

GRANT VIEW DEFINITION ON DATABASE::<database_name> TO <username>;

• Replace <database_name> with the name of the database. You must grant these permissions to all the databases you want to crawl in Atlan.
• Replace <username> with the username created above.

Service principal authentication

Register app with Microsoft Entra ID

You will need to register your service principal application with Microsoft Entra ID and note down the values of the tenant ID, client ID, and client secret.

To register your app with Microsoft Entra ID:

1. Log in to the Azure portal.
2. In the search bar, search for Microsoft Entra ID, and select it from the dropdown list.
3. From the left menu of the Microsoft Entra ID page, click App registrations.
4. From the toolbar on the App registrations page, click + New registration.
5. On the Register an application page, for Name, enter a name for your service principal application and then click Register.
6. On the homepage of your newly created application, from the Overview screen, copy the values for the following fields and store them in a secure location:
   • Application (client) ID
   • Directory (tenant) ID
7. From the left menu of your newly created application page, click Certificates & secrets.
8. On the Certificates & secrets page, under Client secrets, click + New client secret.
9. In the Add a client secret screen, enter the following details:
   1. For Description, enter a description for your client secret.
   2. For Expiry, select when the client secret will expire.
   3. Click Add.
10. On the Certificates & secrets page, under Client secrets, for the newly created client secret, click the clipboard icon to copy the Value and store it in a secure location.

Create a service principal user

To create a service principal user:

CREATE USER <service_principal_display_name> FROM EXTERNAL PROVIDER;

• Replace <service_principal_display_name> with the name of the service principal you created in the previous step.

Grant SQL permissions

To grant SQL permissions to the service principal:

GRANT VIEW DEFINITION ON DATABASE::<database_name> TO <service_principal_display_name>;

• Replace <database_name> with the name of the database.
• Replace <service_principal_display_name> with the name of the service principal you created.

Assign Synapse RBAC role

To assign a Synapse role-based access control (RBAC) role to the service principal:

1. Open Synapse Studio and log in to your Synapse workspace.
2. From the left menu of your Synapse workspace, click the Manage tab. Then from under Security, click Access control.
3. From the options along the top of the Access control page, click + Add.
4. In the Add role assignment tab, enter the following details:
   1. For Scope, select Workspace as the scope.
   2. For Role, select Synapse Artifact User as the Synapse RBAC role to assign. The Synapse Artifact User role provides read access to published code artifacts and their outputs. Although it can create new artifacts, it can neither publish changes nor run code without additional permissions.
   3. For Select user, search for and select the service principal you created.
   4. Click Apply to assign the Synapse RBAC role to the service principal.

Mine query history

To mine query history from Microsoft Azure Synapse Analytics, complete these steps.

Enable query store

The Query Store is disabled by default for new Microsoft Azure Synapse Analytics databases. It stores 7 days of query history by default, which can be extended to 30 days.

To enable the Query Store for mining query history in Atlan, run the following T-SQL command:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

• Replace <database_name> with the name of the database.

Grant permissions

To mine query history, grant the following permissions:

• Basic authentication:

  GRANT VIEW DATABASE STATE TO <username>

  • Replace <username> with the username you created for basic authentication.
• Service principal authentication:

  GRANT VIEW DATABASE STATE TO <service_principal_display_name>

  • Replace <service_principal_display_name> with the name of the service principal you created for service principal authentication.

Find your SQL pool server

To find the server name of your SQL pool for crawling Microsoft Azure Synapse Analytics:

1. Open Synapse Studio.
2. On the login page, select Synapse Workspace.
3. From the left menu of your Synapse workspace, click the Manage tab. Then from under Analytics pools, click SQL pools.
4. On the SQL pools page, under Name, select your SQL pool.
5. In the Properties form, navigate to Workspace SQL endpoint and copy the server name of your SQL pool and save it in a temporary location.