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).

The database login (e.g., MM) that will connect MetaKarta 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;

The login is used to create the assembly on initial setup and every time the assembly needs to be updated through an application upgrade.

One can certainly disable the GRANT UNSAFE ASSEMBLY, but then you would have to remember to reenable it every time they want to update the application, which is not very practical.

However, you can make sure the MIRRepoCertLogin account is not mapped to any user and that it is disabled for login (it still needs permission to access the database engine), then the only thing it would hold is the key to trust the MM assembly.

Database Preparation

Login to SQL server as a user with server admin role and execute the following commands to create a database "MM" and a user "MM" with password "MM123!" (or another one):

Enable clr, and create the database and user:

EXEC sp_configure 'clr enabled', 1; RECONFIGURE; Go CREATE LOGIN MM WITH PASSWORD = 'MM123!'; CREATE DATABASE MM; ALTER DATABASE MM SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE MM SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE MM SET MULTI_USER WITH ROLLBACK IMMEDIATE; ALTER AUTHORIZATION ON DATABASE::MM to MM;

Warning: The product relies on one assembly (named MIRRepo) which is loaded from binary and not from file. This binary is created with the SAFE permissions. So in addition to being the database owner, the MM user should be granted the CREATE ASSEMBLY permission.

Database Connection

Advanced SQL Server Administrators may define ("hard-code") a set of TCP/IP ports for SQL Server to run over the network. However, Microsoft now recommends to run the "SQL Server Browser" service which can be done either in the Services panel or the SQL Server Configuration Manager.

For more information, read: How to: Configure Express to accept remote connections

The connection string syntax is: ** **jdbc:sqlserver://<dbServer>:<dbPortNumber>;databasename=<dbName>

To connect to a named SQL server instance other than the default:

If the SQL Server browser service is running:
If the named instance is configured to listen on dynamic ports: In the installer, specify only the instance name (in the format HOSTNAME\INSTANCENAME) and no port (the port field should be left empty), such as: ** **jdbc:sqlserver://localhost\sqlexpress;databaseName=MM;
If the named instance is configured to listen on static IP ports: The SQL Server instance must be configured to run on a static TCP/IP port and that port must be specified in the installer, such as: ** **jdbc:sqlserver://localhost\sqlexpress:1433;databaseName=MM;
If the SQL Server browser service is not running: In the installer, specify only the instance port, such as: ** **jdbc:sqlserver://localhost:1433;databaseName=MM;

To connect to SQL Server using domain account:

Find the mssql JDBC driver under $MM_HOME/java/jdbc/mssql, e.g. mssql-jdbc-7.4.1.jre11.jar.
Download a Microsoft JDBC driver for SQL Server with the same version, e.g. 7.4, and extract the content. There you will find a sqljdbc_auth.dll from Microsoft JDBC driver x.x for SQL Server\sqljdbc_x.x\enu\auth\x64, and a mssql-jdbc-x.x.x.jre11.jar from Microsoft JDBC driver x.x for SQL Server\sqljdbc_x.x\enu
Copy the sqljdbc_auth.dll to $MM_HOME/bin and replace the mssql-jdbc-x.x.x.jre11.jar under $MM_HOME/java/jdbc/mssql with the one from Microsoft JDBC driver x.x for SQL Server\sqljdbc_x.x\enu
At the Configure Database Connection window append the string ;integratedSecurity=true at the end of the Database url, such as jdbc:sqlserver://localhost:1433;databasename=MM;integratedSecurity=true. Specify other fields and click TEST CONNECTION.

Note 1: The default database instance name for SQL Server Express is "sqlexpress", and "sqlserver" for any other SQL Server edition.

Note 2: The default SQL Server TCP/IP port number is 1433.