Connect to Azure SQL Database in Azure Purview

Članak
11/17/2021
8 min za pročitati

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

Metadata Extraction	Full Scan	Incremental Scan	Scoped Scan	Classification	Access Policy	Lineage
Yes	Yes	Yes	Yes	Yes	No	No**

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

Go to the Azure portal, and navigate to the Purview accounts page and select your Purview account
Open Purview Studio and navigate to the Data Map
Create the Collection hierarchy using the Collections menu and assign permissions to individual subcollections, as required
Navigate to the appropriate collection under the Sources menu and select the Register icon to register a new Azure SQL DB
Select the Azure SQL Database data source and select Continue
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
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

Note

Only the server-level principal login (created by the provisioning process) or members of the loginmanager database role in the master database can create new logins. It takes about 15 minutes after granting permission, the Purview account should have the appropriate permissions to be able to scan the resource(s).

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.

Navigate to your key vault in the Azure portal
Select Settings > Secrets and select + Generate/Import
Enter the Name and Value as the password from your Azure SQL Database
Select Create to complete
If your key vault is not connected to Purview yet, you will need to create a new key vault connection
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.

Note

You need to be an owner of the subscription to be able to add a managed identity on an Azure resource.

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.
Select Access Control (IAM) in the left navigation and then select + Add --> Add role assignment
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

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

Navigate to your key vault in the Azure portal
Select Settings > Secrets and select + Generate/Import
Enter the Name of your choice and Value as the Client secret from your Service Principal
Select Create to complete
If your key vault is not connected to Purview yet, you will need to create a new key vault connection
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:

Allow Azure connections through the firewall.
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.

Navigate to your database account
Select the server name in the Overview page
Select Security > Firewalls and virtual networks
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.

Create and install a self-hosted integration runtime on a personal machine, or a machine inside the same VNet as your database server.
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.
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

Open your Purview account and select the Open Purview Studio
Navigate to the Data map --> Sources to view the collection hierarchy
Select the New Scan icon under the Azure SQL DB registered earlier

If using SQL Authentication

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

Provide a Name for the scan, select the SAMI or UAMI under Credential, choose the appropriate collection for the scan
Select Test connection. On a successful connection, select Continue

If using Service Principal

Provide a Name for the scan, choose the appropriate collection for the scan, and select the Credential dropdown to select the credential created earlier.
Select Test connection. On a successful connection, select Continue.

Scoping and running the scan

You can scope your scan to specific folders and subfolders by choosing the appropriate items in the list.
Then select a scan rule set. You can choose between the system default, existing custom rule sets, or create a new rule set inline.
If creating a new scan rule set
You can select the classification rules to be included in the scan rule
Choose your scan trigger. You can set up a schedule or run the scan once.
Review your scan and select Save and run.

View Scan

Navigate to the data source in the Collection and select View Details to check the status of the scan
The scan details indicate the progress of the scan in the Last run status and the number of assets scanned and classified
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

Select the Scan name to manage the scan
You can run the scan again, edit the scan, delete the scan
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.