---
title: "SqlServer@2"
description: "SqlServer@2 — MS SQL Server, Azure SQL, Synapse Analytics, MS Fabric and Dataverse over Microsoft.Data.SqlClient, no driver needed"
url: "https://docs.justcat.it/reference/data-sources/providers/sqlserver-2/"
---
# SqlServer@2


## 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


**Properties**



Name
: DWH

Provider
: SqlServer@2

Technology
: SqlServer

Connection string
: ```
  Server=localhost;
  Database=DWH;
  Integrated Security=SSPI;
  TrustServerCertificate=true;
  ```





**YAML**


```yaml
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 (<a href="https://learn.microsoft.com/en-us/sql/connect/ado-net/introduction-microsoft-data-sqlclient-namespace?view=sql-server-ver16" target="_blank">release notes</a>); unlike the legacy [SqlServer@1](https://docs.justcat.it/reference/data-sources/providers/sqlserver-1/ "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](https://docs.justcat.it/reference/data-sources/providers/introduction/#how-every-provider-is-used "How every provider is used") |

The full list is in the <a href="https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.connectionstring" target="_blank">Microsoft.Data.SqlClient documentation</a>.

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](https://docs.justcat.it/reference/data-sources/providers/introduction/#how-every-provider-is-used "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.


**Properties**



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;
  ```





**YAML**


```yaml
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`:


**Properties**



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;
  ```





**YAML**


```yaml
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](https://docs.justcat.it/reference/project-file/lists/ "Lists") of definitions: any statement that returns the columns of a [test](https://docs.justcat.it/reference/tests/properties/ "Test properties"), [data source](https://docs.justcat.it/reference/data-sources/properties/ "Data source properties") or [query](https://docs.justcat.it/reference/queries/properties/ "Query properties") definition works. Ready-made table scripts are in [Store definitions in a database](https://docs.justcat.it/how-to-guides/organize-and-run-tests/store-definitions-in-a-database/ "Store definitions in a database").

## Related

* [Technologies](https://docs.justcat.it/reference/data-sources/technologies/ "Technologies") — the SQL Server-compatible platforms this provider serves.
* [SQL Server output](https://docs.justcat.it/reference/outputs/sqlserver/ "SQL Server output") — the same database can also receive the test results.