Enabling SQL Server Traffic Encryption
To enhance database communication security, all connections to a SQL Server database may be encrypted by using industry standard SSL encryption.
The following steps are required to secure communication to and from SQL Server:
- Obtaining a digital certificate
- Configure the database server to work with the certificate
- Configure the client-side software to use SSL when communicating with SQL Server
Note: Because there are a lot of applications and services that use SQL, this article will not discuss the client-side configuration steps. Instead, you will need to read the software vendor’s documentation to learn how to do that. I will only describe the steps needed to be taken on the SQL Server-side.
Say Goodbye to Traditional PC Lifecycle Management
Traditional IT tools, including Microsoft SCCM, Ghost Solution Suite, and KACE, often require considerable custom configurations by T3 technicians (an expensive and often elusive IT resource) to enable management of a hybrid onsite + remote workforce. In many cases, even with the best resources, organizations are finding that these on-premise tools simply cannot support remote endpoints consistently and reliably due to infrastructure limitations.
Enabling SQL Server traffic encryption can help enhance overall data security. (Image: Dreamstime)
Obtaining a Digital Certificate
To enable SSL encryption, you need to obtain a valid digital certificate. Digital certificates can be obtained from three different sources:
- From a commercial trusted root certificate authority (CA), such as GoDaddy, Verisign, Thawte, Rapidssl and others
- From an internal trusted certificate authority
- By using a self-signed certificate
Although all three options are valid, it may be easier to use a self-signed digital certificate for this purpose. Instructions refer to using a self-signed digital certificate. This is mostly true because unlike websites that use SSL, which may have many different client connections to them, in most cases, SQL Servers only communicate with a selected few client machines, which in turn may be servers or desktop computers. The fact that we only need to deal with a small number of client computers means that you will only need to import the self-signed certificate a few times. That is why it’s easier to work with self-signed certificates on a small number of client computers.
Creating a Self-Signed SSL Digital Certificate
To create a self-signed SSL certificate for securing communication between the server-side components and the SQL Server, do the following:
1. Download the Microsoft MAKECERT utility.
MAKECERT does not come pre-installed on Windows Server 2012/R2. To get it, you will need to download the Windows 8 Software Development Kit (SDK). Although the actual download may be big, MAKECERT is part of the core installation of the SDK, so you will not need to download and install the entire SDK.
When downloading the SDK, select only “Windows Software Development Kit,” unless you wish to install the entire SDK.
After installation, MAKECERT will be located in the C:Program Files (x86)Windows Kits8.0binx64 folder.
Another source for MAKECERT is Visual Studio 2013 Express, which may be downloaded from Microsoft.
2. Run the following command on the ObserveIT SQL Server computer, and don’t forget to change to this path: C:Program Files (x86)Windows Kits8.0binx64.
makecert -n "CN=<Name of SQL SRV>" -sk CertificateContainer -sr LocalMachine -ss My -a sha1 -sky exchange -pe -r -m 120 -sp "Microsoft Strong Cryptographic Provider" -sy 1
Where <Name of SQL SRV> is the FQDN of the SQL Server computer. For example:
makecert -n "CN=sqlsrv.domain.local" -sk CertificateContainer -sr LocalMachine -ss My -a sha1 -sky exchange -pe -r -m 120 -sp "Microsoft Strong Cryptographic Provider" -sy 1
Note: The above command is just one example. MAKECERT has many parameters, where you can read more about this tool on the Microsoft Developer Network.
You’ve successfully created a self-signed digital certificate, where it is currently saved in the current directory where MAKECERT is located.
Installing the Certificate
The following procedure describes how to install the certificate using the Certificates Microsoft Management Console (MMC) snap-in.
1. Go to “Start” -> “run” and enter “mmc.”
2. Go to “File” -> “Add/Remove Snap-in.”
3. Select the “Certificates” snap-in, click “Add,” and assign it to the local computer account (Computer Account -> Local Computer).
Installing the certificate using the Certificates Microsoft Management Console (MMC) snap-in. (Image: Daniel Petri)
4. Select “Certificates” again, click “Add,” and assign it to the local service account: “SQL Server (MSSQLSERVER).”
Assigning certificate to local service account. (Image: Daniel Petri)
Selecting computer you want the snap-in to manage. (Image: Daniel Petri)
Adding SQL Server as the service account. (Image: Daniel Petri)
5. In the MMC, under “Local Computers > Personal,” right-click the certificate and select “All Tasks > Manage Private Keys.”
Managing private keys. (Image: Daniel Petri)
6. Add the identity that is running the “SQLSERVER” service (from Component Services), and assign it “Full Control” permissions.
Note: Unless changed by the administrator, this is usually the “NT ServiceMSSQLSERVER” account. To find it, you must select “Service Accounts” in “Object Types,” and the name of the SQL Server in “Locations”. Then paste the “NT ServiceMSSQLSERVER” string and click “Check Names.”
Finding the NT ServiceMSSQLSERVER account. (Image: Daniel Petri)
Finding the NT ServiceMSSQLSERVER account in Select Users or Groups dialog. (Image: Daniel Petri)
The NT ServiceMSSQLSERVER account result. (Image: Daniel Petri)
7. Export the certificate from “Local Computers > Personal” by right-clicking the certificate and selecting “All Tasks > Export.” Use the default export settings, and do not export the private key.
Exporting all tasks. (Image: Daniel Petri)
The Certificate Export Wizard. (Image: Daniel Petri)
Saving the digital certificate with the Certificate Export Wizard. (Image: Daniel Petri)
8. Import the exported certificate to “Service (SQLSERVER) > Trusted Root Certification Authorities”.
Importing the exported digital certificate. (Image: Daniel Petri)
Specifying the file to import with the Certificate Import Wizard. (Image: Daniel Petri)
Placing all certificates in the designated store with the Certificate Import Wizard. (Image: Daniel Petri)
9. Restart the SQLSERVER service.
Configuring the Database Server for Encryption
1. Open the SQL Server Configuration Manager, and expand “SQL Server Network Configuration.”
2. Right-click “Protocols for MSSQLSERVER” and select “Properties”.
The SQL Server Configuration Manager.. (Image: Daniel Petri)
3. In the Properties dialog, under the “Flags” tab, set “Force Encryption” to “Yes”.
Displaying Properties with the SQL Server Configuration Manager. (Image: Daniel Petri)
4. Under the “Certificate” tab, select the certificate that you created.
Selecting a certificate with the SQL Server Configuration Manager. (Image: Daniel Petri)
5. Click “Apply”, and then click “OK”.
6. Restart the SQLSERVER service.
Restarting the SQL Server service. (Image: Daniel Petri)
Finally, you will need to follow the 3rd-party vendor’s documentation to enable encryption for SQL traffic on the client-side.