Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Encrypting SQL Server Connections

Posted by jeffprom on September 5, 2013

If you use SQL Server in the cloud you may not always have a secure connection. Thankfully there is support in several areas within SQL Server to help secure connections. Let’s take a look at a couple.

SQL Server Management Studio
1. In SSMS, go to Object Explorer
2. Click the Connect drop-down, and select Database Engine
3. Enter the Server name and login credentials (don’t click Connect yet!)
4. Click on the ‘Options >>’ button
5. On the ‘Connection Properties’ tabe, check the box for ‘Encrypt Connection’

6. If you have an untrusted certificate on your server (see below), click on the ‘Additional Connection Parameters’ tab. Enter ‘TrustServerCertificate=True’.

7. Click the ‘Connect’ button.

PowerPivot
1. Open Excel
2. Click on the PowerPivot tab
3. Click on the Manage button
4. Select the drop down under ‘From Database’
5. Select ‘From SQL Server’
6. Enter the Server name, login credentials, and Database name
7. Click the ‘Advanced’ button

8. Scroll down to the bottom and set the following:
Trust Server Certificate = True (see certificates below)
Use Encryption for Data = True

9. Click Ok
10. Select the data sources (tables)

Certificates
Digital certificates can come from various sources. There are several ‘trusted’ 3rd party sources such as Thawte and VeriSign that are called Certificate Authorities (CA). To get a certificate from a trusted source you will most likely need to pay an annual fee. If you have a trusted certificate, you shouldn’t have to tell SQL Server that you want to trust the certificate.

On the other hand, if you don’t have a trusted certificate, you should already have a self signed certificate that Windows created.

To see a list of certificates installed on your computer, do the following:
1. click on Start, Run, MMC
2. File, Add/Remove Snap-in…
3. Certificates, Add
4. Select Computer Account, Next
5. Local Computer, Finish
6. Ok
7. Expand Certificates, Personal, and click on Certificates

This shows a list of certificates currently installed. From here you can import and export certificates by right clicking and going under the ‘All Tasks’ menu.

Securing connections in SQL Server is a great way to keep your data private over unsecure lines. SQL Server uses certificates to establish secure connections. Be sure to use these techniques to create secure connections from SSMS or PowerPivot when venturing out into the open cloud.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: