none
SQL Server 2008 R2 Express + Wildcard Certificates RRS feed

  • Question

  • Note: Cross-posted from my question at ServerFault: http://serverfault.com/questions/176595/sql-server-2008-r2-express-wildcard-ssl-certificate

    (Not trying to be a jerk, just think I might get a better response here.)

    ---

    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?

    Tuesday, August 31, 2010 4:58 PM

Answers

  • Sorry for any confusion from the documentation here, Nicholas. Your initial suspicion, "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", is absolutely correct. Support for wildcard certificates is a new client feature in the SQL Server 2008 R2 Native Client, and currently only the SQL Server 2008 R2 Native Client's ODBC driver and OLEDB provider support them, which is why sqlcmd works for you. We are considering adding support for wildcard certificates to a future version of .NET SqlClient as well, but we don't yet have a target release for that feature.


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, September 2, 2010 10:22 PM
    Moderator

All replies

  • Upon further reflection, I can only surmise that this is a bug in the .NET Sql Client, unless I am missing something obvious. Consider this program:

     

      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)
      // snipped some lines for brevity
    ----------------------------------------
    Trying OdbcConnection...
    SUCCESS!
    ----------------------------------------
    
    
    Why would OdbcConnection work but SqlConnection fail with equivalent connection strings, unless there is a bug in SqlConnection or something I am misunderstanding?

     

    Tuesday, August 31, 2010 9:38 PM
  • Nichaolas,

    This looks a product issue. I recommend that you submit a feedback to our product team at https://connect.microsoft.com/sql so that this issue can be addressed in future.

    In addition, what is the result if you specify the name in the "server=" parameter same as the CN in your certificate? You may try removing "tcp:" and the port number. To make sure that you can connect to it, please start SQL Server Browser services at your server side. 

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, September 2, 2010 9:47 AM
    Moderator
  • The CN is a wildcard ("*.example.com") so that results in a connection error since that address can't be resolved.

    In the end, I gave up, self-signed my own cert, and distributed it as a Trusted Root through the Active Directory, which is good enough for me.

    I'll see if I can figure out Connect. I do agree that it seems to be a problem with SNI resolution in SqlClient.

    Thanks for the response!

    Thursday, September 2, 2010 11:04 AM
  • Update: I posted this to Microsoft Connect underneath the .NET Framework (since it seems to mostly be their problem, I think):

     

    https://connect.microsoft.com/VisualStudio/feedback/details/593800/system-data-sqlclient-does-not-support-wildcard-certificates-with-sql-server-2008-r2-express

    Thursday, September 2, 2010 12:35 PM
  • Sorry for any confusion from the documentation here, Nicholas. Your initial suspicion, "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", is absolutely correct. Support for wildcard certificates is a new client feature in the SQL Server 2008 R2 Native Client, and currently only the SQL Server 2008 R2 Native Client's ODBC driver and OLEDB provider support them, which is why sqlcmd works for you. We are considering adding support for wildcard certificates to a future version of .NET SqlClient as well, but we don't yet have a target release for that feature.


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, September 2, 2010 10:22 PM
    Moderator
  • Thanks for the response!

    I admit that I am slightly incredulous as this means that even the bundled SQL Server Management Studio won't work against such a configured server, since it's using the .NET Framework underneath, and I would submit that the documentation needs to be updated ASAP--it's nice that others can use it, but leaving the .NET Framework out is a pretty big asterisk to put on a feature! =)

    Thursday, September 2, 2010 10:31 PM
  • I missed one detail previously which a colleague brought to my attention. You mentioned that you tried setting "TrustServerCertificate=True" in the connection string - that should allow the connection to succeed, regardless of the content of the certificate's CN (i.e., even a wildcard would be ok). Are you sure you tried setting that option to True? And, if you did indeed still see a failure in that case, was the SqlClient exception message the same?
    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, September 2, 2010 10:46 PM
    Moderator
  • Hi Dan,

    Yes, setting "TrustServerCertificate=True" makes it work for SqlClient as in the sample program I provided above, but does defeat the purpose of the certificate in the first place =) (Edit: Initially I thought it didn't work for SqlClient, and it didn't, but that was because I was a fool and specified =Yes instead of =True. I can't keep it straight some days.)

    However, SQL Server Management Studio still fails with the same exception even if you specify this in the "Additional Parameters" dialog. My guess? SSMS only uses that parameter for the initial login, and then forgets it on subsequent requests (or, perhaps, SMO blows up).

    That is, in SSMS,

    - Without "TrustServerCertificate=True" in the "Additional Parameters" tab, you get the exception immediately in an ExceptionMessageBox upon hitting "Connect" and the login dialog is still visible.

    - With "TrustServerCertificate=True" in the "Additional Parameters" tab, the login dialog closes, then you get the exception in an ExceptionMessageBox, and after dismissing it you are left with an empty SSMS workspace. (This is what leaves me to believe SMO or some other abstraction it is using is reconnecting and losing the param.)

    Thanks again for listening. It really is important to small biz developers to know that the people who need to hear things hear it!

    Thursday, September 2, 2010 11:10 PM