Problem related sp_addlinkedserver

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello Friends

I have a query related sp_addlinkedserver.

I have successfully created addlinkedserver

but when I execute a query to transfer data from one server to another i.e

e.g : insert into tablename select * from [servername].databasename.dbo.tablename

This gives an error.The error is given below.

Login Failed for servername\guest. (eg: max4\guest)

or

login failed for user sa

It means that it does not login on my server. and on another machine it gives Sql server does not exist or access denied.

Pls Help me out as soon as possible.

Thanks in advance

Posted 23-Mar-11 18:13pm

Sunil.yadava09

Updated 24-Mar-11 1:44am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2011-03-23T19:17:00

Well, it sounds like the connection string used to connect is not configured correctly.
Have a look at following threads discussing similar issues:
Link 1[^]
Link 2[^]
Make sure the connection string is correct, then the user credentials used are working.

Lastly, it might be that remote connections might be off, or might be the Firewall is not allowing the connections.

Dylan Morley · Answer 2 · 2011-03-24T01:58:00

On the target server (the server you are linking to), make sure that there are valid credentials you can use that have access to the database you require.

E.g. setup a SQL login with..

uid: SomeUser
pwd: SomePassword

Grant SomeUser access to SomeDatabase, make sure it has Read access at least.

Now on the server where you are created the linked server, expand 'Linked Servers' and find the server you have created.

Right Click -> Properties
Switch to 'Security' tab.
Make sure option 'Be made using this security context' is selected

Remote Login: SomeUser
With Password: SomePassword

Now try running your SQL again

Problem related sp_addlinkedserver

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0