SqlServer@2

SqlServer@2s provider lets you read data from SqlServer databases, no drivers needed.

SqlServer@2 provider works out-of-the-box, you don’t need any drivers installed. Major difference from SqlServer@1 are:

  • supports many security features that the older driver does not support.

  • the underlying driver is actively developed by Microsoft (release notes)

  • SqlServer@2 provider is in preview in CAT.

If you need to use MFA, service principals etc., you have to choose SqlServer@2.

Examples

Local SQL Server Instance 

Data sources:
- Name: DWH
  Provider: SqlServer@1
  Connection string: data source=localhost;integrated security=SSPI;initial catalog=DWH

Connect to Azure SQL Database Using MFA

When your organization has multi-factor authentication enabled, you also need SqlServer@2 provider, not SqlServer@1 (in the older the feature is not supported by the underlying driver). This is useful only for interactive testing, because the user is prompted for the credentials.

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

Connect to Azure SQL Database Using Service Principal

MFA effectively blocks you from using your account for autonomous test automation. You need to create a service principal with a secret, give him access to your database and instruct CAT to use the principal The connection string looks like this:

Data sources:
- Name: DWH
  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;

You need to create the service principal in your Microsoft Entra ID (App registrations) and create a secret for it. Application ID and secret is then used in the connection string. NEVER store those values directly in the project file. Use environment variables to hide the entire connection string - see this tutorial.

Storing Test definitions and Data Source definitions in DB

Reading and testing data is not the only purpose of providers. You can also use them for retrieving (potentially also automatically generating) test defintions and / or data sources.

Any SQL statement that returns columns expected by Test definition or Data source defintition will work. But if you want to persist those, you might find useful these scripts that prepares the necessary tables:

CREATE TABLE [Test].[CAT_TestDefinition](
  [TestSuite] [NVARCHAR](300) NULL,
  [Order] [INT] NULL,
  [TestCase] [NVARCHAR](300) NULL,
  [TestName] [NVARCHAR](300) NOT NULL,
  [Description] [NVARCHAR](MAX) NULL,
  [FirstDataSource] [NVARCHAR](200) NULL,
  [FirstQuery] [NVARCHAR](MAX) NULL,
  [SecondDataSource] [NVARCHAR](200) NULL,
  [SecondQuery] [NVARCHAR](MAX) NULL,
  [Expectation] [VARCHAR](50) NOT NULL,
  [Categories] [NVARCHAR](100) NULL,
  [Tolerance] [DECIMAL](19, 4) NULL,
  [Timeout] [INT] NULL,
  [Key] [NVARCHAR](100) NULL,
  [MaximumErrorsLogged] [INT] NULL
);

CREATE TABLE [Test].[CAT_ConnectionDefinition](
  [Name] [NVARCHAR](200) NOT NULL,
  [Provider] [NVARCHAR](200) NOT NULL,
  [ConnectionString] [NVARCHAR](2000) NOT NULL
)

Just fill them up with data and let CAT know about them (see Project files).

← SqlServer@1
Yaml@1 →