Jan 29, 2015 3:43:00 PM
Microsoft SQL and SSL Certificates: Securing your Data with PKI

In today's corporate environment, enterprise companies are choosing to secure intellectual property and customer data to not only protect against the damage associated with data breaches but also to comply with privacy and regulatory mandates.

Luckily there are several ways to protect data included in the SQL Server Encryption Key Hierarchy. Options range from cell-level and column-level to full database encryption (TDE).

Encrypting an entire database on the hard disk using transparent data encryption is transparent and there’s no noticeable performance overhead. This is effective in protecting the data while at rest. For example, if someone were to steal a copy of the database or a backup of the data, the encryption layer would protect the data and make the database unreadable without the appropriate decryption keys.

However, data is decrypted prior to transmitting over the network, therefore, we need to think of encrypting the communication, specifically the network layer. While several options exist for the communications, SSL is the most commonly implemented method. SSL uses certificates to authenticate the server to the client and establish an encrypted communications channel with the database. Unlike other options, like IPsec, which is implemented at OS level and supports authentication using Kerberos certificates, when an SSL certificate is used it is configured on SQL server. Configuring SSL on the server is more straightforward than configuring IPsec. In addition, SSL requires minimal client configuration.

But why use SSL certificates in the first place? Not only are they needed in today's IT infrastructure to encrypt sensitive information as they travel across networks, but in addition to encryption, a proper SSL certificate also provides authentication. This means you can be sure that you are sending information to the correct server, since anyone can pretend to be a website where you might send your sensitive, personal and confidential data.

Hacks, breaches and leaks are all possible to avoid by using a proper Public Key Infrastructure (PKI) Root and Issuing Certificate Authority and getting an SSL Certificate from a trusted authority provider.

public key infrastructure

How do I encrypt SQL Server connections between my applications and database for the better security? When an SSL certificate is used, the information becomes unreadable to everyone except for the server you are sending the information to. This way the SSL protects the connection, i.e. the data as it transits between the client and the SQL Server.

But what is the performance difference between the encryption overhead of SSL versus unencrypted socket communication? Encryption may slow down performance because it requires extra actions on both sides of the network connection but the benefits still outweigh the performance penalty especially when clients connect to SQL servers across the public networks. The main overhead of SSL is the handshake and after negotiation there are relatively fast cyphers used.

So what else is involved when encrypting data transmission from data and the web application clients? You may either use IIS manager to create a self-signed certificate or you can duplicate a web server template from your company's issuing certificate authority then add it through the Certificates MMC for the computer account. Even though, it is possible to use self-signed certificates, it is recommended only when doing it for test purposes because it significantly lowers the level of security. The next step is to give the SQL server's service account read permissions on the certificate, and choose the certificate in SQL Server's network configuration in configuration manager. Also, you may need to append "encrypt=true" in the connection string in your applications among other things such as MSSQL Library (for example for web-based applications) etc.

In order to have the full benefits of SSL certificates, the implementation has to be done correctly from the start. For example, the role of Certificate Services that is offered as a server role in Microsoft Windows Servers is deployed in a secure way, in line with best practices, and the Root Certificate Authority and all its Issuing Certificate Authorities are protected. The deployment does not consist of only installing a role but also using an HSM (Hardware Security Module) to secure keys and using several servers to achieve the best PKI deployment with Certificate Revocation list checking.  A properly built PKI should be a cornerstone of security for an organization and support many use cases for authentication, digital signature and encryption.

Certified Security Solutions (CSS), offers software and products that work together with your PKI and adding value to the deployment. By name, the Certificate Management System (CMS) delivers enhanced levels of secure identity for devices outside of corporate firewalls and uses Transparent Data Encryption. CMS uses SQL column-level encryption for any column(s) that need to be encrypted and is able to fully manage and monitor your company's PKI and its reporting.

Learn more about CMS