Set up Microsoft SQL Server in Certificate Manager

This article describes how to install and set up the Microsoft SQL (MSSQL) Server, used in Smart ID Certificate Manager (CM).

Prerequisites

When MS SQL Server is used for the CMDB database, tables and users can be created during installation of the CM server components.

• The hardware requirements for MSSQL Server specified by Microsoft, applies for the database machine

Step-by-step instruction

Install Microsoft SQL Server

Information from Microsoft can be found here: https://docs.microsoft.com/en-us/sql/?view=sql-server-2017

During installation of the MSSQL Server, set the following options:

• Select standard settings

• Enable mixed mode authentication

• Choose a Login Name, for example, sa (where sa in lower-case stands for system administrator)

• Enter a password

Using MSSQL Server Configuration Manager:

• Enable TCP/IP sockets for the MSSQL Server Network Configuration

Define transaction log size

1. Use the SQL Enterprise Manager to define the size of the transaction log.

2. Specify restricted or unrestricted file growth in the database properties. Your choice decides whether or not you will have to check the growth of the transaction log on a regular basis. With unrestricted growth there is a risk that all the free disk space is used up.

Edit settings on the CM server

1. When installing the CM server, include the CMDB component to create the CMDB database. See Install Certificate Manager server components on Windows and Install Certificate Manager server components on Linux.

2. Enter the host, port, database parameters and password for database system administrator.

3. Enter the password to be set for the new user in MSSQL: lcmreq. The user is created during installation of MSSQL Server.

4. Make sure that the MSSQL Server service user can write to the folder specified for CMDB.

5. After CMDB is created, the cm.conf configuration file is updated with the JDBC connection URL and the chosen password for lcmreq.

Secure the connection

1. Enable TLS support for the connection to the MSSQL Server database.

   1. It can be done directly after installing MSSQL Server or even after the installation of the CM is completed.

   2. A complete guide on how to enable TLS on the MSSQL Server can be found here: https://docs.microsoft.com/en-us/sql/database-engine/configurewindows/ enable-encrypted-connections-to-the-database-engine.

2. The parameters for the JDBC connection URL that is created in the cm.conf configuration file during the CM installation, is going to work with the MSSQL Server regardless if TLS connection is enabled on the MSSQL Server or not.

   1. If the MSSQL Server requires support for TLS encryption, the driver will initiate the TLS certificate exchange. However, the MSSQL Server's TLS certificate will not be validated, but the entire communication will be encrypted. Further information on customizing the connection URL in the cm.conf and thus the TLS (for example, validating the MSSQL Server's certificate etc.) can be found here: https://docs.microsoft.com/en-us/sql/connect/jdbc/understanding-ssl-support

Set up log in using Windows authentication

After the installation you can configure CM to use the service user to login to the database using Windows authentication. Follow these steps:

1. Create a new login on the MSSQL Server for the Windows user or Windows group that should run the CF service.

2. In cm.conf, remove or comment out the following parameter:
   Database.user
   There is no need to modify the connection URL.

3. Start the CF service with the user chosen in the first step.

Additional information