Get Help

SqlServer@2

SqlServer@2 reads data from MS SQL Server and from every Microsoft database that speaks its protocol — Azure SQL, Synapse Analytics, MS Fabric, Dataverse. Nothing to install.

Connects to

MS SQL Server, on premises or in the cloud, and the Microsoft services built on the same protocol: Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, MS Fabric Warehouse, MS Fabric Lakehouse and Dataverse — anything a SQL Server client library can open.

Example

Name
DWH
Technology
MS SQL Server
Provider
SqlServer@2
Connection string
Server=localhost;
Database=DWH;
Integrated Security=SSPI;
TrustServerCertificate=true;

Data sources:
- Name: DWH
  Provider: SqlServer@2
  Technology: SqlServer
  Connection string: >
    Server=localhost;
    Database=DWH;
    Integrated Security=SSPI;
    TrustServerCertificate=true;

Change localhost to the name or IP address of your server and DWH to the name of your database.

Connection string

A standard Microsoft.Data.SqlClient connection string — Key=Value pairs separated by semicolons. The driver is the one Microsoft actively develops (release notes); unlike the legacy SqlServer@1 it supports Microsoft Entra ID authentication — multi-factor authentication, service principals and the other security features of the platform. If you need those, SqlServer@2 is the provider to choose. The keys you will use most:

Key Meaning
Server (or Data Source) Server name or address; add ,port or \instance when needed
Database (or Initial Catalog) Database to connect to
Integrated Security SSPI or true for Windows authentication; leave out when you send a user name and password
User Id, Password SQL Server credentials, or the application id and secret of a service principal
Authentication Entra ID mode: Active Directory Interactive, Active Directory Service Principal, Active Directory Password, …
Encrypt, TrustServerCertificate The driver encrypts the connection by default; TrustServerCertificate=true accepts a certificate the client does not trust — typical for a local or self-signed instance
Command Timeout Default statement timeout in seconds; a test’s own Timeout overrides it — see How every provider is used

The full list is in the Microsoft.Data.SqlClient documentation.

A password, or the whole string, can come from an environment variable — %NAME% is replaced with the value of NAME; see How every provider is used.

Azure SQL Database with multi-factor authentication

When your organization has multi-factor authentication enabled, Authentication=Active Directory Interactive prompts the user for the credentials. This is useful only for interactive testing; an unattended run has nobody to answer the prompt.

Name
DWH
Technology
Azure SQL Database
Provider
SqlServer@2
Connection string
Server=your-sql-server-name.database.windows.net;
Authentication=Active Directory Interactive;
Database=your-database-name;

Data sources:
- Name: DWH
  Provider: SqlServer@2
  Technology: AzureSqlDatabase
  Connection string: >
    Server=your-sql-server-name.database.windows.net;
    Authentication=Active Directory Interactive;
    Database=your-database-name;

Azure SQL Database with a service principal

MFA effectively blocks you from using your own account for autonomous test automation. Create a service principal with a secret in your Microsoft Entra ID (App registrations), give it access to the database and instruct CAT to use it — the application id goes to User Id, the secret to Password:

Name
DWH
Technology
Azure SQL Database
Provider
SqlServer@2
Connection string
Server=your-sql-server-name.database.windows.net;
Authentication=Active Directory Service Principal;
Database=your-database-name;
User Id=your-application-id;
Password=your-secret;

Data sources:
- Name: DWH
  Provider: SqlServer@2
  Technology: AzureSqlDatabase
  Connection string: >
    Server=your-sql-server-name.database.windows.net;
    Authentication=Active Directory Service Principal;
    Database=your-database-name;
    User Id=your-application-id;
    Password=your-secret;

Never store the application id and secret directly in the project file — put them, or the whole string, in environment variables as described above.

Settings

None beyond Connection string.

Query

One T-SQL statement that returns a result set — a SELECT, or an EXEC of a stored procedure that returns one.

Prerequisites

None. The driver ships with CAT; Windows and Linux alike.

Serving test and data source definitions

SqlServer@2 can also serve lists of definitions: any statement that returns the columns of a test, data source or query definition works. Ready-made table scripts are in Store definitions in a database.

  • Technologies — the SQL Server-compatible platforms this provider serves.
  • SQL Server output — the same database can also receive the test results.
← Snowflake@1
Teradata@1 →