Configuring a database on Microsoft SQL Server

Make sure the database you are using meet the requirements described in Compatible databases and the following ones.

Database Requirement 1 - Case Insensitivity

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.

Sign in, then right-click the root of the tree (instance of SQL Server Express).
Go to Security.
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. Restart the service after changing.

Database Requirement 4 - Database owner

The database login that will connect Talend Data Catalog 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 such as database maintenance are implemented in SQL Server by stored procedures written in C# compiled in a stored procedure assembly called MIRRepo. This assembly has been signed to be created with permission set to SAFE.

Enable CLR, which is the default requirement to execute .NET assemblies via stored procedures.
```
EXEC sp_configure 'clr enabled',1
RECONFIGURE;
```

If you are using SQL server 2017 or newer, CLR strict security is enabled by default. You need to do one of the following procedures:

import the certificate used to sign the stored procedure assembly in the database and grant the UNSAFE assembly permission using the following command:

  CREATE CERTIFICATE MIRRepoCert FROM BINARY =
0x308203663082024ea00302010202045eece216300d06092a864886f70d01010b05003075310b30090603550406130255533113301106035504080c0a43616c69666f726e69613116301406035504070c0d4d6f756e7461696e2056696577312a3028060355040a0c214d65746120496e746567726174696f6e20546563686e6f6c6f67792c20496e632e310d300b06035504030c044d6d4462301e170d3230303630313037303030305a170d3330303630313037303030305a3075310b30090603550406130255533113301106035504080c0a43616c69666f726e69613116301406035504070c0d4d6f756e7461696e2056696577312a3028060355040a0c214d65746120496e746567726174696f6e20546563686e6f6c6f67792c20496e632e310d300b06035504030c044d6d446230820122300d06092a864886f70d01010105000382010f003082010a0282010100c2ccf729a28a90958f71a68f6acca9f20b5c256b7c76565b2ece0cd1789bec85e9ab538ac38dc268e48c10e17d3eca1aeb14034bc67bafc05475ed013495aada683c74885f12a8bdbf2025ec3c5a0172010e7055ab27a853e77611ee6ae846453702d18ae3080977ddaee50a282b9dab3f077fe1630804b24f05c58280621dc1426fff7115e8a791435687096c09f754608bb9a6ce00002f7131f09cffd417678bddb8f7a703e4e688f2f0af501c52ecef2cbea3d37c45da4239ddb53295adaddb11dc0118b3188adf812c983d5676c5b7356d68e2258ea32cd3216db21dae49df16d2aa1aef39c618e393ce7e1b131b241c557414424fb6c17c825022a5a4270203010001300d06092a864886f70d01010b05000382010100a1db34a6cda0729a796e5ed0fe5b2f4813ff74bf96300c9ca30fb84be44bd7d0bc46c96a0726eae5e829985429ff4ff09b50ece907c5b8c7f8a71f7a16781103d7eaf2e1c7afa39e4774293610e0d04e6b0c76dc9a85891e6f5fed09059960dc7e2a7c1dc14d64aab9718747752d394b22e339da2c7e6ced1626dde991818cbcaf049d8f112a98b2aa2e80d1168f797a6c992e304e4572b4edcf40d270a281f82d7bde64e8d8b5d83574ecf5470f3d1a9d710498e133e9309a043f63b1682972678fba2a33267999795b5d040524e2f875b667dcec08d310e27b6086b2667dde70d4401fe501944f70581e559d5f3f5b72e49ff722e58594b84a8d15d5dd1414;
  CREATE LOGIN MIRRepoCertLogin FROM CERTIFICATE MIRRepoCert;
  GRANT UNSAFE ASSEMBLY TO MIRRepoCertLogin;

disable the SQL Server CLR strict security as follows:

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

If you are using a version of SQL server prior to 2017, you need to enable CLR strict security as follows:
```
EXEC sp_configure 'clr strict security', 1  
RECONFIGURE;
```

For more information on the CLR strict security, see Microsoft SQL documentation.

Database preparation

Log in to SQL server as a user with server administrator role.
Configure the CLR strict security depending on the version of your SQL server.
Execute the following commands to create a database MM and a user MM, with the password MM123! for example.
```
 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;
```
The product relies on one assembly named MIRRepo which is loaded from binary and not from file. This binary is created with the SAFE permission. 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. Microsoft now recommends running the SQL Server Browser service which can be done either in the Services panel or the SQL Server Configuration Manager.

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

The connection string syntax is:

jdbc:sqlserver://<dbServer>:<dbPortNumber>;databasename=<dbName>

The default database instance name for SQL Server Express is sqlexpress and sqlserver for any other SQL Server edition.

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

You can connect to a named SQL server instance other than the default.

If the SQL Server browser service is running.

Case	Action
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;

You can connect to SQL Server using domain account.
1. Find the mssql JDBC driver under %OMM_HOME%\java\Jdbc\mssql, such as mssql-jdbc-7.4.1.jre11.jar.
2. Download a Microsoft JDBC driver for SQL Server with the same version and extract the content.
  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.
3. Copy the sqljdbc_auth.dll to %OMM_HOME%\bin.
4. Replace the mssql-jdbc-x.x.x.jre11.jar under %OMM_HOME%\java\Jdbc\mssql with the one from Microsoft JDBC driver x.x for SQL Server\sqljdbc_x.x\enu.
5. At the Configure Database Connection window, add the string ;integratedSecurity=true at the end of the Database URL, such as jdbc:sqlserver://localhost:1433;databasename=MM;integratedSecurity=true.
6. Specify other fields and click TEST CONNECTION.