Sql-server – How to add a linked server to a sql server management studio

linked-serversql server

I have an account in sql server management studio, which I can login to database B, which is on server X.
I also have a crystal report on my local PC. Using the crystal report, I can access a database (A) thru a ODBC driver, which is DRV_A, install on my local PC. I can write reports against the database A using the crystal report.

Now I want to directly from sql server management studio and database B to query both database B and A. So it looks I need to add database A as a linked server to database B.

When I try to do that, I get the following error message:

Cannot show request dialog
Additional information:
A requred operation could not be completed. You must be a member of the susadmin role to perform this openration. (SqlManagerUI)

So, it seems that

  1. I need to get a role of permission to add the linked server
  2. Install the ODBC driver on server X

What else I should have, IP address and login name, password?

Thank you very much.

Best Answer

Linked Servers are not database specific, but provide a pipeline between two servers. Of course, the rights granted to the users will control which databases they can access. The account you are using naturally needs to be able to logon to both servers with enough permissions to execute your query.

Yes, creating a Linked Server is an administrative function and not available to all. Doing it yourself requires the ALTER ANY LINKED SERVER permission.

The parameters for the sp_addlinkedserver stored procedure are found at: http://msdn.microsoft.com/en-us/library/ms190479(v=sql.110).aspx

Also, under that link are instructions for several different ways to connect the servers together. If Server X and Server A are both SQL Servers, the setup is quite simple.

You can use sp_addlinkedsrvlogin to manage how the logins authenticate across the linked server. The details are at: http://msdn.microsoft.com/en-us/library/ms189811(v=sql.110).aspx

Depending on your landscape and if you are using Windows Logins, you may need to have Kerberos configured properly on your landscape so that the Kerberos ticket (allowed permissions) can be forward to the other server. See: Configuring Linked Servers for Delegation:

http://technet.microsoft.com/en-us/library/ms189580(v=sql.105).aspx

Related Question