creating a linked server

• Ken Kemp

  Newbie

  Points: 1

  More actions

  August 14, 2006 at 11:05 am

  #75038

  I have a question regarding setting up a linked server. We have 2 SQL servers. We have a view from a DB on SQL Server A that needs to access a DB SQL Server B. I setup the Linked server (so I thought). I used the following command.

  /* Create a Linked server to SQL-Server */

  EXEC SP_ADDLINKEDSERVER

  @server = SQL_LINK1,

  @srvproduct = '',

  @provider = 'SQLOLEDB',

  @datasrc = [SQL-SERVER],

  @location = null,

  @provstr = null,

  @catalog = null

  /* add a login */

  EXEC sp_addlinkedsrvlogin

  @rmtsrvname = SQL_LINK1,

  @useself = 'false',

  @locallogin = NULL,

  @rmtuser = 'sa',

  @rmtpassword = 'password'

  After I have done this, I try to access the tables and views under the Linked server and I receive a message "Error 18456: Login failed for user 'sa'". I then tried changing the Linked Server Security to "Be made using the login's current security context". This returns an error Login Failed for the Anonymous account.

  I verified my sa passwords are correct between servers. Am I missing something simple? Any suggestions?

  Thanks.

• sqldba-294117

  Hall of Fame

  Points: 3486

  More actions

  August 15, 2006 at 8:48 am

  #655071

  Please see the link, is very helpfull.

   

  http://www.informit.com/articles/article.asp?p=21644&rl=1

  Thanks