:: infotechGuyz.com ::
> For the IT community by the IT community

• BlackBerry Server (BES)
• Excel Macro/VBA
• Exchange 2007
• Exchange 2010
• Lync 2010
• Scripting/Command-line
• SQL Server 2008
• SQL Server 2012
• Team Foundation Server
• VMware
• Windows 7
• Windows 8
• Windows Server 2008
• Windows Server 2012

SSL Encryption in SQL Server 2008 R2

 

 

With SQL Server 2008, it is stress-free to set up SSL (SSL) encrypted client/server communication via all protocols. The SNAC net-library handles the jobs of encryption and decryption on both the server and client ends. Setting it up involves both server-side and client-side configuration changes; this segment covers only the client-side alterations in detail.

SQL Server 2008 enables encryption using two types of certificates:

• Certificates produced by and obtained from an external certification authority such as VeriSign

• Certificates produced by SQL Server 2008 (known as self-signed certificates) The bit strength of the encryption (40-bit or 128-bit) be subject to on the bit strength of the OS of the systems involved in the connection.

To set up the server for encryption, your admin registers a cert on the server OS (using the Certificates MC) and then installs it in the

DB Engine. If an externally signed certificate is not mounted on the server, SQL Server uses its built-in self-signed certificate. (A server admin may also create and save a self-signed certificate by using SQL Server 2008 via the new CREATE CERTIFICATE and BACKUP CERTIFICATE T-SQL syntax.) It is likewise up to the server to decide whether encryption is necessary or optional for connecting clients.

The client's partial of the job is to have installed what is identified as a root-level certificate that is issued by the same CA as the server's certificate. To install a root-level certificate, you right-click the certificate itself (a . cer or .cr t file) and select Install Certificate to launch the Certificate Import Wizard. You click Next on the welcome screen to reach the Certificate Store screen. Now you select the first radio button (Automatically Select the Certificate Store) and then click Next. Lastly, you click Finish.

Then, you open SSCM, rightclick the SQL Native Client 10.0 Configuration node, and then choose Properties. You set the Force Protocol Encryption property value to Yes. This allows clients to demand an SSL-encrypted connection when interacting with the DB Engine. If the server does not respond in kind, the connection is killed. The Trust Server Certificate attribute gives clients a select in how they deal with server certificates:

• To use a selfsigned cert, you set the property value Yes. This decision prevents SNAC from validating the server's certificate.

• To use an externally signed cert, you set the property value No, which reasons SNAC to validate the server's certificate.

SSMS can also link over an encrypted connection. When connecting using the Connect to Server dialog, you click the Options button and then click the Connection Properties. Then you select your database and protocol and, at the bottom left, check the Encrypt Assembly check box.