I'm creating a linked server from ServerA to ServerB, both of them are SQL Server 2008 servers. I need to create the linked server using a domain service account so that anyone will be able to utilize the linked server. ServerB only accepts Windows authentication, so I've added the service domain account to ServerB with proper permissions. I've verified the permissions by logging in with the service domain account to ServerB and everything looks great. But when I create a linked server on ServerA with the script below, I get a "login failed for user". Is this script incorrect, or am I missing something else?
USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'ServerB', @catalog=N'MyDatabase' GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'mydomain\myuser',@rmtpassword='mypassword' GO