locked
database mirror, s with are my connection encrypted?? RRS feed

  • Question

  • Hi everybody, i have been doing some messing around with database mirror, and now i reach to the securty issue, i have created my endpoint with FOR DATABASE_MIRRORING( ENCRYPTION = Required  ALGORITHM AES RC4

    wich sould make my connection encrypted with AES algorithm , i have been poking around and i reach to a inconclusive dead end, wich is if i query this system view sys.dm_db_mirroring_connections, the following collums apear ok

    encryption_algorithm encryption_algorithm_desc
    3                       AES

    But if i query SYS.DM_EXEC_CONNECTIONS about those 2 connections appears encrypt option false, despite i'm encrypt all my sql server connection via certificate , and all my coonnections apear true excep those.

     

    thanx in advance , and happy new year

    Friday, December 31, 2010 3:02 PM

Answers

  • Hi Ricardo,

    The database mirroring endpoint encryption and SQL Server connection encryption via SSL are two different encryption layers. Endpoint encryption will encrypt data that is sent over mirroring connections using Transmission Control Protocol (TCP); however, SQL Server SSL encryption is used to encrypt communications between an instance of SQL Server and a client application over the internet network.

    So, if you use both encryptions, the data that is send over database mirroring connections is encrypted by the encryption algorithms specified in the CREATE ENDPOINT or ALTER ENDPOINT statement and then connections between two database mirroring endpoint are encrypted by SSL.

    For more information, please refer to Data Encryption in Database Mirroring Transport Security (http://technet.microsoft.com/en-us/library/ms186360.aspx), and Encrypting Connections to SQL Server (http://technet.microsoft.com/en-us/library/ms189067.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, January 3, 2011 6:59 AM

All replies

  • Hi Ricardo,

    The database mirroring endpoint encryption and SQL Server connection encryption via SSL are two different encryption layers. Endpoint encryption will encrypt data that is sent over mirroring connections using Transmission Control Protocol (TCP); however, SQL Server SSL encryption is used to encrypt communications between an instance of SQL Server and a client application over the internet network.

    So, if you use both encryptions, the data that is send over database mirroring connections is encrypted by the encryption algorithms specified in the CREATE ENDPOINT or ALTER ENDPOINT statement and then connections between two database mirroring endpoint are encrypted by SSL.

    For more information, please refer to Data Encryption in Database Mirroring Transport Security (http://technet.microsoft.com/en-us/library/ms186360.aspx), and Encrypting Connections to SQL Server (http://technet.microsoft.com/en-us/library/ms189067.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, January 3, 2011 6:59 AM
  • Hi Ricardo,

    As far as I know the data transfer in a database mirroring session is always encrypted by default and it either uses the RC4 or AES ( RC4 being the default). The catalog view sys.database_mirroring_endpoints and sys.tcp_endpoints always denotes this mirroring connection is encrypted and as per my understanding the reason behind the encrypt_option = false in sys.dm_exec_connections might be the fact that these SPID are system generated(less than 50). Probably you can work with your network admin to prove this connection is encrypted and leave out the info from sys.dm_exec_connections.


    Thanks, Leks

    Monday, January 3, 2011 7:27 AM
    Answerer