Connect to and manage an on-premises SQL server instance in Microsoft 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 Microsoft Purview. For more information about Microsoft Purview, read the introductory article.

Supported capabilities

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

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

The supported SQL Server versions are 2005 and above. SQL Server Express LocalDB is not supported.

When scanning on-premises SQL server, Microsoft Purview supports:

  • Extracting technical metadata including:

    • Instance
    • Databases
    • Schemas
    • Tables including the columns
    • Views including the columns

When setting up scan, you can choose to specify the database name to scan one database, and you can further scope the scan by selecting tables and views as needed. The whole SQL Server instance will be scanned if database name is not provided.

Prerequisites

Register

This section describes how to register an on-premises SQL server instance in Microsoft Purview using the Microsoft Purview governance portal.

Authentication for registration

There are two ways to set up authentication for SQL server on-premises:

  • SQL Authentication
  • Windows Authentication

Set up SQL server authentication

If SQL Authentication is applied, ensure the SQL Server deployment is configured to allow SQL Server and Windows Authentication.

To enable this, within SQL Server Management Studio (SSMS), navigate to "Server Properties" and change from "Windows Authentication Mode" to "SQL Server and Windows Authentication mode".

The Server Properties window is open with the security page selected. Under Server authentication, S Q L Server and Windows Authentication mode is selected.

If Windows Authentication is applied, configure the SQL Server deployment to use Windows Authentication mode.

A change to the Server Authentication will require a restart of the SQL Server Instance and SQL Server Agent, this can be triggered within SSMS by navigating to the SQL Server instance and selecting "Restart" within the right-click options pane.

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:

The account must have access to the master database. This is because the sys.databases is in the master database. The Microsoft Purview scanner needs to enumerate sys.databases in order to find all the SQL databases on the server.

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, select and hold (or right-click) on login and create New login. If Windows Authentication is applied, select "Windows authentication". If SQL Authentication is applied, 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. Select OK to save.

  5. If SQL Authentication is applied, 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.

    change password.

Storing your SQL login password in a key vault and creating a credential in Microsoft 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 Microsoft Purview yet, you will need to create a new key vault connection
  5. Finally, create a new credential using the username and password to set up your scan. Make sure the right authentication method is selected when creating a new credential. If SQL Authentication is applied, select "SQL authentication" as the authentication method. If Windows Authentication is applied, then select "Windows authentication".

Steps to register

  1. Navigate to your Microsoft 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, which will be a short name you can use to identify your server, and the server endpoint.

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

  1. Select the Data Map tab on the left pane in the Microsoft Purview governance portal.

  2. Select the SQL Server source that you registered.

  3. Select New scan

  4. Select the credential to connect to your data source. The credentials are grouped and listed under different authentication methods.

    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.

View your scans and scan runs

To view existing scans, do the following:

  1. Go to the Microsoft Purview governance portal. 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 Microsoft Purview governance portal. 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.

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 Microsoft Purview.

Next steps

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