5

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?

3
  • (Also, I'm not sure if this should be on StackOverflow or not. It seems to bridge both problem domains.) Aug 31, 2010 at 16:44
  • I think this is ok for SF or SO, so we'll leave it here for now.
    – mrdenny
    Aug 31, 2010 at 18:26
  • "This is why I drink" - oh, haven't we all been there at some point
    – pauska
    Sep 2, 2010 at 22:32

2 Answers 2

3

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.

6
  • Thanks for the reply. I've updated the question with the additional information. To be honest, I've never seen anything appear in that dropdown, even on servers that are successfully using third-party SSL certs. Thanks again! Aug 31, 2010 at 19:18
  • When you tested it via sqlcmd did you verify that the session was encrypted, via wireshark or anything?
    – mrdenny
    Aug 31, 2010 at 19:40
  • Good call ... for sqlcmd.exe, if I leave off the -N parameter ("Encrypt connection", then in Microsoft Network Monitor 3.4 I can see my query passing through in cleartext on frames with a description of "TCP Continued Payload". When I add -N, I see gibberish and "SSL Application Data", which tells me that it's encrypted. I did further experimenting and discovered that I could get the dropdown to show a certificate if its CN exactly matched the server; it would seem that this dialog has perhaps not been updated for support wildcard certificates. I wonder if sqlcmd is using a different library. Aug 31, 2010 at 20:51
  • It should be using the SQL 2008 R2 native client (or what ever version is installed on the client computer). So when you tried both providers only the Native Client worked. I think that only the Native client has been updated. ODBC is old school, OLEDB (SQL Native Client) should be used for new work.
    – mrdenny
    Aug 31, 2010 at 23:53
  • Gave you the answer for A for effort; the answer from Microsoft is that it is supported in the native client, but .NET clients (even Management Studio!) don't support it. Sep 2, 2010 at 22:28
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .