locked
How does the SSL encryption work in SQL Server 2005 RRS feed

  • Question

  • I've being trying to implement SSL encryption following the document "Encrypting Connections to SQL Server" http://msdn.microsoft.com/en-us/library/ms189067(v=SQL.90).aspx

    The document brings me some questions about how the SSL encryption works in SQL Server 2005.

    1. The document says "An extra network roundtrip is required at connect time".

    What kind of steps are processing in the extra network roundtrip?

    For example, I'm assuming

    a. A client sends connection request to a server

    b. The server sends a certificate and public key to the client.

    c. The client creates and sends a key to the server.

     

    2. What kind of encryption key is used for SSL encryption? (Symmetric / Asymmetric ?)

    and who creates the key?(The client / the server)?

    How long does this key last? (As long as session is connected? / you need to create the key every time the data transfered? every query)

    Thanks,

    Kenji

    Tuesday, May 3, 2011 9:18 PM

Answers

  • I'd think "An extra network roundtrip is required at connect time" means the handshake process for SSL connection as general

    you can read more about TLS/SSL from wikipedia : http://en.wikipedia.org/wiki/Transport_Layer_Security

     

    A TLS client and server negotiate a stateful connection by using a handshaking procedure.<sup id="cite_ref-2" class="reference">[3]</sup> During this handshake, the client and server agree on various parameters used to establish the connection's security.

    • The handshake begins when a client connects to a TLS-enabled server requesting a secure connection and presents a list of supported CipherSuites (ciphers and hash functions).
    • From this list, the server picks the strongest cipher and hash function that it also supports and notifies the client of the decision.
    • The server sends back its identification in the form of a digital certificate. The certificate usually contains the server name, the trusted certificate authority (CA) and the server's public encryption key.
    • The client may contact the server that issued the certificate (the trusted CA as above) and confirm the validity of the certificate before proceeding.
    • In order to generate the session keys used for the secure connection, the client encrypts a random number with the server's public key and sends the result to the server. Only the server should be able to decrypt it, with its private key.
    • From the random number, both parties generate key material for encryption and decryption.

    This concludes the handshake and begins the secured connection, which is encrypted and decrypted with the key material until the connection closes.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    • Marked as answer by WeiLin Qiao Wednesday, May 11, 2011 4:45 AM
    Tuesday, May 3, 2011 10:53 PM
  • Also:

    • Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.
    • Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.

    Net-Library is handling all these. This is encrypting the connection.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    • Marked as answer by WeiLin Qiao Wednesday, May 11, 2011 4:45 AM
    Tuesday, May 3, 2011 10:56 PM

All replies

  • I'd think "An extra network roundtrip is required at connect time" means the handshake process for SSL connection as general

    you can read more about TLS/SSL from wikipedia : http://en.wikipedia.org/wiki/Transport_Layer_Security

     

    A TLS client and server negotiate a stateful connection by using a handshaking procedure.<sup id="cite_ref-2" class="reference">[3]</sup> During this handshake, the client and server agree on various parameters used to establish the connection's security.

    • The handshake begins when a client connects to a TLS-enabled server requesting a secure connection and presents a list of supported CipherSuites (ciphers and hash functions).
    • From this list, the server picks the strongest cipher and hash function that it also supports and notifies the client of the decision.
    • The server sends back its identification in the form of a digital certificate. The certificate usually contains the server name, the trusted certificate authority (CA) and the server's public encryption key.
    • The client may contact the server that issued the certificate (the trusted CA as above) and confirm the validity of the certificate before proceeding.
    • In order to generate the session keys used for the secure connection, the client encrypts a random number with the server's public key and sends the result to the server. Only the server should be able to decrypt it, with its private key.
    • From the random number, both parties generate key material for encryption and decryption.

    This concludes the handshake and begins the secured connection, which is encrypted and decrypted with the key material until the connection closes.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    • Marked as answer by WeiLin Qiao Wednesday, May 11, 2011 4:45 AM
    Tuesday, May 3, 2011 10:53 PM
  • Also:

    • Packets sent from the application to the instance of SQL Server must be encrypted by the client Net-Library and decrypted by the server Net-Library.
    • Packets sent from the instance of SQL Server to the application must be encrypted by the server Net-Library and decrypted by the client Net-Library.

    Net-Library is handling all these. This is encrypting the connection.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    • Marked as answer by WeiLin Qiao Wednesday, May 11, 2011 4:45 AM
    Tuesday, May 3, 2011 10:56 PM