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 do this?

I have two SQL Server 2005 databases running on separate servers, separate machines, separate networks. How can I ensure the security of data transferred from one server to another? I tried to research the subject, but could not find anything.

Thanks a lot, Sebastian

Answer 1

Encryption through SQL Server

• Providing a certificate on both machines.

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

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

• OR instead of the previous step, you can add Encrypted=yes to the provider / connection string for the connected / connected server. If, for example, you registered a 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 using the TrustServerCertificate=True parameter because this will disconnect the client from the authentication of the server to which it is connected.

  In addition, when using ODBC, the encryption property can be specified in the DSN.

Please note that although the servers do not require the installation of certificates, since you can configure them to automatically create and sign your own certificates , it is best to manually install them, as you may encounter problems with self-prepared certificates, as they change every time you restart.

The best security is when the client specifically requests channel encryption, because it not only encrypts the data, but the client also tries to verify the authenticity of the server through a certificate, helping to mitigate the man-in-the-middle attack.

Network Encryption

Another option is to configure a secure tunnel (for example, a VPN) between the networks of two servers and ensure that the routing of traffic between them is completely through the specified tunnel. It is also 100% safe if you are sure that the traffic is on the right route.

Answer 2

Here is a good blog article on this.

http://sqlblog.de/blog/2009/07/linked-sql-server-encrypted-connection/

Answer 3

At a simple level, just make sure the connection is over the HTTPS channel. You can then use x509 certificates to provide connectivity.

See SF for information on encryption on linked servers.