locked
SqlServer self-signed certificate private key access RRS feed

  • Question

  • When SqlServer 2005 is unable to find an appropriate installed certificate (and private key) for use in the SSL/TLS encryption of the Login packet, it automatically generates a self-signed certificate for this purpose.

    Can an application (with necessary privileges) directly acess this self-signed certificate, and the corresponding private key store?

     

    I fully understand why this might not be desirable in general security practice, but I want to be able to monitor and at need decrypt, SqlServer sessions.  I also understand that one can create and install a self-signed certificate which SqlServer can use for this purpose, but I prefer not to.

     

     

     

    Wednesday, October 15, 2008 10:02 PM

Answers

  •    As you suspected, this is not a supported scenario and SQL Server does not provide any interface to access the self-signed certificate.

     

       By default the self signed certificate is only used to protect the logon-related packets in order to prevent passive man in the middle attacks (i.e. sniffing the passwords); you should be able to access a lot of the information about connection attempts and established connections using other supported mechanisms such as SQL profiler and DMVs.

     

      Please let us know if there is a particular scenario where the supported mechanisms won’t help you to solve.

     

      Thanks,

      -Raul Garcia

       SDE/T

       SQL Server Engine

     

    Thursday, October 16, 2008 3:56 PM

All replies

  • Moving to security.

    Thanks,

    John
    Thursday, October 16, 2008 4:38 AM
  •    As you suspected, this is not a supported scenario and SQL Server does not provide any interface to access the self-signed certificate.

     

       By default the self signed certificate is only used to protect the logon-related packets in order to prevent passive man in the middle attacks (i.e. sniffing the passwords); you should be able to access a lot of the information about connection attempts and established connections using other supported mechanisms such as SQL profiler and DMVs.

     

      Please let us know if there is a particular scenario where the supported mechanisms won’t help you to solve.

     

      Thanks,

      -Raul Garcia

       SDE/T

       SQL Server Engine

     

    Thursday, October 16, 2008 3:56 PM
  • Thank you for the clarification.  One infers that the self-signed certificate (and its associated private key) are only kept in memory, is that correct?

    One thing bothers me about your statement:
    "
    the self signed certificate is only used to protect the logon-related packets in order to prevent passive man in the middle attacks"

    I guess I misunderstood the disadvantage of a self-signed certificate: I had thought that there would be no chain of trust you can follow to some root authority to verify that the server supplying the certificate is "who it says it is".
    This would seem to imply that a badguy could create a man-in-the-middle, using his own self-signed certificate for the SSL session between the client and the badguy, and the SqlServer certificate for the session between the badguy and trhe SqlServer.


    Thursday, October 16, 2008 10:22 PM
  •   You are absolutely correct. Self signed  certificates lack a cerification authority, therefore they cannot be used to authenticate the server; this is not only true for SQL Server, but for any SSL communication using a self-signed certificate.

     

      The protection given by using a self-signed certificate is limited and documented in BOL, but it is still preferable than having the logon packets in plaintext.

     

      Below I am including a few links that may be useful:

    * SQL Protocols blog: http://blogs.msdn.com/sql_protocols/

    * Encrypting Connections to SQL Server: http://msdn.microsoft.com/en-us/library/ms189067.aspx

     

      I hope this information helps,

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Thursday, October 16, 2008 11:24 PM
  •  frostbackeng wrote:

    Can an application (with necessary privileges) directly acess this self-signed certificate, and the corresponding private key store?

    In order to pass a certificate to SSPI's SChannel an application must call AcquireCredentialsHandle passing in a SCHANNEL_CRED structure with a valid CERT_CONTEXT and valid handle to a cert store. By definition, another application with 'suficient priviledges' will be able to read the same store and get access to the same certificate and private key.

    Monday, October 20, 2008 6:38 AM
  • Unfortunately, as Raul Garcia (above) appears to indicate, SqlServer does not make the self-signed certificate (or its corresponding private key) available.

    I want to be able to associate an otherwise plain-text network session with a user name.  Unfortunately the latter (and other relevant information) is carried in an SSL-encrypted login packet.
    The DMVs do not appear to help for non-TCP sessions (ex: a named-pipe session) unless you can tell me how to relate the GUID in the SQLServer session table to a TCP socket pair and PIB/MID.


    Monday, October 20, 2008 5:05 PM