Built-in MSSQL driver support plugin

This page provides guidance on how the MSSQL driver support plugin interacts with various database objects, such as Primary Keys (PK), Foreign Keys (FK), Unique Constraints, and Unique Indexes during masking jobs.

Handling Primary Keys (PK)

In SQL Server, Primary Keys are managed as Constraints but are reported as Indexes in both the SQL Server Management Studio (SSMS) and the Continuous Compliance Engine UI.

To drop Primary Keys during a masking job, use the Disable Constraints option on the compliance engine UI.

Selecting Drop Index will not affect Primary Keys, even though the UI may imply otherwise.

Example

To drop a Primary Key, the following SQL command is executed:

SQL

DROP CONSTRAINT [PK_tbl_maskPK]

Create table example

SQL

CREATE TABLE [dbo].[tbl_maskPK](
      NOT NULL,
    CONSTRAINT [PK_tbl_maskPK] PRIMARY KEY CLUSTERED ([mask_pk] ASC)
);

Handling Foreign Keys (FK)

Foreign Keys must be managed carefully during masking operations, as any table with a Foreign Key linking to a Primary Key must be included in the Rule Set. It is recommended to select both Drop Indexes and Disable Constraints in the compliance engine UI for these.

Handling Unique Constraints

For Unique Constraints, select Disable Constraints in the compliance engine. Similar to Primary Keys, Unique Constraints are managed as Constraints, even though they may appear as Indexes.

Attempting to drop them via Drop Index will result in an error because SQL Server does not allow dropping Unique Constraints via the DROP INDEX command.

Example

To drop a Unique Constraint, the following SQL command is executed:

SQL

DROP CONSTRAINT [UCmask]

Create table example

SQL

CREATE TABLE [dbo].[tbl_maskUC](
      NOT NULL,
    CONSTRAINT UCmask UNIQUE(mask)
);

Handling Unique Indexes

For Unique Indexes, select Drop Index in the compliance engine.

The Disable Constraints option does not affect Unique Indexes.

Example

To drop a Unique Index, the following SQL command is executed:

SQL

DROP INDEX [UIxmask] ON [dbo].[tbl_maskUIx]

Create table example

SQL

CREATE TABLE [dbo].[tbl_maskUIx](
      NOT NULL
);
CREATE UNIQUE INDEX UIxmask ON [dbo].[tbl_maskUIx](mask);

Handling Check Constraints (untested)

For Check Constraints, SQL Server allows the use of the following SQL command to disable (not drop) the constraint:

SQL

ALTER TABLE [table]
NOCHECK CONSTRAINT [constraint];

It is important to verify whether the compliance engine Disable Constraints option correctly handles Check Constraints.

Create table example

SQL

CREATE TABLE [dbo].[tbl_maskCC](
    [mask] [int] CHECK (mask >= 10)
);