Secure communication between linked SQL Servers

Question

Is the data transferred between two SQL Servers protected (encrypted) by default? If not, is there a way to accomplish this?

I have two SQL Server 2005 databases running on separate servers, separate machines, separate networks. How can I ensure that data transmitted from one server to another is secure? I have tried researching the subject but am unable to find anything.

Many thanks, Sebastian

Encrypt Via SQL Server

1. Provision a certificate on both machines.

2. Configure any non-server clients to trust the certificate's root signing authority. See How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console.

3. Configure the server(s) to force all incoming connections to use SSL so that any clients that do not support this will fail to connect. In SQL Server Configuration Manager, set the ForceEncryption parameter to "Yes" in the Protocols section.

4. OR, instead of the prior step, you can add Encrypted=yes to the provider/connection string for the connection/linked server. If, for example, you register the linked server using sp_addlinkedserver it might look something like this:

   EXEC master.dbo.sp_addlinkedserver
      @server = N'LinkedServerName',
      @srvproduct = N'',
      @provider = N'SQLNCLI',
      @datasrc = N'Server\InstanceName',
      @provstr = N'Encrypt=yes;',
      @catalog = 'DatabaseName'
   ;
   

   I do NOT recommend that you use the option TrustServerCertificate=True because this will disable the client from validating the identity of the server it is connected to.

   Also, if using ODBC, the encryption property can be specified in a DSN.

Note that while servers don't require certificates to be installed since you can configure them to automatically create and sign their own certificates, it is best to manually install them, because you can run into problems with the self-created certificates since they change on each restart.

The best security is when the client specifically requests channel encryption, because this not only encrypts the data but the client also attempts to validate the identity of the server via the certificate, helping to mitigate a man-in-the-middle attack.

Encrypt Via The Network

Another option is to set up a secure tunnel (such as a VPN) between the two servers' networks, and ensure that the routing for traffic between them is entirely through said tunnel. This is 100% secure as well, as long as you are sure the traffic goes over the right route.

Comments

[bilmuhfk]

Thanks for the great answer. But @provstr = N'Encrypted=yes;' is incorrect. It should be @provstr = N'Encrypt=yes;'

[Rask]

Many thanks for really good answer. Just a headsup to anyone who needs to set TrustServerCertificate that it may need to be added to the provider string as TrustServerCertificate=yes. (That was at least the case in my situation.)