Please ensure Javascript is enabled for purposes of website accessibility
Powered by Zoomin Software. For more details please contactZoomin

AVEVA™ Plant SCADA

TABLE OF CONTENTS

Configure encryption in SQL server

Save PDF
Save selected topicSave selected topic and subtopicsSave all topics
Share
Share to emailCopy topic URL
  • Last UpdatedJul 19, 2024
  • 4 minute read

We recommend you enable encrypted connections for SQL Server. You enable encrypted connections for an instance of the SQL Server Database Engine and use SQL Server Configuration Manager to specify a certificate. The server computer must have a certificate provisioned. To provision the certificate on the server computer, you import it into Windows. The client machine must be set up to trust the certificate's root authority.

SQL Server can use Transport Layer Security (TLS) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The TLS encryption is performed within the protocol layer and is available to all supported SQL Server clients.

Enabling TLS encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, when all traffic between SQL Server and a client application is encrypted using TLS, the following additional processing is required:

  • An extra network round trip is required at connect time.

  • Packets sent from the application to the instance of SQL Server must be encrypted by the client TLS stack and decrypted by the server TLS stack.

  • Packets sent from the instance of SQL Server to the application must be encrypted by the server TLS stack and decrypted by the client TLS stack.

Certificate Requirements

For SQL Server to load a TLS certificate, the certificate must meet the following conditions:

  • The certificate must be in either the local computer certificate store or the current user certificate store.

  • The SQL Server Service Account must have the necessary permission to access the TLS certificate.

  • The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.

Install on a Server

With SQL Server 2019 (15.x), certificate management is integrated into the SQL Server Configuration Manager. SQL Server Configuration Manager for SQL Server 2019 (15.x) can be used with earlier versions of SQL Server.

If using SQL Server 2012 (11.x) through SQL Server 2017 (14.x), and SQL Server Configuration Manager for SQL Server 2019 (15.x) is not available, follow these steps:

  1. On the Start menu, select Run, and in the Open box, type MMC and select OK.

  2. In the MMC console, on the File menu, select Add/Remove Snap-in.

  3. In the Add/Remove Snap-in dialog box, select Add.

  4. In the Add Standalone Snap-in dialog box, select Certificates, select Add.

  5. In the Certificates snap-in dialog box, select Computer account, and then select Finish.

  6. In the Add Standalone Snap-in dialog box, select Close.

  7. In the Add/Remove Snap-in dialog box, select OK.

  8. In the Certificates snap-in, expand Certificates, and then expand Personal.

  9. Right-click Certificates, point to All Tasks, and then click Import.

  10. Right-click the imported certificate, point to All Tasks and then select Manage Private Keys. In the Security dialog box, add read permission for the user account used by the SQL Server service account.

  11. Complete the Certificate Import Wizard, to add a certificate to the computer, and close the MMC console. For more information about adding a certificate to a computer, see your Windows documentation.

Export Server Certificate

To export the server certificate:

  1. From the Certificates snap-in, locate the certificate in the Certificates / Personal folder.

  2. Right-click the Certificate, point to All Tasks, and then select Export.

  3. Complete the Certificate Export Wizard, storing the certificate file in a convenient location.

    Configure Server

    Configure the server to force encrypted connections. The SQL Server service account must have read permissions on the certificate used to force encryption on the SQL Server. For a non-privileged service account, read permissions will need to be added to the certificate. Failure to do so can cause the SQL Server service restart to fail.

    To configure the server:

    1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.

    2. In the Protocols for <instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop-down for the Certificate box, and then select OK.

    3. On the Flags tab, in the ForceEncryption box, select Yes, and then select OK to close the dialog box.

    4. Restart the SQL Server service.

      Configure Client

      Configure the client to request encrypted connections.

      1. Copy either the original certificate or the exported certificate file to the client computer.

      2. On the client computer, use the Certificates snap-in to install either the root certificate or the exported certificate file.

      3. Using SQL Server Configuration Manager, right-click SQL Server Native Client Configuration, and then select Properties.

      4. On the Flags page, in the Force protocol encryption box, select Yes.

        Note: The sections in this topic have been derived from the Microsoft documentation. For more information, refer to the topic 'Enable encrypted connections to the Database Engine' in Microsoft Documentation.

        In This Topic
        Related Links
        TitleResults for “How to create a CRG?”Also Available in