To secure the connection between the i2® Analyze application server and the database instance, you must change the configuration of both. Microsoft™ SQL Server stores its associated certificates and you must create or obtain certificates for the Microsoft SQL Server to use.
In i2 Analyze, SSL connections that involve SQL Server require i2 Analyze to trust the certificate that it receives from SQL Server. SQL Server stores certificates in the operating system's certificate stores. In a production deployment, you must use a certificate that is signed by a trusted certificate authority. To demonstrate a working configuration, you can create and use a self-signed certificate.
Ensure that you understand the details that are provided in the SQL Server documentation to configure SSL for your SQL Server. For more information, on Windows™ see Enable Encrypted
Connections to the Database Engine or Linux® see Server Initiated
Encryption.
Create a self-signed certificate for
SQL Server.
- For example, on Windows you can use the
New-SelfSignedCertificate
command
in PowerShell. For information, see New-SelfSignedCertificate.Run
the following command to create a
certificate:
New-SelfSignedCertificate -Subject "CN=hostname" -KeyAlgorithm RSA -KeyLength 2048 -CertStoreLocation "cert:\LocalMachine\My" -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.1") -Hash 'SHA256' -Type SSLServerAuthentication -Provider 'Microsoft RSA SChannel Crytographic Provider'
Important: Ensure that you set the
value of CN
to the hostname of
the server where SQL Server is located.
- For example, on Linux you can run the following commands by using OpenSSL:
openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=hostname' -keyout sql-server-key.key -out sql-server-certificate.pem -days 365
sudo chown mssql:mssql sql-server-certificate.pem sql-server-key.key
sudo chmod 600 sql-server-certificate.pem sql-server-key.key
sudo mv sql-server-certificate.pem /etc/ssl/certs/
sudo mv sql-server-key.key /etc/ssl/private/
Important: Ensure that you set the value of CN
to the hostname of the server where SQL Server is located.
Export the self-signed
certificate.
-
On Windows:
-
Use the Certificates snap-in in the Microsoft Management Console to export the certificate from the Local Computer user's certificates.
-
Locate the self-signed certificate in the
Personal certificate store.
-
Right-click the
certificate, and click .
Complete the Certificate Export
Wizard to export the certificate
without the private key as a DER encoded binary
X.509 file. Set the file name to
i2-sqlserver-certificate.cer.
- On Linux:
- Extract the DER certificate from the
PEM file by using OpenSSL:
openssl x509 -outform der -in sql-server-certificate.pem -out i2-sqlserver-certificate.cer
After you create the CER file, ensure that the
file permissions are the same as the original PEM
file.
Configure
SQL Server to encrypt connections.
- On Windows:
- In SQL Server Configuration Manager,
expand SQL Server Network Configuration, and
right-click Protocols for
<instance> and click
Properties.
- In the Properties window on the
Certificate tab, select
your certificate from the Certificate list and
click
Apply.
- On the Flags
tab, select Yes from the
Force Encryption
list.
- Click OK and
restart the SQL Server instance.
Note: The service account that is used to start
the SQL Server instance must be have read
permissions to your certificate. By default, the
service account is
NT
Service/MSSQLSERVER
on Windows. For more
information about service accounts, see
Service
Configuration and Access Control.
- On Linux:
- Run the following commands to
specify your certificate and key, and configure
SQL Server:
systemctl stop mssql-server
cat /var/opt/mssql/mssql.conf
sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/sql-server-certificate.pem
sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/sql-server-key.key
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1
- Restart the SQL Server
instance.