Secure communication between linked SQL Servers

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.

I realize this is a very old thread, but there's something in here that I just found out is potentially misleading.

"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."

The ForceEncryption parameter on the SQL server side does not fail clients that do not have Encrypt = True set on the client end. Importantly, you can create insecure connections to the SQL server instance even when ForceEncryption is set to "Yes" on the server.

You can validate this by setting ForceEncryption = Yes on the server side, then by connecting to that SQL server using SSMS and choosing not to encrypt the connection on the client side.

You can check the encryption state of the client connection by visiting the Properties of the server in Object Explorer, then clicking on View Connection Properties (right click on the servername in Object Explorer, then click Properties).

You can then see that the value for the Encypted property is set to No.

You can then do a select or other SQL statement to confirm that you are able to access data from the server side, without encryption. I believe that in order to have full end-to-end encryption, you must set encryption on both the client and the server side.

Here is a Microsoft link explaining this issue.

And a screenshot in case that link doesn't work.

And another Microsoft link saying the same thing.

And another screenshot in case that link doesn't work.