Network packets are human-readable using a packet sniffer when SQL results passing between client machine and SQL server. Tabular Data Stream(TDS) is the protocol used by SQL server to form packets to be sent through the network. This post explains the steps to encrypt a session with SSL in SQL Server.
Below is the screenshot of packet sniffer when data flowing from server to client
To secure the communication with SSL in SQL server then purchase a certificate from a Certificate Authority (CA), such as VeriSign, DigiCert. You need to install this certificate on SQL server with service account.
1. Open SQL server Configuration manager and select SQL server network configuration node
2. Right click the protocols for SQL server instance and select properties
3. On the Flags tab, choose Yes for Force Encryption as shown below
4. on the certificate tab, add your installed certificate.
5. click on ok.
6. you need to restart the SQL server service for it to take effect.
If you do not choose the force encryption option then you can specify it in connection string in your client code.
Driver={SQL Server Native Client 11.0};Server=<servername>;Database=<database>; Trusted_Connection=yes;Encrypt=yes;
7. You can also do it when connecting to SQL server using management studio, In the connect to database engine dialogue box, click on options
8. Go to the connection properties tab, and check Encrypt connection
9. click on connect
Reference: http://www.packtpub.com/microsoft-sql-server-2012-security-cookbook/book
I highly recommend to read this book to learn some practical examples on security concepts in SQL server 2012.
| Share this post : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
