Microsoft SQL Server

1. Create a service account in SQL Server with the appropriate permissions for Validio. See Prerequisites.
2. Create a Validio credential to connect to the service account. See Add a SQL Server Credential.
3. Create a Validio source to monitor the tables in the SQL Server database. See Add a SQL Server Source.

​

Prerequisites

​

Setup for user and password-based authentication

-- Add a new validio user/password account to the server
USE master;
CREATE LOGIN validio WITH PASSWORD = '<password>';

-- Add a validio user to the database, repeat for each database
USE <database>;
CREATE USER validio FOR LOGIN validio;

​

Setup for Microsoft Entra ID-based authentication

-- Add a validio Microsoft Entra ID application to the database, repeat for each database
USE <database>;
CREATE USER validio FROM EXTERNAL PROVIDER;

​

Permissions

-- Repeat these steps for each database
USE <database>;

-- Grant access to read all tables and views
ALTER ROLE db_datareader ADD MEMBER validio;

-- Grant access to view definitions (used for lineage and Row Count (metadata) validators)
GRANT VIEW DEFINITION TO validio;

-- Grant access to query logs and table statistics (used for lineage and Row Count (metadata) validators)
-- For Azure SQL Database
GRANT VIEW DATABASE PERFORMANCE STATE TO validio;

-- For Azure SQL Managed Instance or SQL Server 2022 (16.x) and later
USE master;
GRANT VIEW SERVER PERFORMANCE STATE TO validio;

-- For SQL Server 2019 (15.x) and earlier
USE master;
GRANT VIEW SERVER STATE TO validio;

​

Add a SQL Server credential

1. Navigate to Credentials and click + New credential.
2. Under Namespace, select a namespace where the resources will be created.
3. For Credential Type, select Microsoft SQL Server Credential.
4. Fill in the credential parameter fields. Refer to the SQL Server Credential Parameters table.
5. Check Use for catalog and schema checks to automatically discover credentials and add them to the catalog page.
6. Click Create.

​

SQL Server credential parameters

​

User and password authentication

​

Entra ID authentication

​

Add a SQL Server source

1. Navigate to Sources and click + New source.
2. Under Source type, select SQL Server.
3. Under Config,
   1. Select the valid Credential or create a new credential to authenticate your connection to the data warehouse.
   2. You have two options for specifying the dataset and tables to monitor:
      1. Select an existing table — Enter the Database, Schema, and Table to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the SQL Server Source Configuration Parameters table.
      2. Use Custom SQL — Write a valid SQL query to specify the tables to monitor.
   3. Set the Polling schedule, which is how frequently the validators on the source will check for changes.
4. Under Schema, click Continue to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
5. Under Source details,
   1. Add Tags to help group related sources or to use for routing notifications.
   2. Add an Owner who will be the contact for incident notifications.
   3. Assign a Priority, which indicates the importance of incidents detected on this source.
6. (Optional) Under Validators, select Row Count (metadata) to add a metadata validator to each source. This step is shown only when you select existing tables, not views or custom SQL. Freshness (metadata) is not available for SQL Server.
7. Click Continue to create the source. Source names are generated automatically and will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

​

SQL Server source parameters