## Set up Microsoft Azure Synapse Analytics

URL: https://docs.atlan.com/apps/connectors/data-warehouses/microsoft-azure-synapse-analytics/how-tos/set-up-microsoft-azure-synapse-analytics

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

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

- [Dedicated SQL pools](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-overview-what-is) (formerly SQL DW)
- [Serverless SQL pools](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/on-demand-workspace-overview)

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

- [Basic authentication](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/set-up-microsoft-azure-synapse-analytics/llms.txt) - this method uses a username and password to fetch metadata.
- [Service principal authentication](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/set-up-microsoft-azure-synapse-analytics/llms.txt) - this method requires a client ID, client secret, and tenant ID to fetch metadata.

## Basic authentication

:::warning Who can do this?
 You will need your [Microsoft Azure Synapse Analytics administrator](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/sql-authentication?tabs=serverless) 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:

```sql
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](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/crawl-microsoft-azure-synapse-analytics/llms.txt).

To create a user for the [newly created login](#create-a-login):

```sql
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](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/crawl-microsoft-azure-synapse-analytics/llms.txt).

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:

```sql
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](#create-a-user).

## Service principal authentication

To configure service principal authentication, complete the following steps in sequence:

1. Register app with Microsoft Entra ID
2. Create a service principal user
3. Grant SQL permissions
4. Assign Synapse RBAC role

### Register app with Microsoft Entra ID

:::warning Who can do this?
 You will need your _[Cloud Application Administrator](https://learn.microsoft.com/en-us/entra/identity/role-based-access-control/permissions-reference#cloud-application-administrator)_ or _[Application Administrator](https://learn.microsoft.com/en-us/entra/identity/role-based-access-control/permissions-reference#application-administrator)_ to complete these steps  - you may not have access yourself. This will be required if the creation of registered applications is not enabled for the entire organization.

:::

You will need to [register your service principal application](https://learn.microsoft.com/en-us/azure/healthcare-apis/register-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](https://portal.azure.com/).
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:

```sql
CREATE USER <service_principal_display_name> FROM EXTERNAL PROVIDER;
```

- Replace `<service_principal_display_name>` with the name of the [service principal you created](#create-a-service-principal-user) in the previous step.

### Grant SQL permissions

To grant SQL permissions to the [service principal](#create-a-service-principal-user):

```sql
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](#create-a-service-principal-user).

### Assign Synapse RBAC role

:::warning Who can do this?
 You will need your [Synapse Administrator](https://learn.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-synapse-rbac-roles#built-in-synapse-rbac-roles-and-scopes) to complete these steps - you may not have access yourself.

:::

To [assign a Synapse role-based access control (RBAC) role](https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-manage-synapse-rbac-role-assignments) to the service principal:

1. Open [Synapse Studio](https://web.azuresynapse.net/en/) 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_](https://learn.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-synapse-rbac-roles#built-in-synapse-rbac-roles-and-scopes) 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](#create-a-service-principal-user).
 4. Click **Apply** to assign the Synapse RBAC role to the service principal.

## Mine query history

This section is required only if you want to use the Synapse Miner workflow to mine query history. These steps aren't required for crawling Synapse assets.

:::warning

Atlan currently only supports mining query history for dedicated SQL pools with the [Microsoft Azure Synapse Analytics miner](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/mine-microsoft-azure-synapse-analytics/llms.txt). Mining query history for serverless SQL pools is currently not supported.

:::

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

### Enable query store

The [Query Store](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-history-storage-analysis) 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](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/mine-microsoft-azure-synapse-analytics/llms.txt), run the following T-SQL command:

```sql
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](#basic-authentication):

 ```sql
 GRANT VIEW DATABASE STATE TO <username>
 ```

 - Replace `<username>` with the [username](#create-a-user) you created for basic authentication.
- [Service principal authentication](#service-principal-authentication):

 ```sql
 GRANT VIEW DATABASE STATE TO <service_principal_display_name>
 ```

 - Replace `<service_principal_display_name>` with the name of the [service principal you created](#create-a-service-principal-user) for service principal authentication.

## Find your SQL pool server

To find the server name of your SQL pool for [crawling Microsoft Azure Synapse Analytics](https://docs.atlan.com/llms/connectors/microsoft-azure-synapse-analytics/crawl-microsoft-azure-synapse-analytics/llms.txt):

1. Open [Synapse Studio](https://web.azuresynapse.net/en/).
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.

---