Verifying SQL Sserver Identity in a Network

When running a server in complex and vast networks or when processing confidential data it's necessary for a client to verify that it's connecting to the correct server. There can be a false SQL server that stands out with the same name as the correct server and imitates its runtime.

Connecting to the correct server ensures registering the server's IP address for a client. There can however be a situation when the false server may use the IP address of the correct server.

Therefore there exists a reliable method, that can determine the identity of a server. This method has its base in an asymmetric encryption algorithm. The server generates its private key and a certificate containing the public key. The certificate is then granted to all potential clients and the private key is kept secret. Using the certificate each client is able to check whether it's communicating with the server with the corresponding private key.

Only the security administrator may manipulate with the certificate and the SQL server private key.

Utilizing the certificate allows you beside the SQL server verification also the use of the encrypted communication between a client and a server. Encrypted communication is not enabled without this certificate.

Creating the Key and the Certificate

The most simple way how to obtain the private key and certificate, is to create a certificate signed by its owner. No third party guarantees such certificate's validity, however it serves well for verifying server identity.

Go to the Server certificate windows opened from the System / Tools:

Press the Start button;
move the mouse cursor over the rectangle until the gauge fills in, and a window appears with a information about the gathered random data.
When you close the window, the two keys are created and stored in the database.

The server identity verification is then enabled for all network clients connecting to this server.

Importing Certificate and Private Key from Files

You can use the following if you have the private key and certificate stored in files. These files may be created by some specialized programs like openssl that serve for creating certificates and private keys.

Importing certificate and private key to a SQL server enables identity verification for all accessing clients.

Both keys are imported from the Server certificate opened from the System / Tools in this way:

Select the Import the certificate ... tab
Select the certificate file by the Import the certificate from file button;
Select the private key file by the Import the secret key from file button.

Utilizing Keys Created by openssl

You can import the key created by openssl into a SQL server, if it's not encrypted and if it's in the PEM format. You can use the following command to create a private key and certificate with your own signature:

openssl req -x509 -new -outform DER -out cert.der -nodes -keyout key.pem

When importing a private key created in this way, you have to leave the field for password empty.

Protecting the Private Key

Verifying the server identity is reliable as long as the private key is kept safely hidden. When the private key gets stolen, it's very simple to create a false server.

The private key stored in a file is reliably secured by a password. However it's necessary to choose a password that cannot be easily guessed, because it's not difficult to try some commonly user passwords.

The private key stored in the database cannot be accessed from the client interface. However it can be read by an analysis of the database file. Therefore if the database file is not secured from reading it should be encrypted.

How is the Server Identity Verified by the Client?

The network client (not local client) may use these three methods to verify server identity:

When the client connects to a SQL server for the first time, it downloads and saves its certificate. For any further connection it's checked whether the client communicates with the same server as for the first time. This method works fully automatical and each access to the server is verified (except for the first).
The SQL server operator may publish the so-called certificate miniature to all potential clients. The client may verify on first connection, that the server it's connecting to has the same certificate miniature.
The client may verify, whether the certificate it received from the server is signed by some trustworthy certificate authority. This verification isn't valid if the server uses a certificate that it itself signed.

You can see the certificate downloaded from a remote SQL server by selecting the appropriate server on the Control Panel and opening the Server connection data dialog from its popup menu. Then press the Show button. You can see the data regarding the certificate in the window that appears.

Creating a New Server of the Same Name

If a client was accessing a SQL server of a certain name and then wants to connect to a different server of the same name, then the identity verification system won't allow its connection. The new server is considered a false server pretending the identity of the old server.

To work with the new server, the client has to delete the old certificate first. Just press the Erase button in the Server connection data dialog.

Remote Access to a SQL Server Using the HTTP-tunnel

Client - Server Communication and Network Protocols

Client - Server Network communication Encryption