How to access tables on a different server (SQL Server)

I have created a stored procedure in SQL Server.  I need to access tables on a database within the same server instance, but also some tables on a completely different server.

How do I do that?
Laurie AlmoslinoAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Welcome to EE, I see this is your first question. Trust we will serve you well :)

A linked server is not a two-way street. it is a single lane 'one way' street.

On server 'A', you set up a linked server to Server 'B'.

Which means Server A can put and get data (and limited other things) on Server B where Server B just sees it as yet another connection / user.

When setting up the linked server on Server A, you have to give it the information it will need to log onto Server B

Read : https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine

Then read : https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine

When accessing another DB on same instance, use three part name identifier
e.g. DBname.SchemaName.TableName  

When accessing another DB on different instance, use four part name identifier
e.g. ServerName.DBname.SchemaName.TableName  (or Linked Server as ServerName)
2
 
Pushpakumara MahagamageVPCommented:
Create a link server to second server and then you can access second database tables

How to add linked server

http://sqlserverplanet.com/dba/how-to-add-a-linked-server
0
 
Laurie AlmoslinoAuthor Commented:
Thank you for this answer.  I apologize for leaving out some information in my question.  The reason the tables are on a second server is that the tables will be accessed by an outside app, and the IT department wanted to keep the two servers separate, so that the main (production) server is more secure.

is there any way to reference the 2nd server without linking the two servers together?

thank you!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pushpakumara MahagamageVPCommented:
Do you familiar  with web service. seems you have to write a web service to access  2nd server data.

Perhaps you have to use SSIS, you can pull data through web service and keep them on 1st server.
0
 
Olaf DoschkeSoftware DeveloperCommented:
the IT department wanted to keep the two servers separate, so that the main (production) server is more secure.

Linked servers keep the physical servers separate. The linked server will need to offer some log in the other server can use to make a connection, but that doesn't "lower shields" per se.

The way you describe it you would defy the meaning of production server quite a bit.You allow normal users on a production server unless your database server is in the cloud and you want to serve data for generally unknown public users, too. Typically only the web server then accesses the database.

But in a scenario with employees in a LAN or WAN your end users will have logins in the production server, they will have just as low permissions as necessary to work with the data, but they will connect.

Now when it comes to accessing a second server from a first server, maybe the more delicate data, you can set it up, so a low permission user still is able to call procedures WITH EXECUTE AS somelogin. That doesn't let users inherit these "superpowers", just like a PHP script connecting to a MySQL database via root user doesn't make the public website user have an end-to-end root user connection to the database.

Bye, Olaf.
1
 
Laurie AlmoslinoAuthor Commented:
Thank you Mark.  I appreciate the clear way you explained this.
0
 
Mark WillsTopic AdvisorCommented:
A pleasure, and thank you for the compliment :)
0
 
Olaf DoschkeSoftware DeveloperCommented:
The situation is still a bit unclear, There is a production server, and there is another server, I don't assume test or development, but actually another production server for another application, perhaps? And which one needs access to the other one?

And I want to clear some things up yet a bit further: Typically the one you connect to, either by an application user (as in a web app) or by each end user (employee situation), that server will be the one needing the linked server as Mark described it as a one-way connection. That server uses the other just like a client uses the first server. So what one-way connection means is that linked server still doesn't have the first server as linked server resource, too. But of course, the linked server is not just read-only. Access permissions are as given by the login used to make the linked server connection.

In that situation, a user with an SSMS connecting to the first server will see that linked server resource and may use it. If it's problematic this opens up access, notice you can link to just a certain database, the login used to make the connection can be given just the minimum necessary permissions and so you don't open up all the server and all its databases.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.