Enforce an encrypted connection to a Microsoft SQL Server database
search cancel

Enforce an encrypted connection to a Microsoft SQL Server database

book

Article ID: 338843

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

In your vSphere environment, you can set up vCenter Server to store server data in an external database. vCenter Server for Windows supports Microsoft SQL Server as external databases.
Such configuration, however, often means transmitting sensitive or confidential information over the unprotected network. That is why you might want to protect the communication by using an encryption protocol. The latest and most robust secure protocol is TLSv1.2.
 
This KB article describes the steps that you must perform to configure vCenter Server for communication with an external MS SQL Server database via the TLSv1.2 protocol.
 


Environment

VMware vCenter Server 6.7.x
VMware vCenter Server 6.5.x

Resolution

To start using TLSv1.2 for communication between vCenter Server and an external MS SQL Server database, you must perform the following actions:

  • Configure the MS SQL Server machine
  • Configure the vCenter Server machine

Configure the MS SQL Server machine

  1. Ensure that the MS SQL Server machine supports TLSv1.2.
    1. Connect to the MS SQL Server and run the following commands to determine the version of the MS SQL Server machine:
      select concat(convert(varchar, ServerProperty('ProductVersion')), ' ', convert(varchar, ServerProperty('ProductLevel')))
    2. Verify if your current version of SQL Server has support for TLSv1.2. For more information, see TLS 1.2 support for Microsoft SQL Server.
    3. If your current version of SQL Server has no support for TLS 1.2, update it.
  2. Import a trusted certificate to the Microsoft Management Console (MMC) on the MS SQL server.
    1. Obtain a valid private-key certificate for the MS SQL Server machine.
      The certificate should be issued at least for the Server Authentication purpose.
    2. Import the certificate with private and public keys (usually a *.pfx file) to the MMC on the MS SQL Server machine.
      For information on how to import a certificate to the MMC, see How To Import Personal Certificate With MMC.
    3. Navigate to SQL Server Configuration Manager>SQL Server Network Configuration.
    4. Right-click Protocols for MSSQLSERVER and select Properties from the drop-down menu.
    5. Open the Flags tab.
    6. Set Force Encryption to Yes.
    7. Open the Certificate tab and verify that the certificate is trusted.
      If you cannot find the certificate that you installed, see the Troubleshooting section at How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console.
  3. Configure Microsoft Windows to support TLSv1.2:
    1. Create registry keys in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols branch to disable the following encryption protocols by creating Enabled (Type=DWORD32) and DisabledByDefault (Type=DWORD32) values in the corresponding registry branches:
      • Multi-Protocol Unified Hello
      • PCT 1.0
      • SSL 2.0
      • SSL 3.0
      • TLS 1.0
      • TLS 1.1
    2. Create registry keys to enable the TLSv1.2 encryption protocol.
  4. Restart the MS SQL database service.

  5. If the MS SQL database service does not restart, perform the following steps:
    1. Navigate to Control Panel>Administrative Tools and double-click Local Security Policy.
    2. In the Security Setting pane, go to Local Policies>Security Options.
    3. In the right pane, double-click System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing.
      A dialogue box opens. Click Enabled and click OK.
    4. Run gpupdate/force.
    5. Try to start MS SQLSERVER database service again.
    6. If the service does not start, go to C:\ProgramData\Microsoft\Crypto\RSA and grant NETWORK SERVICE user Read permission to the MachineKeys folder.
    7. Restart the MS SQL database service.

     

Configure the vCenter Server machine

  1. Obtain a valid certificate for the machine where vCenter Server is installed.
    The certificate should be issued at least for the Ensures the identity of a remote computer purpose.

  2. Import the certificate to the MMC and verify that it is trusted.

  3. Import the certificate to the JRE on the vCenter Server machine and verify that it is trusted.
    The default location of JRE is the following: <vCenter Installation Directory>\jre\bin.
    "<vCenter Installation Directory>\jre\bin\keytool.exe" -import -noprompt -alias <certificate alias> -file <certificate path>.pem -keystore "<vCenter Installation Directory>\jre\lib\security\cacerts" -storepass <your keystore pass>

  4. Verify that the ODBC driver on the vCenter Server machine supports TLS 1.2.

  5. If the ODBC driver does not support TLS 1.2, use the MSSQL Server Native Client.

    1. Download and install the MSSQL Server Native Client.
      The version of the MSSQL Server Native Client must be identical to the version of the MS SQL Database Server.
    2. Create a new ODBC connection that uses a new version of the MSSQL Server Native Client or if an ODBC connection already exists, update it.

Test the connections to the MS SQL Server database

After you configure the external MS SQL Server and the vCenter Server machines, you might want to check if all the incoming connections to the MS SQL Server database are encrypted. To do this, run the following query:

SELECT DB_NAME(p.dbid) as DBName, p.loginame, s.session_id, s.encrypt_option
FROM sys.sysprocesses p, sys.dm_exec_connections
WHERE p.spid=s.session_id and DB_NAME(p.dbid)='vcdb'