Configuring encryption for encyclopedia connections
This topic covers the use of encrypted connections to encyclopedias meaning that data passed between the System Architect client application and the database server is encrypted.
System Architect versions
In System Architect products, requesting encrypted connections from the client side is supported from version 11.4.5.1.
Use of the Transport Layer Security (TLS) protocol version 1.1 or greater is supported from version 11.4.5.1.
Security protocols
We support SSL (Secure Sockets Layer 2 and 3) and TLS (Transport Layer Security) 1.0, 1.1 and 1.2.
From System Architect 11.4.5.1, the following settings should be made in sa2001.mst.
[ADO] Connections_SQLProvider=SQLNCLI11 ; ^ this is the original provider that was required to support encryption. The System Architect installation may set a different value. Connections_DataTypeCompatibility=80
Without these settings being present, the old SQL OLE DB provider is used and that only supports up to TLS 1.0.
so, for instance, you can have a TLS 1.2 subkey with client and server subkeys and each can contain these DWORDs:
DisabledByDefault(0), Enabled(0 or 1).
The server subkeys are required only on the server; the client subkeys are required only on the client. The server can be configured with all enabled, then you must restart the SQL Server service. System Architect needed to be restarted after changing these settings for it to be able to browse for and open an encyclopedia. System Architect Encyclopedia Manager (SAEM) did not need to be restarted.
This tells you that by default the protocols are all enabled except for SSL 2.0:
If no security protocol is enabled you get this message on attempting to open a connection: Encryption not supported on the client.
Precise level of support
▪System Architect, System Architect Encyclopedia Manager, Catalog Manager, SAREST, SAXT Reader and Updater were tested successfully with only TLS 1.2 enabled.
SQL Server requirements for TLS 1.2
It is supported out of the box in SQL Server 2016. Prior versions require upgrading. All of the relevant material can be found here:
There is a server configuration that can be made to force the use of encryption for all connections. This carries no side-effects and does not require installation of a certificate on the client machine. The following link tells you where to find the configuration, just skip the part about certificates for now:
Without using the above, System Architect applications must be configured to request the use of encryption through the following ini or mst settings:
[ADO] Connections_UseEncryption=yes
This setting should be made in the mst file in order for it to apply to SAREST and SAXT. The side-effect is that the server must be specified using the name in the certificate which is usually the fully-qualified domain name of the server. Without so specifying the server name you will generally receive error message target principal name is incorrect or a more general error on connecting to a database.
Connections_UseEncryption_Servern=[full domain name of server]\server name For example, Connections_UseEncryption_Server1=myservermachine.mydomain.com\myserver
If this setting is found (the match is not case sensitive) then it means encryption will be requested only for the named servers. If there is no Connections_UseEncryption_Server1 setting then the Connections_UseEncryption setting (see above) determines whether encryption is enabled. The settings are read in number order until a setting is not found, so ensure that the number sequence is contiguous and starts from 1.
Note This means that if you do not use the full server name for a server that you are supposed to be using encryption for, you will not receive a target principal name is incorrect error message and the connection will not be encrypted.
Certificates
When requesting encryption from the client, encryption can only be used with a security certificate installed on the server and the client machine.
▪Certificates are normally requested through certificate providers.
▪The following link tells you how to create a self-signed certificate in various ways:
▪You have to copy the certificate to the Trusted Root Certification Authorities folder in MMC by using Ctrl+drag (it ends up in the Certificates folder underneath that).
▪You have to enable some protocols in the SQL Server Configuration Manager such as Named Pipes for the connection to be allowed.
▪Export the certificate from the personal folder of the certificates manager in MMC.
On the client machine:
▪Use MMC to import the exported certificate.
▪Copy it to the Trusted Root Certification Authorities folder (it ends up in the Certificates folder underneath that).
▪Without doing that you will receive error message SSL Provider: The certificate chain was issued by an authority that is not trusted on attempting to open a connection.
Confirming use of encryption
From SAEM you can verify that you have an encrypted connection by executing the following SQL, which returns a TRUE or FALSE value:
SELECT DISTINCT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID
From System Architect you can verify that an encrypted connection has been requested by opening Microsoft Visual Basic for Applications: open the Immediate window by pressing Ctrl+G, and type:
?sa2001.encyclopedia.connectstring
and then press return – the connection string value should be presented in the Immediate window.
The connection string should include this:
Use Encryption for Data=True;
Note The above will be present only if the following sa2001.ini or .mst settings was made:
This is reported by SAXT if any error occurs on attempting to open a connection to obtain the list of encyclopedias when logging in.
Error opening encyclopedia
This is reported by SAXT when logging in as Updater if no security protocol is enabled on attempting to open a connection.
target principal name is incorrect
This happens when encryption is requested through the [ADO]Connections_UseEncryption=yesini/mst setting and the server is not identified using the common name (CN) field value in the certificate.
SSL Provider: The certificate chain was issued by an authority that is not trusted
This happens when the certificate has not been copied to the Trusted Root Certification Authorities folder in MMC.
There is no catalog on server ... that you have permission to access
This is reported by Catalog Manager if there is any error on attempting to open a connection.
You could not be logged in to the server
This is reported by SAEM if no security protocol is enabled on attempting to open a connection.
Encryption not supported on the client
This happens if no security protocol is enabled on attempting to open a connection.
It is possible for a user to log in to SAXT as Reader or SAREST when encryption has been requested from the client or the server side when no security protocols are enabled. The responsibility for correct client and server configuration lies with the customer.