MS SQL Linked Server Security failing using current security context

Question

I have 2 instances of SQL 2008 64 bit (ver 10.0.4000) running on a windows 2008 64 bit server db03 and db03\ins2. The ins2 has a linked server connecting to db03 that was setup by replication. I have code that needs to connect to db03 from ins2 to kick off a proc, so am trying to use same linked server connection. It is failing with error:

Msg 18483, Level 14, State 1, Line 1 Could not connect to server db03 because '' is not defined as a remote login at the server.

Here is what I have done:

• Verified kerberos is setup correctly and validated I am connecting with kerberos. http://msdn.microsoft.com/en-us/library/ms189580%28v=SQL.105%29.aspx
• Changed linked server property to allow data access (turned off b/c replication created it).
• Ran this (I think redundant from my GUI change) EXEC sp_addlinkedsrvlogin 'DB03', 'true'
• Changed to using sql authentication account to connect on linked server properties and tested that it works.
• Both servers are using same domain service account.

What I would like to happen is to use the "Be made using the login's current security context."

EXEC master.dbo.sp_addlinkedserver @server = N'DB03', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB03',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'DB03', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Thanks, Chuck.

• SQLGuyChuck: For giggles, I created a new alias for the db03 instance and all worked. It seems the replication created linked server is crippled in some fashion. Need to find a way to get it to work as updating all my code is just not going to work for me.
• Robert L Davis: 1) Kerberos not required for this. 2) Data access is for running direct queries. For executing a procedure, make sure RPC and RPC out is enabled on the linked server.
• Robert L Davis: How is security configured for the linked server?
• SQLGuyChuck: @SirSQL on twitter #sqlhelp said: Repl creates it as a remote server which is no longer supported. Found out the hard way. He had to drop repl, create linked server, re-create replication. And another alternative is to create a sql alias and new linked server connection, but I can't as lots of code would have to change.
• Robert L Davis: Try disabling remote proc transaction promotion. This forces a distributed transaction.

Answer 1

You mention a SQL Agent Job. I usually make sure the SQL Server Agent is configured with a domain account as well, and I usually use the same account.

• SQLGuyChuck: I changed to proc call as that is ultimately all I am doing. I did test with one of my procs and was able to reproduce.
• SQLGuyChuck: I can do a regular select statement, just not a proc call.

Answer 2

The answer is that we have to recreated the linked server, which means dropping replication using that remote server, creating linked server manually, then adding the replication back. See http://msdn.microsoft.com/en-us/library/ms188740.aspx for IsRemote vs. IsLinkedServer which allows this kind of access and IsRemote does not.