Connect Validatar to Microsoft SQL Server with the SQL Server connection type and the built-in SQL Server data source template. Validatar Server connects to the database directly. Validatar Cloud reaches a database inside your network through a Data Agent that you install next to it.
Applies to: Validatar Cloud · Validatar Server
When you finish, the database's schemas, tables and columns are in the Catalog, column comments included, and your tests can query it.
The example on this page is the Wholesale Distribution System: the WideWorldImporters database on SQL Server, read by Validatar Cloud through a Linux Data Agent. It ingests 11 schemas and 49 tables, and the Warehouse Reconciliation project compares it with the Enterprise Data Warehouse on Snowflake.
Choose how Validatar reaches the database
| You run | The database is | Validatar connects |
|---|---|---|
| Validatar Server | Reachable from the Validatar server | Directly. Leave Data Agent Group empty. |
| Validatar Server | On a network the server cannot reach | Through a Data Agent installed on that network |
| Validatar Cloud | Inside your network, a private cloud network or on premises | Through a Data Agent installed where it can reach the database |
| Validatar Cloud | Open to the internet, such as an Azure SQL database with a public endpoint | Directly, once the database firewall allows Validatar Cloud (see Connecting to Data for the address) |
The Data Agent makes an outbound connection to Validatar and runs the queries inside your network, so the database never has to accept connections from outside. Either way, Validatar runs the queries your tests and the template define and reads the results. A read-only login is all it needs.
Before you start
- The Data Source Admin role in Validatar.
- A SQL Server login with read access to the database. The template's ingestion reads the system catalog views (
sys.schemas,sys.objects,sys.columns,sys.sql_modules,sys.extended_properties), and SQL Server shows a login only the objects it has rights to. - For Validatar Cloud with a private database: a Data Agent installed and registered, on Windows or Linux. It must reach the SQL Server host on its port (1433 by default). Check that its group shows under Settings > Configuration > Data Agents.
No driver install is needed. Validatar connects with Microsoft's SqlClient library, which ships with Validatar Server and the Data Agent.
A read-only login for the example database looks like this:
-- Read-only login for Validatar on the WideWorldImporters database.
-- Run as a member of sysadmin or securityadmin.
CREATE LOGIN validatar_reader
WITH PASSWORD = '<strong password>';
GO
USE WideWorldImporters;
GO
CREATE USER validatar_reader
FOR LOGIN validatar_reader;
-- Read every table and view, for tests and profiling.
ALTER ROLE db_datareader
ADD MEMBER validatar_reader;
-- Lets ingestion read view definitions into the Catalog.
GRANT VIEW DEFINITION
TO validatar_reader;
Create the SQL Server data source
- Go to Settings > Data Sources and click New Data Source.
- Enter the Name (
Wholesale Distribution System), choose the Environment and add a Description. - In the Connection panel, click Add.
- Leave Processing Engine on Built-In Engine: Save results in Local Storage.
- Choose SQL Server as the Connection Type. Validatar selects the SQL Server template.
- On Validatar Cloud with a private database, choose your agent group in Data Agent Group. On Validatar Server with direct access, leave it on No Assigned Group.
- Click Build, fill in Server, Database Name and User Name, and click Update Connection String.
- Type the login's password in Password.
- Click Test Connection. With a Data Agent selected, the agent runs the test from inside your network.
- When the Connection succeeded banner shows, click Create Connection.
- Click Save.
The Build form writes a SQL Server authentication string, with [[PASSWORD]] standing in for the password:
Server=<server>;Database=WideWorldImporters;User=validatar_reader;Password=[[PASSWORD]];
Validatar hands the connection string to SqlClient as written, so its usual forms work in Server: host,port for a non-default port, host\INSTANCE for a named instance. If you use a different sign-in method, add its keywords to the string by hand and prove it with Test Connection before you save.
Add it to a project and ingest
- On the data source, open Projects, check the projects that will test it, and click Save. The Wholesale Distribution System goes into Warehouse Reconciliation.
- Open Schema Metadata and click Refresh Now.
- Wait for the run in Ingestion History to finish.
The SQL Server template reads every user schema, table and view, the view definitions, column data types, nullability, identity and primary key columns. It takes column comments from extended properties, preferring MS_Description. Click Set Refresh Schedule to re-ingest on a schedule, so new tables appear without anyone clicking Refresh Now.
Check that it worked
- Metadata Statistics on Schema Metadata shows 11 schemas and 49 tables for the Wholesale Distribution System.
- In Catalog > Wholesale Distribution System, the Schemas tab lists
Application,Purchasing,SalesandWarehouseamong others. - Open
Sales.Ordersand its Columns tab. The columns are listed with their data types, and the comments stored in the database's extended properties came across with them.
Next, profile the source or build a test that compares it with the warehouse.
Do it with Validatar AI
Do it with Validatar AI (Validatar Cloud, beta). Open the AI panel and ask: "Create a SQL Server data source named Wholesale Distribution System for the WideWorldImporters database on
with the login validatar_reader, using the SQL Server template and our Data Agent group. Add it to the Warehouse Reconciliation project and leave the password blank." Review what it creates before you run it. You can undo the change from the chat history.
Type the password yourself: open the data source's Connection page, click Edit, enter it in Password, test and save. The AI cannot ingest metadata, so click Refresh Now afterwards.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| "A network-related or instance-specific error" or a timeout | The query runs somewhere that cannot reach the host and port | On Validatar Cloud, pick a Data Agent Group whose agent can reach the server. Check the host, the port and any firewall between them. |
| "Login failed for user" | Wrong login or password, or the Password field is empty | Re-enter the password. Check that the login uses SQL Server authentication and is enabled. |
| "Cannot open database ... requested by the login" | The login has no user in that database | Create the database user, as in the script above. |
| "The certificate chain was issued by an authority that is not trusted" | SqlClient encrypts connections by default and does not trust the server certificate | Give SQL Server a certificate the Validatar host trusts. For a test server only, add TrustServerCertificate=True to the string. |
| No Data Agent Group field | No agent is registered yet | Install the Data Agent and wait for it to show under Settings > Configuration > Data Agents. |
| Tables or schemas missing from the Catalog | The login cannot see them | Grant read access on those schemas, then click Refresh Now. |
| Views show no definition | The login lacks VIEW DEFINITION |
Grant it and ingest again. |
Related
- Create a Data Source
- Validatar Data Agent
- Ingesting Schema Metadata
- Create and Run a Profile Set
- SQL Server Data Source Template
Engineers who script their setup can create SQL Server data sources through the Validatar MCP server and the Validatar Agent Kits.