Register and scan an on-premises SQL server

This article outlines how to register a SQL server data source in Purview and set up a scan on it.

Supported Capabilities

The SQL server on-premises data source supports the following functionality:

  • Full and incremental scans to capture metadata and classification in an on-premises network or a SQL server installed on an Azure VM.

  • Lineage between data assets for ADF copy/dataflow activities

SQL server on-premises data source supports:

  • every version of SQL from SQL server 2019 back to SQL server 2000

  • Authentication method: SQL authentication

Prerequisites

Setting up authentication for a scan

There is only one way to set up authentication for SQL server on-premises:

  • SQL Authentication

SQL authentication

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.

Using an existing server administrator

If you plan to use an existing server admin (sa) user to scan your on-premises SQL server, ensure the following:

  1. sa is not a Windows authentication account.

  2. The server level login that you are planning to use must have server roles of public and sysadmin. You can verify this by connecting to the server, navigating to SQL Server Management Studio (SSMS), navigating to security, selecting the login you are planning to use, right-clicking Properties and then selecting Server roles.

    Server level login.

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

  1. Navigate to SQL Server Management Studio (SSMS), connect to the server, navigate to security, right-click on login and create New login. Make sure to select SQL authentication.

    Create new login and user.

  2. Select Server roles on the left navigation and ensure that public role is assigned.

  3. Select User mapping on the left navigation, select all the databases in the map and select the Database role: db_datareader.

    user mapping.

  4. Click OK to save.

  5. Navigate again to the user you created, by 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.

    change password.

Storing your SQL login password in a key vault and creating a credential in Purview

  1. Navigate to your key vault in the Azure portal1. Select Settings > Secrets
  2. Select + Generate/Import and enter the Name and Value as the password from your SQL server login
  3. Select Create to complete
  4. If your key vault is not connected to Purview yet, you will need to create a new key vault connection
  5. Finally, create a new credential using the username and password to setup your scan

Register a SQL server data source

  1. Navigate to your Purview account

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

  3. Select Data Map on the left navigation.

  4. Select Register

  5. Select SQL server and then Continue

    Set up the SQL data source.

  6. Provide a friendly name and server endpoint and then select Finish to register the data source. If, for example, your SQL server FQDN is foobar.database.windows.net, then enter foobar as the server endpoint.

Creating and running a scan

To create and run a new scan, do the following:

  1. Select the Data Map tab on the left pane in the Purview Studio.

  2. Select the SQL Server source that you registered.

  3. Select New scan

  4. Select the credential to connect to your data source.

    Set up scan

  5. You can scope your scan to specific tables by choosing the appropriate items in the list.

    Scope your scan

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

    Scan rule set

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

    trigger

  8. Review your scan and select Save and run.

Viewing your scans and scan runs

To view existing scans, do the following:

  1. Go to the Purview studio. Select the Data Map tab under the left pane.

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

  3. Select the scan that has results you want to view.

  4. 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:

  1. Go to the Purview studio. Select the Data Map tab under the left pane.

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

  3. Select the scan you would like to manage. You can edit the scan by selecting Edit scan.

  4. You can cancel an in progress scan by selecting Cancel scan run.

  5. You can delete your scan by selecting Delete scan.

Next steps