Connect to and manage an on-premises SQL server instance in Azure Purview
This article outlines how to register on-premises SQL server instances, and how to authenticate and interact with an on-premises SQL server instance in Azure Purview. For more information about Azure Purview, read the introductory article.
Supported capabilities
| Metadata Extraction | Full Scan | Incremental Scan | Scoped Scan | Classification | Access Policy | Lineage |
|---|---|---|---|---|---|---|
| Yes | Yes | Yes | Yes | Yes | No | Yes** |
** Lineage is supported if dataset is used as a source/sink in Data Factory Copy activity
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.
Set up the latest self-hosted integration runtime. For more information, see the create and configure a self-hosted integration runtime guide.
Register
This section describes how to register an on-premises SQL server instance in Azure Purview using the Purview Studio.
Authentication for registration
There is only one way to set up authentication for SQL server on-premises:
- SQL Authentication
SQL Authentication to register
The SQL account must have access to the master database. This is because the sys.databases is in the master database. The Purview scanner needs to enumerate sys.databases in order to find all the SQL databases on the server.
Creating a new login and user
If you would like to create a new login and user to be able to scan your SQL server, follow the steps below:
Note
All the steps below can be executed using the code provided here
Navigate to SQL Server Management Studio (SSMS), connect to the server, navigate to security, select and hold (or right-click) on login and create New login. Make sure to select SQL authentication.
Select Server roles on the left navigation and ensure that public role is assigned.
Select User mapping on the left navigation, select all the databases in the map and select the Database role: db_datareader.
Select OK to save.
Navigate again to the user you created, by selecting and holding (or right-clicking) and selecting Properties. Enter a new password and confirm it. Select the 'Specify old password' and enter the old password. It is required to change your password as soon as you create a new login.
Storing your SQL login password in a key vault and creating a credential in Purview
- Navigate to your key vault in the Azure portal1. Select Settings > Secrets
- Select + Generate/Import and enter the Name and Value as the password from your SQL server login
- 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 username and password to setup your scan
Steps to register
Navigate to your Purview account
Under Sources and scanning in the left navigation, select Integration runtimes. Make sure a self-hosted integration runtime is set up. If it is not set up, follow the steps mentioned here to create a self-hosted integration runtime for scanning on an on-premises or Azure VM that has access to your on-premises network.
Select Data Map on the left navigation.
Select Register
Select SQL server and then Continue
Provide a friendly name, which will be a short name you can use to identify your server, and the server endpoint.
Select Finish to register the data source.
Scan
Follow the steps below to scan on-premises SQL server instances to automatically identify assets and classify your data. For more information about scanning in general, see our introduction to scans and ingestion
Create and run scan
To create and run a new scan, do the following:
Select the Data Map tab on the left pane in the Purview Studio.
Select the SQL Server source that you registered.
Select New scan
Select the credential to connect to your data source.
You can scope your scan to specific tables 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.
Choose your scan trigger. You can set up a schedule or run the scan once.
Review your scan and select Save and run.
View your scans and scan runs
To view existing scans, do the following:
Go to the Purview Studio. Select the Data Map tab under the left pane.
Select the desired data source. You will see a list of existing scans on that data source under Recent scans, or can view all scans under the Scans tab.
Select the scan that has results you want to view.
This page will show you all of the previous scan runs along with the status and metrics for each scan run. It will also display whether your scan was scheduled or manual, how many assets had classifications applied, how many total assets were discovered, the start and end time of the scan, and the total scan duration.
Manage your scans - edit, delete, or cancel
To manage or delete a scan, do the following:
Go to the Purview Studio. Select the Data Map tab under the left pane.
Select the desired data source. You will see a list of existing scans on that data source under Recent scans, or can view all scans under the Scans tab.
Select the scan you would like to manage. You can edit the scan by selecting Edit scan.
You can cancel an in progress scan by selecting Cancel scan run.
You can delete your scan by selecting Delete scan.
Note
- Deleting your scan does not delete catalog assets created from previous scans.
- The asset will no longer be updated with schema changes if your source table has changed and you re-scan the source table after editing the description in the schema tab of Purview.
Next steps
Now that you have registered your source, follow the below guides to learn more about Purview and your data.