Log inRequest a demoStart free

Guides

Test data management for SQL Server

What test data management for Microsoft SQL Server involves, what DataNivra's SQL Server connector does today, its tested limits, and the export path for everything outside them.

Guide

Microsoft SQL Server runs a large share of line-of-business systems: ERP, policy administration, core banking modules and countless in-house applications. Test environments for those systems are frequently refreshed by restoring a production backup, which is quick for the DBA and risky for everyone else. This guide covers what good SQL Server test data looks like and, plainly, where DataNivra stands today.

Current status: available

The SQL Server connector is available in agent 0.2.0 and later. It passed DataNivra's connector conformance kit against SQL Server 2022 (the Linux engine container), including discovery, read-only proof, bounded reads, masking, subsetting, certification and provisioning of a synthetic estate. The SQL Server integration page shows the tested versions and limitations from the same registry the product uses; this page is reviewed whenever that status changes.

What it does not cover yet:

  • It needs agent 0.2.0 or later; agent 0.1.0 does not contain its driver.
  • SQL authentication only. Microsoft Entra ID (service principal) and Windows integrated authentication are not supported yet.
  • Managed services are untested. Azure SQL Database, Azure SQL Managed Instance and Amazon RDS for SQL Server are compatibility profiles still to be run through the conformance kit.
  • Geography, geometry, hierarchyid, xml and sql_variant columns are carried as text.

Why restoring a backup into QA is the problem to solve

A restored backup contains every row, every column and every historical table. It usually lands on a server with weaker access controls, more users and longer retention than production. It also carries SQL Server specifics that make masking in place difficult: computed columns, triggers that fire on update, temporal tables that preserve the original values in history tables, and change data capture that can replicate unmasked rows elsewhere. Masking after restore means the unmasked copy existed, even briefly, in a less protected place.

What good SQL Server test data needs

  • A read-only extraction identity. Before reading, the connector proves it from the server's own catalog: fn_my_permissions must show no write or DDL permission on the server, the database or any visible table, and the login must not be sysadmin or a member of db_owner, db_datawriter or db_ddladmin. Connections set ApplicationIntent=ReadOnly, so an Always On listener can route them to a readable secondary.
  • Encryption you can verify. TLS is on by default and the server certificate is checked against your CA bundle; a session that is not encrypted is refused rather than silently downgraded.
  • Entity-based subsets. SQL Server schemas often have hundreds of foreign keys plus relationships enforced only by the application. A subset needs closure over both, so a sales order never arrives without its customer.
  • Consistent masking across databases. Many estates split one business entity across several databases on the same instance. Deterministic, keyed masking gives the same customer the same pseudonym in each of them.
  • Certification before provisioning. A dataset should be proven masked and intact before it reaches QA, not after someone notices.

Setting up the connector

Create a dedicated login and database user with SELECT on the schemas to read and nothing else, store the connection document (host, database, user and the password as a secret reference) in your secret store, and register the source in the console. The console's source wizard shows a copyable least-privilege script and an example document. The agent refuses the source if the login can write.

Outside the connector's limits: export to files inside your network

If your server is outside what the connector covers (for example Entra ID authentication only), the path that works today is to produce an extract inside your network and read it with a shipped connector:

  1. Export the tables you need to Parquet or CSV with tooling you already trust (for example an SSIS package, bcp or an existing data pipeline). Keep the extract on storage inside your boundary.
  2. Place the files in a read-only mounted directory and register it as a Local files source, or write them to your own bucket or container and use Amazon S3 or Azure Blob / Data Lake.
  3. Declare table names and CSV header handling explicitly; file names are never reported as metadata.
  4. Declare relationships that the files cannot carry (foreign keys are not part of Parquet) through policy or an industry pack, then subset, mask, certify and provision as usual.

This path is honest but not free: the export step is yours to schedule and secure, and the extract itself is sensitive until it has been masked. Treat the landing directory like production.

How DataNivra approaches it

Everything row-level runs in the agent and engine inside your network. DataNivra Cloud receives table and column names, counts and evidence references, never rows or credentials. The customer-resident processing tutorial explains the split.

Next steps

  • Check the integrations page for the full connector list and status.
  • Size a subset of your SQL Server estate with the free tools.
  • Read the documentation on agent installation and file-set sources.
  • Start free with the synthetic sandbox to see the full flow before you touch real data.