Can I setup a link SQL server connection between servers on different networks?

Question

We have a production SQL server hosted offsite at a hosting company, and we have a staging environment within our own network. We want to be able to setup a SQL job that copies content from a table on the staging server to prod on a regular basis, and I think we need to setup a linked server connection to do this. What do I need to get the hosting company to do to allow us to set this up? We have RDP access to the production servers, I just need to know what network and security configurations need to happen from the hosting company's perspective so I can ask them to do it.

Answer 1

A linked server is not the best option.

• it opens the SQL Server for remote T-SQL execution, a very serious security hole
• it requires SQL password based authentication because of the different domains involved
• it does not offer any redundancy when faced with spotty conectivity
• TDS as a protocol is not designed for speed

A much better alternative is to use Service Broker:

• SSB operates on a dedicated port that does not allow arbitrary T-SQL commands, like a linked server
• SSB supports certificate based authentication accross distinct domains
• Message fragmentation and delivery fairness ensures a smooth operation over bad/slow connections
• SSB uses a high throughput protocol designed for speed, sam eprotocol used in database mirroring.

If you insist on the linked server then you must:

• enable SQL Server to listen on the public internet addressed
• enable TCP ion the server and open the SQL listenening port (default TCP 1433) on the firewall. If the server listen on non-default ports, the you must start the SQL Browser service and open port 1434 UDP on the firewall, and allow sqlservr.exe to open arbitrary ports on the firewall.
• you must enable SQL Authentication to allow for SQL based password.
• To protect the traffic you should ensure SSL is used see How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console and How to Enable Channel Encryption.
• Check, re-check and double-recheck that the [sa] login has a bulletproof password that is known only by people that you have absolute 100% trust. Your TDS port opened to the internet will be subject to a constant barage of brute force attacks on [sa] from a million automated bots.