Securing Your SQL Server Application: Enabling Server-Initiated Encrypted Connections

Network encryption is a vital security step in hardening your application and guarding your data. Additionally, it is more often becoming a requirement by many organizations and laws (like HIPAA).

In this article, we will discuss how to secure network communication between your application and the SQL Server database using an encrypted connection. We will demonstrate how encryption can be enabled and leveraged from SourcePro DB with no source code changes to your application.

Code to Query Connection Security

Using this code snippet, we will establish a connection to a SQL Server database server and query the type of connection made to the database. Note this example assumes that the user account has been granted the permission VIEW SERVER STATE.

const RWDBDatabase db =
  RWDBManager::database("MS_SQL", , , , );
const RWDBConnection conn = db.connection();
RWDBResult result = 
  conn.executeSql("SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID");
const RWDBTable resultTable = result.table();
RWDBReader reader = resultTable.reader();
std::cout << "Connection using services" << std::endl;
while (reader()) {
    RWCString protocol;
    reader >> protocol;
    std::cout << "Encrypt Option " << protocol << std::endl;
}

Encrypt Option FALSE

As we can see, the server has indicated that the current connection is not encrypted. Let us now set about changing that.

Generate Secure Certificate

For this article we will use the openssl command line utility to generate a self-signed certificate. Note the common name for the certificate (/CN) should match your SQL Server host fully qualified domain name. If you are on UNIX, you will need to modify the permission of the public certificate and private-key files to be read only to the user and group.

openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=server.example.com' -keyout mssql.key -out mssql.pem -days 365
chmod 440 mssql.pem
chmod 440 mssql.key

Update the mssql.conf Server Configuration File

The mssql.conf file now needs to be updated to configure the server for secure connections.

[network]
tlscert = /mssql.pem
tlskey = /mssql.key
tlsprotocols = 1.2
forceencryption = 1

This configuration file has the server use the public certificate and private-key that we generated in the section above. By default, SQL Server allows connections from TLS (1.0, 1.1, 1.2), we will set the server to only allow TLS 1.2. Lastly, we will instruct the server to force all connections to be encrypted by setting forceencryption.

With these changes the server can now supports and requires encrypted connections. The last thing we need to do is stop/start the server for the new settings to take effect.

A Secure Connection

Having made these changes, we can use our same example from before. We do not need to make any code changes or re-compile the executable. Running this executable now the connection security is reported as:

Encrypt Option TRUE

As we can see from the output, the server is now reporting that the connection is secure.

Conclusion

As we have seen it is very simple to enable server initiated communication in SQL Server and SourcePro DB requires no source code changes to utilize this encrypted connection.

But what if we want to have the client initiate the encrypted connection (or to have both sides employ encryption)? Next time we will discuss how to implement that.

In the meantime, you can request a free evaluation of SourcePro. With SourcePro, you write your code once and deploy it on any platform. This helps you reduce time-to-market, increase reliability, and extend the life of your applications.

REQUEST AN EVALUATION