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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.