SQL Server – Firewall and Linked Servers Configuration

linked-serversql serversql-server-2008-r2

I have:

  1. SQL Server 2008R2 database server in my LAN and under my control (call it LOCALSQL)
  2. remote SQL Server, on which I have much less control (call it REMOTESQL), for which I only have IP, username, password
  3. Linux-Apache-PHP server, on my LAN, used to access LOCALSQL (call it PHPSERVER)

Up to now, PHPSERVER accesses LOCALSQL; I can only access REMOTESQL from a test Windows machine with SQL Server Management Studio, with the necessary firewall permission.

Now, I would like to use sp_addlinkedserver to have PHPSERVER execute query involving tables from both servers.
What should I do for the firewall? Should I allow connections:

a) between LOCALSQL and REMOTESQL

b) between PHPSERVER and REMOTESQL

c) both of the above

Best Answer

a) between LOCALSQL and REMOTESQL

Your PHP client never connects to the REMOTESQL server. It only connects to LOCALSQL and then LOCALSQL connects to REMOTESQL.

Keep in mind that your PHP availability is now driven by the REMOTESQL availability and consider that distributed queries are notoriously problematic to perf tune. Good luck!

Related Question