Sql-server – Query execution showing error as login failed

sql serversql-server-2000

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.

Best Answer

You need to 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'
Related Question