SQL Server 2008 R2 Express + Wildcard SSL Certificate

Wildcard certificates (for example, *.example.com) simply did not work in SQL Server 2008 or lower. But Encrypting Connections to SQL Server on MSDN states, plain as day, that

SQL Server 2008 R2 supports wildcards certificates.

Excellent. So I set up SQL Server 2008 R2 Express on a machine, and I configure the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Certificate entry to the thumbprint of my wildcard SSL certificate (because in five years of dealing with SQL server, I have never gotten that #$@# dialog in Sql Server Configuration to display any certificates).

The SQL Server log then tells me that this went over quite well:

2010-08-31 11:46:04.04 Server The certificate [Cert Hash(sha1) "5DDD9E51B30E0CA6CE3656AE54EC6D0B8B75904A"] was successfully loaded for encryption.

Unfortunately, if I attempt to use Microsoft SQL Server Management Studio (the 2008 R2 version) or the Sql* classes provided in the .NET Framework 4.0, I always receive the following exception:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.) (Microsoft SQL Server, Error: -2146762481)

Here are things I've tried:

Making sure that the hostname is configured correctly. (For example, the hostname is prod, and the DNS suffix is correctly set: prod.example.com.)
Making sure that a PTR record for prod.example.com is correctly set up.
Setting TrustServerCertificate=Yes in the connection string.

Interestingly, if I try to connect via sqlcmd.exe, I receive no complaints about the certificate.

I am beginning to suspect that wildcard certificates in SQL server will be loaded by the server, but there is no instance of the .NET SQL client that can correctly work against one.

Can anyone shed some light on this?

Update: Some additional information about the wildcard certificate:

Yep, it's installed in Local Computer > Personal > Certificates.
It's got the Server Authentication (1.3.6.1.5.5.7.3.1) Enhanced Key Usage.
It's got Key Encipherment (a0) where (a0) means AT_KEYEXCHANGE. (It works fine for an FTP server and an IIS Web site, so if this were mucked up, I imagine it would not work there.)
The subject of the certificate is CN = *.example.com (substituting "example" for our work domain). That is, it's issued to *.example.com. This was the dealbreaker in versions prior to 2008 R2 that prevented SQL Server from loading the certificate.
It's got the private key.
The Friendly Name can be set to whatever--prod.example.com is what it's at now.

Update 2: So this will really fry your brain:

If I set up a connection via ODBC:

Microsoft SQL Server Native Client Version 10.50.1600

Data Source Name: prod.example.com
Data Source Description: prod
Server: tcp:prod.example.com,8484\SQLEXPRESS
Use Integrated Security: No
Database: (Default)
Language: (Default)
Data Encryption: Yes
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

Then I get a successful result:

Microsoft SQL Server Native Client Version 10.50.1600

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
INFO: Connection was encrypted with server certificate validation.
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

Update 3: OK, one last shot at this before I give up on the wildcard certificate. Here is a little sample program that I wrote in C#:

    static void Main(string[] args)
    {
        Console.WriteLine(new string('-', 40));

        try
        {
            var connectionString = 
                @"Data Source=tcp:prod.example.com,8484\SQLEXPRESS; " +
                "User ID=ExampleDev;Password=ExamplePass; " +
                "Encrypt=True";

            Console.WriteLine("Trying SqlConnection...");

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                Console.WriteLine("SUCCESS!");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("FAILED!");
            Console.WriteLine(e);
        }

        Console.WriteLine(new string('-', 40));

        try
        {
            var connectionString = 
                @"Driver={SQL Server Native Client 10.0}; " +
                "Server=tcp:prod.example.com,8484\SQLEXPRESS; " +
                "Uid=ExampleDev; Pwd=ExamplePass; Encrypt=yes";

            Console.WriteLine("Trying OdbcConnection...");

            using (var connection = new OdbcConnection(connectionString))
            {
                connection.Open();

                Console.WriteLine("SUCCESS!");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("FAILED!");
            Console.WriteLine(e);
        }

        Console.WriteLine(new string('-', 40));
        Console.ReadLine();
    }
}

The output of this program, after substituting usernames and passwords as is appropriate, as run on my machine, is as follows:

----------------------------------------
Trying SqlConnection...
FAILED!
System.Data.SqlClient.SqlException (0x80131904): A connection was successfully e
stablished with the server, but then an error occurred during the pre-login hand
shake. (provider: SSL Provider, error: 0 - The certificate's CN name does not ma
tch the passed value.)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception
, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt,
Boolean trustServerCert, Boolean& marsCapable)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternal
ConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Bool
ean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
   >
----------------------------------------
Trying OdbcConnection...
SUCCESS!
----------------------------------------

This is why I drink.

I know that "select usually isn't broken," but I don't know what to make of this. It seems like the SqlClient classes in the .NET Framework 4.0 are just broken when it comes to validating wildcard certificates. What else might be different between the two methods?

The fact that the certificate didn't show up in the Configuration Tool presents the first problem. If the cert is installed correctly then it should show up on that list. Where in the certificate store did you place the certificate? It should be in the Local Computer's certificate store under Personal > Certificates.

You'll also need to make sure that the server authentication certificate has the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1). The cert must also be created using the KeySpec option of AT_KEYEXCHANGE, you optionally can set the key usage property to include key encipherment.

The answer from Microsoft is that the .NET SqlClient will not work against wildcard certificates, period.

Even though SQL Server 2008 R2 Express now supports wildcard certificates, and the native providers have been updated to understand them (this means ODBC and OLEDB), the System.Data.SqlClient classes in the .NET Framework 4.0.

Essentially, this means that if you're supporting a .NET application against the database server, the wildcard certificates feature is still a no go.

SQL Server 2008 R2 Express + Wildcard SSL Certificate

Related

Recent Posts