Database Requirements

Supported versions are Microsoft SQL Server 2008 R2 to 2022 - 64 bit

Warning: With respect to cloud versions of SQL Server, the Microsoft Azure SQL Database and SQL Server on Virtual Machines may work if configured properly per further requirements in SQL Server setup below. However, RDS for SQL Server cannot not be used because you cannot grant UNSAFE ASSEMBLY (according to the documentation) to deploy the stored procedure assembly per further requirements in SQL Server setup below.

Make sure you apply the current Microsoft patches.

Detailed requirements are defined as part of the SQL Server setup below.

Database Requirement 1 - Case Insensitive

The database must be configured to interpret SQL in a case insensitive manner.

The case insensitive collation must be Latin1_General_CI_AS .

Database Requirement 2 - Mixed-Authentication Mode

The Mixed-Authentication Mode is usually set during the SQL Server installation process.

The Mixed-Authentication Mode can be verified or changed by using the SQL Server Management Studio: first sign in, then right click on the root of the tree (instance of SQL Server Express), go to Security, and finally select "SQL Server and Windows Authentication mode"

Database Requirement 3 - TCP/IP Protocol Enabled

The TCP/IP Protocol must be enabled in the SQL Server Configuration Manager for both the named instance and the client protocols (Make sure you restart the service after changing).

Database Requirement 4 - Login must be Database Owner

The database login (e.g., MM) that will connect TDC to the SQL Server database must be the owner of the database.

Database Requirement 5 - SQL Common Language Runtime (CLR) Strict Security for SQL Server 2017 or newer

All database intensive operations (not just database maintenance, but also trace lineage, delete models, etc.) are implemented in SQL Server by stored procedures written in C# compiled/delivered in a stored procedure assembly called MIRRepo which has been signed that will be created with permission set SAFE.

If you are using SQL server 2017 or newer, then CLR Strict Security is enabled by default. Therefore, the certificate used to sign the stored procedure assembly must be imported in the database and granted the UNSAFE assembly permission ( See Microsoft SQL Docs on CLR strict security ) using the following commands:

CREATE CERTIFICATE MIRRepoCert FROM BINARY = 0x308203663082024ea00302010202045eece216300d06092a864886f70d01010b05003075310b30090603550406130255533113301106035504080c0a43616c69666f726e69613116301406035504070c0d4d6f756e7461696e2056696577312a3028060355040a0c214d65746120496e746567726174696f6e20546563686e6f6c6f67792c20496e632e310d300b06035504030c044d6d4462301e170d3230303630313037303030305a170d3330303630313037303030305a3075310b30090603550406130255533113301106035504080c0a43616c69666f726e69613116301406035504070c0d4d6f756e7461696e2056696577312a3028060355040a0c214d65746120496e746567726174696f6e20546563686e6f6c6f67792c20496e632e310d300b06035504030c044d6d446230820122300d06092a864886f70d01010105000382010f003082010a0282010100c2ccf729a28a90958f71a68f6acca9f20b5c256b7c76565b2ece0cd1789bec85e9ab538ac38dc268e48c10e17d3eca1aeb14034bc67bafc05475ed013495aada683c74885f12a8bdbf2025ec3c5a0172010e7055ab27a853e77611ee6ae846453702d18ae3080977ddaee50a282b9dab3f077fe1630804b24f05c58280621dc1426fff7115e8a791435687096c09f754608bb9a6ce00002f7131f09cffd417678bddb8f7a703e4e688f2f0af501c52ecef2cbea3d37c45da4239ddb53295adaddb11dc0118b3188adf812c983d5676c5b7356d68e2258ea32cd3216db21dae49df16d2aa1aef39c618e393ce7e1b131b241c557414424fb6c17c825022a5a4270203010001300d06092a864886f70d01010b05000382010100a1db34a6cda0729a796e5ed0fe5b2f4813ff74bf96300c9ca30fb84be44bd7d0bc46c96a0726eae5e829985429ff4ff09b50ece907c5b8c7f8a71f7a16781103d7eaf2e1c7afa39e4774293610e0d04e6b0c76dc9a85891e6f5fed09059960dc7e2a7c1dc14d64aab9718747752d394b22e339da2c7e6ced1626dde991818cbcaf049d8f112a98b2aa2e80d1168f797a6c992e304e4572b4edcf40d270a281f82d7bde64e8d8b5d83574ecf5470f3d1a9d710498e133e9309a043f63b1682972678fba2a33267999795b5d040524e2f875b667dcec08d310e27b6086b2667dde70d4401fe501944f70581e559d5f3f5b72e49ff722e58594b84a8d15d5dd1414;

CREATE LOGIN MIRRepoCertLogin FROM CERTIFICATE MIRRepoCert;

GRANT UNSAFE ASSEMBLY TO MIRRepoCertLogin;

Alternatively, you can disable the SQL Server CLR Strict Security as follows:

EXEC sp_configure 'show advanced options', 1;
‎ RECONFIGURE;
‎ EXEC sp_configure 'clr strict security', 0;
‎ RECONFIGURE;
‎ EXEC sp_configure 'show advanced options', 0;
‎ RECONFIGURE;