Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to retrive the table values from other server

Query

EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = 'SA', @rmtpassword = 'SA'

SELECT * FROM OPSERVER.VENUS.dbo.TABL1

The above query is showing error as login failed for user 'SA'

Treid Query

EXEC sp_addlinkedserver @server = 'OPSERVER', @srvproduct = 'SQL Server'

sp_addlinkedsrvlogin 
  @rmtsrvname = 'OPSERVER', 
  @locallogin = 'sa', 
  @rmtuser = 'sa', 
  @rmtpassword = 'sa'

SELECT * FROM OPSERVER.VENUS.dbo.TABLE1



Showing error as "Login failed for user 'sa'"

What wrong in my query.

share|improve this question
add comment

migrated from stackoverflow.com Jan 24 '12 at 15:50

This question came from our site for professional and enthusiast programmers.

2 Answers

Stored procedure sp_addlinkedsrvlogin has the parameter @useself. BOL says about it (marked by me):

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE.

A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

I think you need to try to add the login with @useself='false':

sp_addlinkedsrvlogin 
  @rmtsrvname = 'OPSERVER', 
  @useself='false',
  @locallogin = 'sa', 
  @rmtuser = 'sa', 
  @rmtpassword = 'RemoteSaPassword'
share|improve this answer
add comment

You need top provide password for remote sa account, like this:

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OPSERVER', @rmtuser = 'SA', @rmtpassword = 'RemoteSaPassword'

Or if you run under local sa account

sp_addlinkedsrvlogin 
  @rmtsrvname = 'OPSERVER', 
  @locallogin = 'sa', 
  @rmtuser = 'sa', 
  @rmtpassword = 'RemoteSaPassword'

FOr windows auth:

sp_addlinkedsrvlogin 
  @rmtsrvname = 'OPSERVER', 
  @locallogin = 'domain\username', 
  @rmtuser = 'sa', 
  @rmtpassword = 'RemoteSaPassword'

Or

sp_addlinkedsrvlogin 
  @rmtsrvname = 'OPSERVER', 
  @useself = 'TRUE'
share|improve this answer
    
I felt very bad myself, again it is showing error as Login FAILED FOR user sa' –  user1157846 Jan 24 '12 at 12:22
    
You replaced the string RemoteSaPassword with a proper sa password on server OPSERVER? –  Oleg Dok Jan 24 '12 at 12:24
    
Password is SA ONLY, BUT IT IS SHOWING ERROR AGAIN.... –  user1157846 Jan 24 '12 at 12:31
    
If it is windows authentication means, what i have to give...? –  user1157846 Jan 24 '12 at 12:33
    
Updated answer. –  Oleg Dok Jan 24 '12 at 12:33
show 8 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.