Scenario: Authenticating Microsoft SQL Server
Nov 27, 2023
Information in this section is only applicable to configurations in which Kaspersky Security Center uses Microsoft SQL Server as a database management system.
To protect Kaspersky Security Center data transferred to or from the database and data stored in the database from unauthorized access, you must secure communication between Kaspersky Security Center and SQL Server. The most reliable way to provide secure communication is to install Kaspersky Security Center and SQL Server on the same device and use the shared memory mechanism for both applications. In all other cases, we recommend that you use a SSL or TLS certificate to authenticate the SQL Server instance. You can use a certificate from a trusted certification authority (CA) or a self-signed certificate. We recommend that you use a certificate from a trusted CA because a self-signed certificate provides only limited protection.
SQL Server authentication proceeds in stages:
- Generating a self-signed SSL or TLS certificate for SQL Server according to the certificate requirements
If you already have a certificate for SQL Server, skip this step.
An SSL certificate is only applicable to SQL Server versions earlier than 2016 (13.x). In SQL Server 2016 (13.x) and later versions, use a TLS certificate.
For example, to generate a TLS certificate, enter the following command in PowerShell:
New-SelfSignedCertificate -DnsName SQL_HOST_NAME -CertStoreLocation cert:\LocalMachine -KeySpec KeyExchange
In the command, instead of SQL_HOST_NAME you must type the SQL Server host name if the host is included in the domain or type the fully qualified domain name (FQDN) of the host if the host is not included in the domain. The same name—host name or FQDN—must be specified as an SQL Server instance name in the Administration Server setup wizard.
- Adding the certificate on the SQL Server instance
The instructions for this stage depend on the platform on which SQL Server is running. Refer to the official documentation for details:
To use the certificate on a failover cluster, you must install the certificate on each node of the failover cluster. For details, refer to the Microsoft documentation.
- Assigning the service account permissions
Ensure that the service account under which the SQL Server service is run has the Full control permission to access private keys. For details, refer to the Microsoft documentation.
- Adding the certificate to the list of trusted certificates for Kaspersky Security Center
On the Administration Server device, add the certificate to the list of trusted certificates. For details, refer to the Microsoft documentation.
- Enabling encrypted connections between the SQL Server instance and Kaspersky Security Center
On the Administration Server device, set value
1to the environment variable
KLDBADO_UseEncryption. For example, in Windows Server 2012 R2, you can change environment variables by clicking Environment Variables on the Advanced tab of the System Properties window. Add a new variable, name it
KLDBADO_UseEncryption, and then set value
- Additional configuration to use TLS 1.2 protocol
If you use the TLS 1.2 protocol, then additionally do the following:
- Ensure that the installed version of SQL Server is a 64-bit application.
- Install Microsoft OLE DB Driver on the Administration Server device. For details, refer to the Microsoft documentation.
- On the Administration Server device, set value
1to the environment variable
KLDBADO_UseMSOLEDBSQL. For example, in Windows Server 2012 R2, you can change environment variables by clicking Environment Variables on the Advanced tab of the System Properties window. Add a new variable, name it
KLDBADO_UseMSOLEDBSQL, and then set value
If the OLE DB Driver version is 19 or newer, also set value
MSOLEDBSQL19to the environment variable
- Enabling usage of TCP/IP protocol on a named instance of SQL Server
If you use a named instance of SQL Server, then additionally enable usage of TCP/IP protocol and assign a TCP/IP port number to the SQL Server Database Engine. When you configure SQL Server connection in the Administration Server setup wizard, specify the SQL Server host name and the port number in the SQL Server instance name field.