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

Introduction

In the previous article we discussed how to enable a server initiated encrypted connection to a Microsoft SQL Server. But what if we have a scenario where we do not want to incur the overhead of encryption for every application? In that scenario instead of configuring the server to force encryption we will instead need the client to initiate the encrypted connection.

Update the mssql.conf Server Configuration File

Starting from the client and server we configured in our previous article we will need to modify the server mssql.conf file now to allow both encrypted and unencrypted connections by setting forceencryption to be 0. 

[network]
tlscert = <path_to_file>/mssql.pem
tlskey = <path_to_file>/mssql.key
tlsprotocols = 1.2
forceencryption = 0

With this change the server remains configured to support an encrypted connection however it will not require one. The final thing we need to do is stop/start the server for the new settings to take effect.

Verify Connection Security Is Not Required

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 the permission VIEW SERVER STATE.

const RWDBDatabase db =
  RWDBManager::database("MS_SQL", <SERVER>, <USERNAME>, <PASSWORD>, <DB>);
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

At this point we have demonstrated that the server does support an unencrypted connection. Let us now explore a couple of ways we can initiate a secure connection from the client side.

Configure a Data Source Name (DSN)

To set up a secure connection on Windows we will need to again import the certificate into the OS trusted chain and update the Data Source for our connection to require Strong Encryption.

Import Certificate Into OS

Because we are going to use the self-signed public certificate, we generated above we will need to import the public certificate into the user certificate keychain.

Run “Manage user certificates” from your client machine’s Control Panel and import the .pem file as a certificate under Trusted Root Certification Authorities -> Certificates.

Update DSN to Enable Strong Encryption

Either create or update an ODBC Data Source and make sure the checkbox for “Use strong encryption for data.” is selected.

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

Application Code

The above solution is great however what if you do not want to manage multiple Data Sources for encrypted and unencrypted connections to your database. SourcePro DB provides a programmatic method that you can use to enable a secure connection.

Update the Call to Get a Connection to the Database

Rather than provide individual server, user name and password parameters to RWDBDatabase::manager() you can instead pass an ODBC Connect string. This allows you to specify additional connection options when the connection to the database is opened.

Using our example, we can update the RWDBDatabase::manager() call to be:

const RWDBDatabase db =
  RWDBManager::database("MS_SQL", 
  "DSN=<SERVER>;UID=<USERNAME>;PWD=<PASSWORD>;DATABASE=<DB>;ENCRYPT=YES", "", "", "");

A Secure Connection

After we have made these changes we can re-compile the executable. Running this executable now the connection security is reported as:

Encrypt Option TRUE

As you would suspect if ENCRYPT is instead set to NO then the connection will be reported as not encrypted by the server.

Putting it All Together

In the previous article and this one we have looked at how to enable server initiated and client initiated secure connections. The best practice to come away with is to enable both.

This allows you to verify that your server is going to require a secure connection and your application will always require a secure connection. By doing this you take steps to ensure that regardless of configuration changes to either the server or the client you can have some assurance that a secure connection has used.

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