Connect to Azure SQL Database in Azure Purview

This article outlines the process to register an Azure SQL data source in Azure Purview including instructions to authenticate and interact with the Azure SQL database source

Supported capabilities

** Lineage is supported if dataset is used as a source/sink in Data Factory Copy activity

Known limitations

• Azure Purview doesn't support over 300 columns in the Schema tab and it will show "Additional-Columns-Truncated".

Prerequisites

• An Azure account with an active subscription. Create an account for free.

• An active Purview resource.

• You will need to be a Data Source Administrator and Data Reader to register a source and manage it in the Purview Studio. See our Azure Purview Permissions page for details.

Register

This section will enable you to register the Azure SQL DB data source and set up an appropriate authentication mechanism to ensure successful scanning of the data source.

Steps to register

It is important to register the data source in Azure Purview prior to setting up a scan for the data source.

1. Go to the Azure portal, and navigate to the Purview accounts page and select your Purview account

   

2. Open Purview Studio and navigate to the Data Map

   

3. Create the Collection hierarchy using the Collections menu and assign permissions to individual subcollections, as required

   

4. Navigate to the appropriate collection under the Sources menu and select the Register icon to register a new Azure SQL DB

   

5. Select the Azure SQL Database data source and select Continue

   

6. Provide a suitable Name for the data source, select the relevant Azure subscription, Server name for the SQL server and the collection and select on Apply

   

7. The Azure SQL Server Database will be shown under the selected Collection

   

Scan

Authentication for a scan

In order to have access to scan the data source, an authentication method in the Azure SQL Database needs to be configured. The following options are supported:

• SQL Authentication

• System-assigned managed identity - As soon as the Azure Purview account is created, a system-assigned managed identity (SAMI) is created automatically in Azure AD tenant, and has the same name as your Azure Purview account. Depending on the type of resource, specific RBAC role assignments are required for the Azure Purview SAMI to perform the scans.

• User-assigned managed identity (preview) - Similar to a SAMI, a user-assigned managed identity (UAMI) is a credential resource that can be used to allow Azure Purview to authenticate against Azure Active Directory. Depending on the type of resource, specific RBAC role assignments are required when using a UAMI credential to perform scans.

• Service Principal - In this method, you can create a new or use an existing service principal in your Azure Active Directory tenant.

Configure Azure AD authentication in the database account

The service principal or managed identity must have permission to get metadata for the database, schemas, and tables. It must also be able to query the tables to sample for classification.

• Configure and manage Azure AD authentication with Azure SQL

• Create Azure AD user in Azure SQL Database with the exact Purview's managed identity or your own service principal by following tutorial on Create the service principal user in Azure SQL Database. Assign proper permission (for example: db_datareader) to the identity. Example SQL syntax to create user and grant permission:

  CREATE USER [Username] FROM EXTERNAL PROVIDER
  GO
  
  EXEC sp_addrolemember 'db_datareader', [Username]
  GO
  

  Note

  The Username is your own service principal or Purview's managed identity. You can read more about fixed-database roles and their capabilities.

Using SQL Authentication for scanning

You can follow the instructions in CREATE LOGIN to create a login for Azure SQL Database if you don't have this login available. You will need username and password for the next steps.

1. Navigate to your key vault in the Azure portal

   

2. Select Settings > Secrets and select + Generate/Import

   

3. Enter the Name and Value as the password from your Azure SQL Database

   

4. Select Create to complete

5. If your key vault is not connected to Purview yet, you will need to create a new key vault connection

6. Finally, create a new credential using the key to set up your scan

   

   

Using a system or user assigned managed identity for scanning

It is important to give your Purview account's system managed identity or user-assigned managed identity the permission to scan the Azure SQL DB. You can add the SAMI or UAMI at the Subscription, Resource Group, or Resource level, depending on what you want it to have scan permissions on.

1. From the Azure portal, find either the subscription, resource group, or resource (for example, an Azure SQL Database) that you would like to allow the catalog to scan.

2. Select Access Control (IAM) in the left navigation and then select + Add --> Add role assignment

   

3. Set the Role to Reader and enter your Azure Purview account name or user-assigned managed identity under Select input box. Then, select Save to give this role assignment to your Purview account.

   

Using Service Principal for scanning

Creating a new service principal

If you need to Create a new service principal, it is required to register an application in your Azure AD tenant and provide access to Service Principal in your data sources. Your Azure AD Global Administrator or other roles such as Application Administrator can perform this operation.

Getting the Service Principal's Application ID

1. Copy the Application (client) ID present in the Overview of the Service Principal already created

   

Granting the Service Principal access to your Azure SQL Database

1. Navigate to your key vault in the Azure portal

   

2. Select Settings > Secrets and select + Generate/Import

   

3. Enter the Name of your choice and Value as the Client secret from your Service Principal

   

4. Select Create to complete

5. If your key vault is not connected to Purview yet, you will need to create a new key vault connection

6. Finally, create a new credential using the key to set up your scan

   

   

Firewall settings

If your database server has a firewall enabled, you will need to update the firewall to allow access in one of two ways:

1. Allow Azure connections through the firewall.
2. Install a Self-Hosted Integration Runtime and give it access through the firewall.

Allow Azure Connections

Enabling Azure connections will allow Azure Purview to reach and connect the server without updating the firewall itself. You can follow the How-to guide for Connections from inside Azure.

1. Navigate to your database account
2. Select the server name in the Overview page
3. Select Security > Firewalls and virtual networks
4. Select Yes for Allow Azure services and resources to access this server

Self-Hosted Integration Runtime

A self-hosted integration runtime (SHIR) can be installed on a machine to connect with a resource in a private network.

1. Create and install a self-hosted integration runtime on a personal machine, or a machine inside the same VNet as your database server.
2. Check your database server firewall to confirm that the SHIR machine has access through the firewall. Add the IP of the machine if it does not already have access.
3. If your Azure SQL Server is behind a private endpoint or in a VNet, you can use an ingestion private endpoint to ensure end-to-end network isolation.

Creating the scan

1. Open your Purview account and select the Open Purview Studio

2. Navigate to the Data map --> Sources to view the collection hierarchy

3. Select the New Scan icon under the Azure SQL DB registered earlier

   

If using SQL Authentication

1. Provide a Name for the scan, select Database selection method as Enter manually, enter the Database name and the Credential created earlier, choose the appropriate collection for the scan and select Test connection to validate the connection. Once the connection is successful, select Continue

   

If using a system or user assigned managed identity

1. Provide a Name for the scan, select the SAMI or UAMI under Credential, choose the appropriate collection for the scan

   

2. Select Test connection. On a successful connection, select Continue

   

If using Service Principal

1. Provide a Name for the scan, choose the appropriate collection for the scan, and select the Credential dropdown to select the credential created earlier.

   

2. Select Test connection. On a successful connection, select Continue.

Scoping and running the scan

1. You can scope your scan to specific folders and subfolders by choosing the appropriate items in the list.

   

2. Then select a scan rule set. You can choose between the system default, existing custom rule sets, or create a new rule set inline.

   

3. If creating a new scan rule set

   

4. You can select the classification rules to be included in the scan rule

   

   

5. Choose your scan trigger. You can set up a schedule or run the scan once.

   

6. Review your scan and select Save and run.

   

View Scan

1. Navigate to the data source in the Collection and select View Details to check the status of the scan

   

2. The scan details indicate the progress of the scan in the Last run status and the number of assets scanned and classified

   

3. The Last run status will be updated to In progress and then Completed once the entire scan has run successfully

   

Manage Scan

Scans can be managed or run again on completion

1. Select the Scan name to manage the scan

   

2. You can run the scan again, edit the scan, delete the scan

   

3. You can run an incremental scan or a full scan again

   

Next steps

Now that you have registered your source, follow the below guides to learn more about Purview and your data.

• Data insights in Azure Purview
• Lineage in Azure Purview
• Search Data Catalog