## Set up Microsoft SQL Server

URL: https://docs.atlan.com/apps/connectors/database/microsoft-sql-server/how-tos/set-up-microsoft-sql-server

> Configure authentication and permissions for Microsoft SQL Server to enable Atlan to crawl metadata from your database.

Setting up Microsoft SQL Server authentication enables Atlan to securely connect to your database and extract metadata. This process configures the necessary credentials and permissions for Atlan to discover and catalog your SQL Server assets.

## Prerequisites

Before you begin, make sure you have:

* Administrative access to your Microsoft SQL Server instance or contact with your SQL Server administrator
* Access to Microsoft Entra ID with Application Administrator or Cloud Application Administrator permissions (required for service principal authentication only)

## Set up authentication

Atlan supports the following authentication methods for fetching metadata from Microsoft SQL Server:

- **Basic authentication**: Uses a SQL Server username and password to fetch metadata.
- **Service principal authentication**: Uses a client ID, client secret, and tenant ID to fetch metadata using Microsoft Entra ID (Azure AD).
- **Windows (NTLM) authentication**: Uses Windows Active Directory credentials to fetch metadata.

### Basic authentication

Use SQL Server native authentication with username and password credentials.

1. Create a login with a specific password to integrate into Atlan:

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

2. Create a user for that 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.

3. Grant permissions for crawling assets and mining view lineage** (minimum required permissions):

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

 - Replace `<database_name>` with the name of the database.
 - Replace `<username>` with the username you created.

4. Grant additional permissions to preview and query assets:

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

 - Replace `<database_name>` with the name of the database.
 - Replace `<username>` with the username you created.

:::warning
You _must_ grant permissions to the user for all the databases you want to crawl in Atlan except the system databases (`master`, `tempdb`, `msdb`, `model`). The Microsoft SQL Server crawler only fetches database and schema names without these permissions and no other metadata for other asset types.
:::

### Service principal

Use Microsoft Entra ID (Azure AD) service principal authentication for enhanced security and centralized identity management.

1. You need to register your service principal application with Microsoft Entra ID and note the values of the client ID, client secret, and tenant 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 expires.
 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.

2. Create the login at the server level:

 ```sql
 CREATE LOGIN <login_name> FROM EXTERNAL PROVIDER;
 ```

 - Replace `<login_name>` with the display name of your Microsoft Entra application (service principal). This makes SQL Server recognize that Entra principal as a valid login.

3. Grant permissions for each database you want to crawl, create a user mapped to the login and grant permissions:

 - For crawling assets and mining view lineage:

 ```sql
 CREATE USER <username> FOR LOGIN <login_name>;
 GRANT VIEW DEFINITION ON DATABASE::<database_name> TO <username>;
 ```

 - `<username>` can be the same as `<login_name>` (simpler) or different if you prefer. SQL Server separates server-level logins from database-level users.
 - Replace `<database_name>` with the name of the database.

 - Grant additional permissions to preview and query assets:

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

 - Replace `<database_name>` with the name of the database.
 - Replace `<username>` with the username you created.

 :::warning
 You _must_ grant permissions to the user for all the databases you want to crawl in Atlan except the system databases (`master`, `tempdb`, `msdb`, `model`). The Microsoft SQL Server crawler only fetches database and schema names without these permissions and no other metadata for other asset types.
 :::

### Windows (NTLM)

Use Windows Active Directory credentials to authenticate with SQL Server through the NTLM protocol.

1. Create a Windows login on your SQL Server instance:

 ```sql
 CREATE LOGIN [<domain>\<login_name>] FROM WINDOWS;
 ```

 - Replace `<domain>` with your Active Directory domain name (for example, `CORP`).
 - Replace `<login_name>` with the Windows username.

2. Create a user for that login:

 ```sql
 CREATE USER <username> FOR LOGIN [<domain>\<login_name>];
 ```

 - Replace `<username>` with the database username to use when integrating Atlan.
 - Replace `<domain>\<login_name>` with the domain and login name used in the previous step.

3. Grant permissions for crawling assets (minimum required permissions):

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

 - Replace `<database_name>` with the name of the database.
 - Replace `<username>` with the username you created.

4. Grant additional permissions to preview and query assets:

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

 - Replace `<database_name>` with the name of the database.
 - Replace `<username>` with the username you created.

:::warning
You _must_ grant permissions to the user for all the databases you want to crawl in Atlan except the system databases (`master`, `tempdb`, `msdb`, `model`). The Microsoft SQL Server crawler only fetches database and schema names without these permissions and no other metadata for other asset types.
:::

When configuring the connection in Atlan, provide the following credentials:

- **Username**: Your Windows username without the domain prefix (for example, `atlan_service`, not `CORP\atlan_service`).
- **Password**: Your Windows password.
- **Windows Domain**: Your Active Directory domain name (for example, `CORP`).

## Next steps

Once you have configured authentication and permissions:

* [Crawl Microsoft SQL Server assets](https://docs.atlan.com/llms/connectors/microsoft-sql-server/crawl-microsoft-sql-server/llms.txt) - Set up and run the crawler to extract metadata from your database into Atlan.

---