Click here to Skip to main content
15,568,965 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 24-Mar-11 2:44am
v2

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.
 
Share this answer
 
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900