Monthly Archives: March 2013

SQL Server Connection Encryption and .Net Framework 4.5

SQL Server has offered the ability to encrypt network connections for quite a while. This can be achieved using the self-generated certificate that SQL Server generates when installed, or using a certificate generated outside SQL Server (Your own Enterprise Certificate Authority in your Active Directory or a third-party Certificate Authority). These certificates, once registered to the server, can be loaded inside SQL Server and used to open up SSL encrypted connections between clients and the server.   In general you would use encryption as an additional layer of security when opening up a database server to the world (e.g. an internet facing DB-Server). However, there are cases when the network connection between a client and the server are required to be encrypted inside the confines of a corporate network.   SQL Server will check for the availability of a server certificate at start-up. If it finds a certificate issued by a certificate authority it will load and use it for encryption purposes. If, however, no certificate can be found, then a self-generated certificate will be used.   You can find out if you are using a self-generated certificate or a third-party certificate by reading the SQL Server error log and looking through the start-up messages at the beginning of the current log-file. A message about the encryption certificate is written after the initial information about start-up parameters and initialisation of the system DBs: A self-generated certificate generates the following log entry:

.

A third-party generated certificate on the other hand:

My Issue: I stumbled across an edge-case (for me at least) when working with encrypted connections to SQL Server using the .Net Framework v. 4.5 recently that I wrestled with for a few days before coming to a conclusion and thought it at least noteworthy.   As of v. 4.5 of the .Net Framework the authentication of a certificate is checked more thoroughly than in previous versions. As stated in the MSDN Library (http://msdn.microsoft.com/en-us/library/ex6y04yf.aspx), when the connection property “Encrypt” is set to true and the property “TrustServerCertificate” is set to false (default value), the server name (or Virtual Name for Clustered Instances) in the connection string must *exactly* match the common name (CN) specified in the certificate. If this is not the case, then the connection attempt will fail:   As you can see above, the principal name did not match so no connection could be established. This applies to the following scenarios:

  1. Not using fully qualified domain names in the connection string (FQDN):
    • You have a company domain (mydomain.com) You have a SQL Server (server1), FQDN = server1.mydomain.com
    • You have an SSL certificate generated for the FQDN (server1.mydomain.com) which is registered and loaded by SQL Server at startup
    • Your DB connections only use the server name, not the FQDN: “Server=server1; Initial Catalog=Northwind; Integrated Security=true; Encrypt=True”

In previous versions of the .Net Framework the connection would be opened and the servername “server1” would still be accepted when compared to the servername on the certificate, if the name was the same and the domain was not included, then the connection would still be accepted. As of v. 4.5 this certificate verification is basically doing what it should – identifying the name provided against the name in the certificate as strictly as expected.

  1. Using a DNS Alias to identify and connect to the SQL Server:
    • You have a company domain (mydomain.com) You have a SQL Server (server1), FQDN = server1.mydomain.com
    • You have an SSL certificate generated for the FQDN (server1. mydomain.com) which is registered and loaded by SQL Server at startup
    • You have a DNS Alias (mssql) created as an abstraction layer to your physical SQL Server (allows for easier infrastructure changes).
    • Your DB connections use the Alias name, not the Servername or FQDN: “Server=mssql; Initial Catalog=Northwind; Integrated Security=true; Encrypt=True”

As in scenario 1, previous .Net Framework versions were more than happy to honour the alias redirect to the actual server and accept encrypted connections   The same error occurs as in scenario 1 when trying to connect using .Net Framework 4.5, the reasons are the same – the alias name is not registered in the standard certificate so cannot be strictly identified with the name in the certificate. The on-going theme here is that name resolution between the connection string and the server name in the certificate has become much stricter and also made alias usage (scenario 2) impossible with a standard certificate requested through the certificate authority.   The solution is simple enough – either change your software to use the fully qualified name of the server (as is present in the certificate itself), or request a certificate that incorporates all possible “versions” of the server name and/or alias that you wish to use. These additional names are stored as part of the certificate in a portion of the certificate called the “Subject Alternative Name”.   As I want to continue to use aliases to keep our infrastructure “invisible” to any deployed applications, I went for door number two and created a certificate request that incorporates all name variations and aliases for the system that I plan on using. Solution:   Here are the steps for creating a certificate request within your domain that will fulfill the requirements by using the Subject Alternative Name field to store and use the alias when connecting to the SQL Server using SSL encryption (done on the server you are wanting to set up SSL on). This is an example using a certificate authority within a domain, the workflow may be different when using an external authority (I have never used them for this work before).   I have included a video showing how to create the custom certificate request using the Microsoft Management Console (mmc) and the certificate snap-in. The steps in the video should be simple to follow and will create an offline certificate request .txt file that will contain the certificate request information required at the certificate authority.   There are a few things to note from the video:

  • The friendly name and description are purely for reference.
  • The information entered is there to make your life easier when trying to identify all the different certificates that are installed on the machine.
  • The tab “Subject” is where the actual server information for the certificate will be entered.
  • The “Subject Name” needs to be the Fully Qualified Domain Name (FQDN) of the server you are creating this certificate for entered as the Common Name (CNAME) type.
  • The “Alternative Name” section is where all the alias names will be entered that you want to use to access the server. In this example I have entered a fully qualified alias name and also the short version (I am not sure if this is really necessary, I’m just hedging my bets with regard to the .Net 4.5 issues I was experiencing).

Notice that you can enter as many different aliases here as you feel like – This is the magic to allow you to offer up different aliases to your “customers” so they don’t necessarily know that they are all on one server. You could even use a wildcard here if you are happy for the certificate to be used by *any* server – I’m not too keen on this as *any* server can use the certificate then, so you lose a little control over the certificate usage (maybe this isn’t really an issue, but I prefer to assign single certificates instead). Once you have the request in a text file you then connect to the certificate authority site and send the contents of the text file to the authority system. The response is a certificate that is valid within your domain for the server named in the subject and also for the alternatives listed in the alternatives list. This process is listed below:   The text file created in the video should look something like this: First of all you need to call up the certificate authority site (ask your domain administrator team for the address). You will be confronted with a rather snazzy Microsoft website for Active Directory Certificate Services:

Your choice will be to request a certificate. The next page should offer two possibilities:

  1. “Create and submit a certificate request”
  2. “Submit a certificate request using a bas-64-encoded CMC or PKCS #10 file, or submit a renewal request by using a base64-encoded PKCS #7 file.”

Quite a mouthful (and written verbatim here for Google searches) but we want to take the second option as we have already created the certificate request and only want to submit it. You now open your previously created text file and copy the contents into the corresponding text box on the webpage:

There is a good possibility that your domain/certificate admins have created a template to include all other necessary settings (e.g. certificate expiry). These templates are offered up in the “Certificate Template” dropdown. Once this information has been entered you can submit the request. Assuming that the information is created correctly, your request will be successfully processed and a certificate will be available for download.   The certificate can be downloaded to the SQL server and “installed”. This is done by importing the certificate into the local computer certificate store (the MMC snap-in loaded at the beginning of this blog post). To import you need to navigate to personal certificate store, right-click and choose “Import”. A new wizard will prompt for the certificate file and then import this into the local certificate store. You will now have a new entry in the personal certificate folder with the friendly name used in the certificate request. As stated at the beginning of this blog post: At start-up, SQL Server will automatically look in the local machine certificate store to see if a valid SSL certificate is available. The certificate will be automatically loaded if the certificate subject exactly matches the server name (or the virtual name for a clustered server). You will be able to see this in the SQL Server log in the start-up messages:

You now have a SQL Server that can accept SSL encrypted connections, even if you use an alias. You can also then decide whether you want to force encryption of all SQL Server traffic, which is done through the SQL Server Configuration Manager under “SQL Server Network Configuration” and the properties of the Protocols:

This is also where you can see which certificate SQL Server will use for SSL encryption:

So that was how I solved my problem of wanting to use SSL with server aliases and .Net 4.5 on a SQL Server. I hope it could help you if you ever need to do the same in the future.

As always, comments/suggestions/improvements/corrections are more than welcome, just get in touch!