Configuring a database on Microsoft SQL Server - 7.3

Talend Data Catalog Installation and Upgrade Guide for Windows

author
Talend Documentation Team
EnrichVersion
7.3
EnrichProdName
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
task
Installation and Upgrade
EnrichPlatform
Talend Data Catalog
Make sure the database you are using meet the requirements described in Compatible databases and the following ones.

Database Requirement 1 - 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.
  1. Sign in, then right-click the root of the tree (instance of SQL Server Express).
  2. Go to Security.
  3. Select SQL Server and Windows Authentication mode.

Database Requirement 2 - 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 3 - Database owner

The database login that will connect Talend Data Catalog to the SQL Server database must be the owner of the database.

Database preparation

  1. Log in to SQL server as a user with server Administrator role.
  2. Configure the CLR strict security depending on the version of your SQL server.

    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.

    • 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 enabled', 1  
      RECONFIGURE;

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

  3. 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. However, 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>

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

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.

  • 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;