Tutorial: Register and scan an on-premises SQL Server

Microsoft Purview is designed to connect to data sources to help you manage sensitive data, simplify data discovery, and ensure right use. Microsoft Purview can connect to sources across your entire landscape, including multi-cloud and on-premises. For this scenario, you'll use a self-hosted integration runtime to connect to data on an on-premises SQL server. Then you'll use Microsoft Purview to scan and classify that data.

In this tutorial, you'll learn how to:

  • Sign in to the Microsoft Purview governance portal.
  • Create a collection in Microsoft Purview.
  • Create a self-hosted integration runtime.
  • Store credentials in an Azure Key Vault.
  • Register an on-premises SQL Server to Microsoft Purview.
  • Scan the SQL Server.
  • Browse your data catalog to view assets in your SQL Server.

Prerequisites

Sign in to the Microsoft Purview governance portal

To interact with Microsoft Purview, you'll connect to the Microsoft Purview governance portal through the Azure portal. You can find the studio by going to your Microsoft Purview account in the Azure portal, and selecting the Open Microsoft Purview governance portal tile on the overview page.

Screenshot of Microsoft Purview window in Azure portal, with the Microsoft Purview governance portal button highlighted.

Create a collection

Collections in Microsoft Purview are used to organize assets and sources into a custom hierarchy for organization and discoverability. They're also the tool used to manage access across Microsoft Purview. In this tutorial, we'll create one collection to house your SQL Server source and all its assets. This tutorial won't cover information about assigning permissions to other users, so for more information you can follow our Microsoft Purview permissions guide.

Check permissions

To create and manage collections in Microsoft Purview, you'll need to be a Collection Admin within Microsoft Purview. We can check these permissions in the Microsoft Purview governance portal.

  1. Select Data Map > Collections from the left pane to open the collection management page.

    Screenshot of the Microsoft Purview governance portal window, opened to the Data Map, with the Collections tab selected.

  2. Select your root collection. The root collection is the top collection in your collection list and will have the same name as your Microsoft Purview account. In our example below, it is called Microsoft Purview Account.

    Screenshot of the Microsoft Purview governance portal window, opened to the Data Map, with the root collection highlighted.

  3. Select Role assignments in the collection window.

    Screenshot of the Microsoft Purview governance portal window, opened to the Data Map, with the role assignments tab highlighted.

  4. To create a collection, you'll need to be in the collection admin list under role assignments. If you created the Microsoft Purview account, you should be listed as a collection admin under the root collection already. If not, you'll need to contact the collection admin to grant you permission.

    Screenshot of the Microsoft Purview governance portal window, opened to the Data Map, with the collection admin section highlighted.

Create the collection

  1. Select + Add a collection. Again, only collection admins can manage collections.

    Screenshot of the Microsoft Purview governance portal window, showing the new collection window, with the 'add a collection' buttons highlighted.

  2. In the right panel, enter the collection name and description. If needed you can also add users or groups as collection admins to the new collection.

  3. Select Create.

    Screenshot of the Microsoft Purview governance portal window, showing the new collection window, with a display name and collection admins selected, and the create button highlighted.

  4. The new collection's information will reflect on the page.

    Screenshot of the Microsoft Purview governance portal window, showing the newly created collection window.

Create a self-hosted integration runtime

The Self-Hosted Integration Runtime (SHIR) is the compute infrastructure used by Microsoft Purview to connect to on-premises data sources. The SHIR is downloaded and installed on a machine within the same network as the on-premises data source.

This tutorial assumes the machine where you'll install your self-hosted integration runtime can make network connections to the internet. This connection allows the SHIR to communicate between your source and Microsoft Purview. If your machine has a restricted firewall, or if you would like to secure your firewall, look into the network requirements for the self-hosted integration runtime.

  1. On the home page of the Microsoft Purview governance portal, select Data Map from the left navigation pane.

  2. Under Source management on the left pane, select Integration runtimes, and then select + New.

    Select the Integration Runtimes button.

  3. On the Integration runtime setup page, select Self-Hosted to create a Self-Hosted IR, and then select Continue.

    Create new SHIR.

  4. Enter a name for your IR, and select Create.

  5. On the Integration Runtime settings page, follow the steps under the Manual setup section. You'll have to download the integration runtime from the download site onto a VM or machine that is in the same network as your on-premises SQL Server. For information about the kind of machine needed, you can follow our guide to manage integration runtimes.

    get key

    • Copy and paste the authentication key.

    • Download the self-hosted integration runtime from Microsoft Integration Runtime on a local Windows machine. Run the installer. Self-hosted integration runtime versions such as 5.4.7803.1 and 5.6.7795.1 are supported.

    • On the Register Integration Runtime (Self-hosted) page, paste one of the two keys you saved earlier, and select Register.

      input key.

    • On the New Integration Runtime (Self-hosted) Node page, select Finish.

  6. After the Self-hosted integration runtime is registered successfully, you'll see this window:

    successfully registered.

Set up authentication

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

  • SQL Authentication
  • Windows Authentication

This tutorial includes steps to use SQL authentication. For more information about scanning on-premises SQL Server with Windows authentication, see Set up SQL server authentication.

SQL authentication

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

Create 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, select and hold (or right-click) 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.

  4. Select OK to save.

  5. Navigate again to the user you created, by selecting and holding (or right-clicking) on the user and selecting Properties. Enter a new password and confirm it. Select the 'Specify old password' and enter the old password. It's required to change your password as soon as you create a new login.

    change password.

Create a Key Vault credential

  1. Navigate to your key vault in the Azure portal. Select Settings > Secrets.

    Select Secrets from Left Menu

  2. Select + Generate/Import

    Select Generate/Import from the top menu.

  3. For upload options, select Manual and enter the Name and Value as the password from your SQL server login. Ensure Enabled is set to Yes. If you set an activation and expiration date, ensure that today's date is between the two, or you won't be able to use the credential.

    Add values to key vault credential.

  4. Select Create to complete.

  5. In the Microsoft Purview governance portal, navigate to the Management page in the left menu.

    Select Management page on left menu.

  6. Select the Credentials page.

    The credentials button on the Management page is highlighted.

  7. From the Credentials page, select Manage Key Vault connections.

    Manage Azure Key Vault connections.

  8. Select + New from the Manage Key Vault connections page.

  9. Provide the required information, then select Create.

  10. Confirm that your Key Vault has been successfully associated with your Microsoft Purview account as shown in this example:

    View Azure Key Vault connections to confirm.

  11. Create a new Credential for SQL Server by selecting + New.

    Select +New to create credential.

  12. Provide the required information. Select the Authentication method and a Key Vault connection from which to select a secret from.

  13. Once all the details have been filled in, select Create.

    New credential

  14. Verify that your new credential shows up in the list view and is ready to use.

    View credential

Register SQL Server

  1. Navigate to your Microsoft Purview account in the Azure portal, and select the Microsoft Purview governance portal.

  2. Under Sources and scanning in the left navigation, select Integration runtimes. Make sure a self-hosted integration runtime is set up. If it's 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.

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.

  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:

  1. Go to the Microsoft Purview governance portal. Select the Data map tab on the left pane.
  2. Select the desired 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 page shows you all of the previous scan runs, along with the status and metrics for each scan run.
  4. Click the run ID to check more about the scan run details.

Manage your scans - edit, delete, or cancel

To manage or delete a scan:

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

  2. Select the desired 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.
  • 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 on the Schema tab of Microsoft Purview.

Clean up resources

If you're not going to continue to use this Microsoft Purview or SQL source moving forward, you can follow the steps below to delete the integration runtime, SQL credential, and purview resources.

Remove SHIR from Microsoft Purview

  1. On the home page of the Microsoft Purview governance portal, select Data Map from the left navigation pane.

  2. Under Source management on the left pane, select Integration runtimes.

    Select the Integration Runtimes button.

  3. Select the checkbox next to your integration runtime, then select the delete button.

    Check box next to integration runtime and delete button highlighted.

  4. Select Confirm on the next window to confirm the delete.

  5. The window will self-refresh and you should no longer see your SHIR listed under Integration runtimes.

Uninstall self-hosted integration runtime

  1. Sign in to the machine where your self-hosted integration runtime is installed.

  2. Open the control panel, and under Uninstall a Program search for "Microsoft Integration Runtime"

  3. Uninstall the existing integration runtime.

Important

In the following process, select Yes. Do not keep data during the uninstallation process.

Screenshot of the 'Yes' button for deleting all user data from the integration runtime.

Remove SQL credentials

  1. Go to the Azure portal and navigate to the Key Vault resource where you stored your Microsoft Purview credentials.

  2. Under Settings in the left menu, select Secrets

    Select Secrets from Left Menu in Azure Key Vault.

  3. Select the SQL Server credential secret you created for this tutorial.

  4. Select Delete

    Delete Secret from top Menu in Azure Key Vault Secret.

  5. Select Yes to permanently delete the resource.

Delete Microsoft Purview account

If you would like to delete your Microsoft Purview account after completing this tutorial, follow these steps.

  1. Go to the Azure portal and navigate to your purview account.

  2. At the top of the page, select the Delete button.

    Delete button on the Microsoft Purview account page in the Azure portal is selected.

  3. When the process is complete, you'll receive a notification in the Azure portal.

Next steps