SQL Server 2008 Books Online (November 2009)
Encrypting Connections to SQL Server

SQL Server supports Secure Sockets Layer (SSL) and is compatible with Internet Protocol security (IPSec).

Secure Sockets Layer (SSL)

Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to all SQL Server clients except DB Library and MDAC 2.53 clients.

SSL can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server. Encryption with a self-signed certificate is possible and is described in the following section, but a self-signed certificate offers only limited protection.

The level of encryption used by SSL, 40-bit or 128-bit, depends on the version of the Microsoft Windows operating system that is running on the application and database computers.

Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling encryption does slow performance. When all traffic between SQL Server and a client application is encrypted using SSL, the following additional processing is required:

  • An extra network roundtrip is required at connect time.
  • Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.
  • Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.
Configuring SSL for SQL Server

The following procedure describes how to configure SSL for SQL Server.

To configure SSL

  1. Install a certificate in the Windows certificate store of the server computer.
  2. Click Start, in the Microsoft SQL Server program group, point to Configuration Tools, and then click SQL Server Configuration Manager.
  3. Expand SQL Server Network Configuration, right-click the protocols for the server you want, and then click Properties.
    ms189067.note(en-us,SQL.100).gifNote:
    This is the Protocols for <instance_name> section in the left pane of the tool, not a specific protocol in the right pane.

  4. On the Certificate tab, configure the Database Engine to use the certificate.
  5. On the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted.
    • When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.
    • When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.
    • SQL Server must be restarted after you change the ForceEncryption setting.
    Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide protection against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate
    ms189067.Caution(en-us,SQL.100).gifCaution:
    SSL connections that are encrypted by using a self-signed certificate do not provide strong security. They are susceptible to man-in-the-middle attacks. You should not rely on SSL using self-signed certificates in a production environment or on servers that are connected to the Internet.
Certificate Requirements

For SQL Server to load a SSL certificate, the certificate must meet the following conditions:

  • The certificate must be in either the local computer certificate store or the current user certificate store.
  • The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  • The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
  • The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).
  • The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.
Encryption on a Cluster

If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes. To configure the failover cluster for encryption, you can then select the ForceEncryption check box on the Protocols for <server> property box of SQL Server Network Configuration.
Internet Protocol Security (IPSec)

SQL Server data can be encrypted during transmission by using IPSec. IPSec is provided by the client and server operating systems and requires no SQL Server configuration. For information about IPSec, see your Windows or networking documentation.
See Also

Tasks

How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

A.Lockwood
Web Server Certificate Will Work
If you're not sure what sort of a certificate to request a "Web Server" certificate will get you what you need. Just be sure to set the name to the FQDN like it says and if you're running a cluster be sure to use the virtual/cluster FQDN. If you happen to be running a SQL 2005 cluster with a SQL Server service account that is a domain account but isn't a local admin you will need to take additional steps before SQL will be able to use the certificate. These steps include updating the Registry and granting the SQL Server service account permissions to decrypt the certificate private key (Read about WinHttpCertCfg here: http://msdn.microsoft.com/en-us/library/aa384088.aspx).

Jason H - SQL
Self Signed Certificate will be used unless one with matching FQDN is detected in certificate store

The SQL Errorlog tells of whether a self-signed certificate is being used or a certificate from the certificate store is being used.

  • If the self-signed certificate is being used, the errorlog will have this message near startup time:
    Message 26018: "A self-generated certificate was successfully loaded for encryption."

  • If an SSL certificate from the certificate store is being used, the errorlog will have this message near startup time:
    Message 26013: "The certificate was successfully loaded for encryption."
  • I wish the errorlog told you which SSL Cert its using, so we filed a design-change request to ask for that in future releases of SQL Server. You may be able to run the Process Monitor tool (Microsoft/Sysinternals) to see which one is being loaded.

    Other hints of failure may be visible:
  • If you specify an SSL certificate but it fails to load, the errorlog will display
Message 26014: "Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online."


  • Similarly, if you specify an SSL certificate but it fails to load, and you have required ForceEncryption=TRUE, then the errorlog will display
Message 26015: "Unable to load user-specified certificate. Because connection encryption is required, the server will not be able to accept any connections. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online."
  • If both the SSL certificate fails, and the self-signed certificate fails, the errorlog will display
Message 26017: "Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate."

===============

As described above, in "Configuring SSL for SQL Server", If you want to provide a certificate for the SQL server instance to use, you can list which certificates are eligable via the Configuration Manager in the start menu under the SQL program group. Click the SQL Server (2005/2008) Network Configuration node in the tree, and expand your instance. Right click > Properties to list if "Force Encryption" is No or Yes. On the Certificate tab, there is a list box that includes only the eligable certificates from the certificate store.

SQL uses the SQL WMI provider to check certificates in two stores:

  1. “Local Machine” My certificate store,
  2. "User" My certificate store.

If the SSL certificate you have installed into the certificate store does not match the exact list in the above section "Certificate Requirements" then it will not be displayed in the drop-down list within Configuration Manager.There is one more gotcha we found - even if your configuration manager doesn't list a certificate being used, or you had one picked and cleared that existing SSL certificate from the configuration manager list with the "Clear" button, SQL Server can still be using an SSL certificate, instead of the self-signed cert. Check the errorlog for either of the above informational messages to determine which one is being used. The reason is, If you used to have a certificate in either the Local machine or User's My Store, SQL Server may still pick it up from the certificate store and utilize that certificate to encrypt with if the machine name (fullly qualified domain name) matches the subject in that certificate.

If you decide you don't want the SSL certificate to be chosen any more, and want to go back to self-signed certificate, then you can remove it from the list by Clearing the certificate list in Configuration Manager, and also you have to remove the certificate from its respective cert store so SQL will stop picking it up.

You can get to the Certificate Stores, by using mmc.exe (start > run) and adding in the Snap-in for Certificates. The first choice presented is "My user account", "Service Account", "Compute Account". From what I can tell, SQL checks the nodes under the "My user account" store in the Personal node, and the nodes under the "Computer Account" store.

To add a cert to the store, Right click and select import and browse to the certficate you want to import.

The certificate is encoded as a binary large object and stored as a binary value in the following file location:
%Userprofile%\Application Data\Microsoft\SystemCertificates\My\Certificates (this maps to the My user Account store)

SQL has to be able to access that folder to be able to find and load the certificate. The SQL Service startup account has to have permissions to the App Data folder of its own profile. AppData folders can be customized which can lead to problems figuring out why a certificate fails to load should a failure occur.

=============
For advanced situations, Microsoft has certain SNI BID tracing on the server they can enable to get the Windows error condition being returned from a few specific failures if you should encounter problems when SQL is picking the certificate or generating the self-signed certificate.

Also in some situations, the ring buffer DMV sys.dm_os_ring_buffers may offer some error feedback about which OS error was detected when failures occur related to non-sql APIs happened during loading of SSL Certificates or self-signed certificates.

select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'



Don't expect user-friendly results. I got 0xc000003A which I looked up and found out to be STATUS_OBJECT_PATH_NOT_FOUND = 0xC000003A = {Path Not Found} The
path %hs does not exist. The root cause of that problem leading to the HResult was that the AppData environment variable pointer to the folder was pointing to an invalid folder.

=============
You can test if the certificate is eligable for SQL Server usage by querying the WMI provider directly outside of Configuration Manager. This WMI query should return the same list that's in pull-down box in Configuration Manager certificates listing.

If the SSL Certificate doesn't show up in the output of this script from the WMI provider directly, then probably something is not matching the criteria explained in the "Certificate Requirements" text in the MSDN article above.

To query the WMI provider, one way is to a make a text file named TEST_SQL_CERT.vbs and put VB script code in the file. Substitue the computer name in instead of the period in the first line if necessary to query remote SQL Server WMI providers.

strComputer = "." 
Set objWMIService = GetObject("winmgmts:\\" & strComputer & 
"\root\Microsoft\SqlServer\ComputerManagement") 
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM SecurityCertificate",,48) 
For Each objItem in colItems 
Wscript.Echo "-----------------------------------"
Wscript.Echo "SecurityCertificate instance"
Wscript.Echo "-----------------------------------"
Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
Wscript.Echo "FriendlyName: " & objItem.FriendlyName
Wscript.Echo "IssuedBy: " & objItem.IssuedBy
Wscript.Echo "IssuedTo: " & objItem.IssuedTo
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "SHA: " & objItem.SHA
Wscript.Echo "StartDate: " & objItem.StartDate
Wscript.Echo "SystemStore: " & objItem.SystemStore
Next


=============
There is another handy tool called CertUtil that allows you to open the cert store from the command line and print out the details of a certificate to text for review of the criteria required for SQL to use it.

http://www.microsoft.com/downloads/details.aspx?FamilyID=C16AE515-C8F4-47EF-A1E4-A8D
CBACFF8E3&displaylang=en

You can run it like this:
Certutil -store -v my <CertificateSerialNumber>

For the command line, I got the <CertificateSerialNumber> from the following Registry key (for the first instance MSSQL.1)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib
- registry value Certificate

The other important registry value under that key is the ForceEncryption flag. That's registry key is how the SQL Configuration Manager tells the SQL Server engine to check for an SSL certificate when the service is starting up. The ForceEncryption flag enforce that encryption has to be enabled or refuse connections and refuse to start the Server. The Certificate value tells SQL which one to prefer when its starting up.

Even if no certificate value is present in the registry key, SQL Server will use the first one it finds in the two described Certificate Stores that matches the fully-qualified domain name.
=============


Page view tracker