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.