|This post discuss about security of SQL Server data and steps required to configure SSL Encryption in SQL Server. There is a feature named Transparent Data Encryption in SQL server 2008 which provides security for static data, what about the data that flows over the network. SSL Encryption can answer this question.|
What is SSL Encryption?
Secure Sockets Layer(SSL) encryption encrypts network traffic between the clients and server. This feature was introduced long back in SQL version 2000. Enabling SSL encryption increases the security of data transmitted between instances of SQL Server and applications.
Encryption is useful when client connects to the SQL Server over internet and when data transfers over public networks.
To use SSL encryption, you need to buy a certificate from a trusted certificate authority and then install and configure it in SQL Server.
You can see the transferring data over network using Net monitor tool when some one sends a request to SQL Server.
When there is no SSL encryption on SQL Server then any one can see the data over network.
Configuring SSL Encryption in SQL Server
This configuration is on SQL Server 2008 R2 and operating system is Windows Server 2008 R2.
Add certificate services if it is not already installed on the server by going to server manager as shown below
click next and leave the default settings and click install button
Now you can browse localhost/certserv in internet explorer then you will see the below screen
click on download CA certificate and send a separate request for server side and install
After installing the certificate, open SQL Server Configuration Manager and right click protocols then select properties
In properties window set the Force Encryption to Yes
Under Certificate tab, select the certificate that you have installed
Once the above steps are complete, restart the SQL Server service.
Now re-run the Netmon tool then you will notice the data got encrypted.
reference Channel 9 show