Secure transfer of data from the SQL server to the SQL server via the Internet


I need to transfer data daily from SQL Server (2008) to SQL Server (2005). One of the servers is located at our web host so the data will be transferred over the Internet. On the same network I would use SSIS to transfer the data, but over the Internet this is not a secure option. Is there a secure way of achieving this?

You can use SSL with SQL Server (2000/2005 Instructions / 2008 Instructions) and then force protocol encryption on the connection between both machines. You don't have to use a purchased SSL certificate either, you can use Windows Server Certificate Services to generate one - however if you do so then the CRL must be on a machine that both servers can connect to. An easy way to do this is install Certificate Services on a stand alone machine, perhaps just a VM and the configure it to embed a public DNS name for it's CRL. This doesn't have to be a machine running Certificate Services, just something you own and can upload to. Then you can generate the certificates and publish the CRL and tada, all done.

You will need to ensure the service account SQL is running as has access to the private key of the certificate it is using.