SQL Server connecting to another SQL server?

Question

So I have 2 SQL servers, both internal on the firewall, and my webserver is on the DMZ. The webserver currently connects to one of the SQL servers for it's data.

I need some information from the other SQL server which I cannot connect to from the DMZ because of security risks that the company is not willing to take.

How can I connect to one SQL server to another to obtain certain information for particular rows in a database on the first?

So basically what I need to do is:

Server 1 has database "db1" which has a table "tbl" with fields "field1,field2,field3"

I need to populate "field3" from data obtained from Server 2 using a query.

Is this possible?

If not, how else can I do it?

Answer 1

You can set up and use linked servers.

Answer 2

Take a look at linked servers.

http://technet.microsoft.com/en-us/library/ms188279.aspx

Answer 3

you can either create a linked server or use OPENDATASOURCE

Answer 4

Very similar question with good answers: http://stackoverflow.com/questions/189422/how-do-i-create-and-query-linked-database-servers-in-sql-server

Answer 5

I am pretty sure you need to link the servers to pull off what you are trying to do.

http://technet.microsoft.com/en-us/library/ms188279.aspx