locked
Do you see any flaws in this approach to securing the client to sql server data connection? RRS feed

  • Question

  • 1. Install an x509 certificate on the sql server

    2. Under  "SQL Server Configuration Manager", right click "Protocols for MSSQLSERVER" > Properties > select Certificate tab, then select the installed certificate.

    I believe this setup allows a client to request an encrypted connection with the sql server, if desired. Do you agree?

    TIA,

    Barkingdog

    Wednesday, December 30, 2009 8:08 AM

Answers

  • Everything looks fine . Make sure the application can read encrypted data , else application will throw an access denied exception.
    Apart from that -- After point 2 , i would ask you to set the proper value for flag FORCE ENCRYPTION

    When the ForceEncryption option for the Database Engine is set to Yes , all client/server communication is encrypted and clients that cannot support encryption are denied access.

    When the ForceEncryption option for the Database Engine is set to No , encryption can be requested by the client application but is not required.

    Thanks, Leks
    Thursday, December 31, 2009 6:08 PM
  • Hi,

    The client should be able to handle the encryption. If anyone would decrypt the data sooner, that would be a security risk.

    SQL Server uses SSL, strength dpends on capabilities. See the BOL for more details. It's like with the web: if your browser can't handle SSL, then you can't visit https sites. Most of the new clients know this - it's not Microsoft invention. Alternatively, you can use IPSec in case your client can't handle encrypted connections. That should be set up on the OS level and completely transparent for the processes on the hosts (that is, any process can use it).

    -- Erik -- http://blog.rollback.hu
    Saturday, January 2, 2010 9:55 PM
  • Hi,

    Try the Win 2003 guide - it should work in a very similar way (at least it does on Vista). Check this: http://support.microsoft.com/kb/816514.

    -- Erik -- http://blog.rollback.hu
    Thursday, January 7, 2010 3:59 PM

All replies

  • Everything looks fine . Make sure the application can read encrypted data , else application will throw an access denied exception.
    Apart from that -- After point 2 , i would ask you to set the proper value for flag FORCE ENCRYPTION

    When the ForceEncryption option for the Database Engine is set to Yes , all client/server communication is encrypted and clients that cannot support encryption are denied access.

    When the ForceEncryption option for the Database Engine is set to No , encryption can be requested by the client application but is not required.

    Thanks, Leks
    Thursday, December 31, 2009 6:08 PM
  • Lekss,

    You wrote

    >>> Everything looks fine . Make sure the application can read encrypted data , else application will throw an access denied exception.
    Apart from that -- After point 2 , i would ask you to set the proper value for flag FORCE ENCRYPTION


    I never thought of that. I tested the encryption with Sql Server and Management Studio (on a separate workstation) and all worked well. I presumed that the data was encrypted (by the network libraries) before being sent across the wire and was decrpyted on the client side (network libraries?) before the client actually read it.  In other words, the client itself did not need to know how to encrypt\decrpyt  the data (only how to request data in that format.)

    If the client needs does need to read encrypted data, that limits the number of clients that can use this secured approach.  For example, how, off hand, is a third party client going to know what encryption approach sql server used (DES? AES?, AES 256?,etc.)

    Barkingdog


    Thursday, December 31, 2009 9:17 PM
  • Hi,

    The client should be able to handle the encryption. If anyone would decrypt the data sooner, that would be a security risk.

    SQL Server uses SSL, strength dpends on capabilities. See the BOL for more details. It's like with the web: if your browser can't handle SSL, then you can't visit https sites. Most of the new clients know this - it's not Microsoft invention. Alternatively, you can use IPSec in case your client can't handle encrypted connections. That should be set up on the OS level and completely transparent for the processes on the hosts (that is, any process can use it).

    -- Erik -- http://blog.rollback.hu
    Saturday, January 2, 2010 9:55 PM
  • Erik,

    Do you know of any step-by-step guides to setting up IPSec in a Windows 2008 environment?  I found a few documents on the subject but they were largely unintelligible.

    TIA,


    Bakringdog
    Sunday, January 3, 2010 4:26 AM
  • Hi,

    Try the Win 2003 guide - it should work in a very similar way (at least it does on Vista). Check this: http://support.microsoft.com/kb/816514.

    -- Erik -- http://blog.rollback.hu
    Thursday, January 7, 2010 3:59 PM