Setting up Server Project with Remote HDB (SQL Server)

Scenario: You want to set up the Server project and link it up with History Database (SQL server) located on remote computer. Furthermore, you want to encrypt the communication between a Server project and the linked HDB, SQL Server located on different computers (SSL on TCP/IP connection) to establish a secured communication.

For this, you must set the encryption in the SMC located on Server and in the SQL Server Configuration Manager located on the remote Server computer. You can use a commercial certificate from Certificate Authorities (CA) or a self-signed or a SMC-created Windows Store based host certificate with a full name of the computer (host name + domain), for example, ABCXY022PC.dom01.company.net.

 

Reference: For background information, see the reference section.

 

Workflow diagram:

 

 

Prerequisites:

  • On Server, SMC is launched and a project is restored or created and activated and started.
    • The root, host certificates used for securing the communication between Server and remote SQL Server are imported in the appropriate Windows Certificate store on Server station using SMC and set as default.
    • The root certificate (.cer file) is imported in the Local Computer and User Certificates > Trusted Root Certificate Authorities (TRCA) store.
    • The host certificate having the full name of the remote SQL Server computer (host name + domain) must be generated using the Server root certificate and (.cer file and .pfx file) is imported in the Local Computer > Personal Store.
  • On Remote SQL Server, same Server root certificate (.cer file) and the remote SQL Server host certificate (.pfx file) is available on the disk.
    • Using MMC the host certificate (.pfx file) is imported in the Current User > Personal Store. See Import the Certificates in the Windows Store using MMC in Security Certificates Procedures.

Steps:

1 – Link the SQL Server Instance to the SMC

You must link the SQL server instance, using its Fully Qualified Domain Name (FQDN), to the SMC.

  1. In the SMC tree, select History Databases.
  1. Open the SQL Servers expander.
  1. Type the SQL server instance name with the fully qualified domain name (host name + domain) in the Manual search field and click Search Server.
  • The fully qualified server instance name appears in the list in the SQL Servers expander.
  1. Select the server instance from the list and select the Linked check box.
  1. Create your HDB on this instance.
  • The HDB instance is created.

 

2 – Set the Encryption in the System Management Console
  1. From the Windows Start menu, select the Desigo CC SMC.
  1. In the SMC tree, select Projects > [project name].
  1. Open the Server Project Information expander.
  1. In the Linked HDB field, select the HDB whose communication you want to encrypt.
  1. Select the Encrypted check box that is enabled only when the HDB that you have linked to the project is on the remote computer (SSL on TCP/IP connection).
    NOTE: Encrypting the communication between Server project and remote SQL Server slows down the performance!
  1. Click Save.
  • The encryption is set in SMC between the Server project and the linked remote history database (SQL Server).

 

3 – Set the Encryption in the SQL Server Configuration Manager
  1. In the Start menu, select Start > All Programs > Microsoft SQL Server [date] > Configuration Tools and click SQL Server Configuration Manager.
  1. Right-click Protocols for [SQL server name] and select Properties.
  1. The Protocols for [SQL server name] dialog box displays. Perform the following steps in this dialog box.
    a. Click the Certificate tab and click Import: A Select Certificate dialog box displays.
    b. Select a valid host certificate from the disk and click Next.
    c. Enter the password for the host certificate and click Next till you reach Finish.
    d. Click the Flags tab in the Protocols for [SQL server name] dialog box.
    e.     In the Force Encryption drop-down list, select one of the following:
    - No: If the Encrypted check box in the SMC is selected, the communication will be encrypted otherwise not.
    - Yes: If the Encrypted check box in the SMC is not selected, the HDB will not connect to the project and you must encrypt the communication.
  1. Click Apply and OK.
  • The encryption is set in the SQL Server Configuration Manager.