Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dejjan
Yak Posting Veteran
99 Posts |
 Posted - 2004-11-16 : 02:43:33
|
| I have two servers with different databases and with different logins and passwords. When I want to execute this from Server1.Database1 :select * from Server2.Database2.dbo.Table_nameI receive message: Login failed for user 'sa'.I know that I can do it with OPENROWSET, but is there any way to make permanent connection between those two servers (some similar procedure as sp_addlinkedserver) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-16 : 06:46:18
|
| Why yes, you can use....sp_addlinkedserver. However, you should NOT use the sa login to access it. Create a new login on the server you want to link to and use that login instead. You can map that login to be used by all connections to that linked server when you set it up. |
 |
|
|
dejjan
Yak Posting Veteran
99 Posts |
Posted - 2004-11-16 : 07:26:11
|
| but, if I use sp_addlinkedserver, it inserts a row into sysservers but I still can't execute my select statement (the same message). How can I map that login ? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-16 : 08:06:26
|
| sp_addlinkedsrvlogin can handle that for you. I assume you can't use Enterprise Manager to add the linked server? It's very easy to do that way. |
|
|
|
|
|
|
Login failed for user 'sa'.