Share via

Connect to and manage an on-premises SQL server instance in Microsoft Purview

  • Article

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 Labeling Access Policy Lineage Data Sharing Live view
Yes Yes Yes Yes Yes Yes No Limited** No 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 isn't supported.

When you scan an 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 isn't provided.

Prerequisites

Register

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

  1. Accessing the Microsoft Purview Data Map depends on which portal experience you're using.

    • If you're using the Microsoft Purview portal, navigate directly to the portal (https://purview.microsoft.com) and select the Data Map solution.
    • If you're using the classic Microsoft Purview governance portal, navigate directly to the portal (https://web.purview.azure.com), select your Microsoft Purview account, and select Data map from the left menu.

  2. Under Source management, select Integration runtimes. Make sure a self-hosted integration runtime is set up. If it isn't set up, follow the steps mentioned in prerequisites to create a self-hosted integration runtime for scanning from an on-premises or Azure VM that has access to your on-premises network.

  3. Select Data sources 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

Authentication for scanning

There are two authentication methods supported for SQL server on-premises:

  • SQL Authentication
  • Windows Authentication - Not supported by a Kubernetes SHIR.

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, the 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 portal. 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 isn't connected to Microsoft Purview yet, you'll 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.

Set up a self-hosted integration runtime

If you haven't already, you need to download and install the latest self-hosted integration runtime. For more information, see the create and configure a self-hosted integration runtime guide.

Create and run scan

Now that you've set up authentication and a self-hosted integration

  1. Open the Microsoft Purview portal and select the Data Map.

  2. Select Data sources.

  3. Select the SQL Server source that you registered.

  4. Select New scan

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

    Set up scan

  6. You can scope your scan to specific tables by choosing the appropriate items in the list after enter Database name.

    Scope your scan

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

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

    trigger

  9. Review your scan and select Save and run.

View your scans and scan runs

To view existing scans:

  1. Go to the Microsoft Purview portal. On the left pane, select Data map.
  2. Select the data source. You can view a list of existing scans on that data source under Recent scans, or you can view all scans on the Scans tab.
  3. Select the scan that has results you want to view. The pane shows you all the previous scan runs, along with the status and metrics for each scan run.
  4. Select the run ID to check the scan run details.

Manage your scans

To edit, cancel, or delete a scan:

  1. Go to the Microsoft Purview portal. On the left pane, select Data Map.

  2. Select the data source. You can view a list of existing scans on that data source under Recent scans, or you can view all scans on the Scans tab.

  3. Select the scan that you want to manage. You can then:

    • Edit the scan by selecting Edit scan.
    • Cancel an in-progress scan by selecting Cancel scan run.
    • Delete your scan by selecting Delete scan.

Note

  • Deleting your scan does not delete catalog assets created from previous scans.

Next steps

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