How to set up Microsoft Azure Synapse Analytics

πŸ’ͺ Did you know? Atlan currently only supports crawling dedicated SQL pools (formerly SQL DW) with the Microsoft Azure Synapse Analytics package. Serverless SQL pools are currently not supported.

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

Basic authentication

πŸ€“ Who can do this? You will need your Microsoft Azure Synapse Analytics administrator to run these commands β€” you may not have access yourself.

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 dedicated 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.

(Optional) Preview and query assets

To grant the minimum permissions required to also preview sample data and query assets from Microsoft Azure Synapse Analytics:

GRANT SELECT ON DATABASE::<database_name> TO <username>;

Service principal authentication

Register app with Microsoft Entra ID

πŸ€“ Who can do this? You will need your Microsoft Entra ID administrator to complete these steps β€” you may not have access yourself.

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;

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

πŸ€“ Who can do this? You will need your Synapse Administrator to complete these steps β€” you may not have access yourself.

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:

Find your SQL pool server

To find the server name of your dedicated 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 dedicated SQL pool and save it in a temporary location.

Related articles

Was this article helpful?
0 out of 0 found this helpful