[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Linked Server Problem

Posted on 2009-02-18
Medium Priority
Last Modified: 2012-05-06
Hello there.   I have a SQL Server on my machine.  I successfully link to a corporate database (BF-SQL3) using the sp_addlinkedsvr script and am able to see all tables associated with that server, as I am supposed to be.  However,  we are trying to migrate my SQL Server Database to another corporate wide server (BF-SQL1).  For some reason I cannot link the two!!!

-What MS SQL permissions do I need in order to allow this to happen?

Question by:suicehockey44
  • 4
  • 2
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23670890
You can run SQL Scripts which will work perfectly..
But if you use Stored Procedure, you need to enable MSDTC service and other set of things to make it work as given below:

LVL 57

Expert Comment

by:Raja Jegan R
ID: 23670898
Kindly give me the exact error message you have obtained so that I can guide you on it.
FYI, I worked on similar things before..

Author Comment

ID: 23671471
Thanks guys, I run the sp_addlinkedserver and that actually works!!  However, it will not allow me
access to the tables, which are protected with a UN/PASS combo.  While running the
sp_addlinkedsrvlogin I receive the following message:

Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


Author Comment

ID: 23671480
BTW the script for that is:

EXEC sp_addlinkedsrvlogin 'SERVER', 'FALSE' ,null, 'TEST_USER', '*******'


Author Comment

ID: 23671509
Another interesting thing is that Im Using SQL Server Express 2008 on my machine and the linked server is 2005.  
I am reluctant to truly consider this an issue because whilst connect to my own machines server, both the aforementioned scripts work correctly. Hmm.

Accepted Solution

suicehockey44 earned 0 total points
ID: 23672354
I believe I found a solution. If I hard code the SELECT statement from the linkserver.catalog.view I get the results, but for some reason, the objects themselves aren't visible in the GUI. Perhaps a compatability issue.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question