1

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.

Improve this question
6
  • 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.
    – SQLGuyChuck
    Dec 6, 2011 at 17:27
  • 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
    Dec 6, 2011 at 17:28
  • How is security configured for the linked server?
    – Robert L Davis
    Dec 6, 2011 at 17:30
  • @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.
    – SQLGuyChuck
    Dec 6, 2011 at 17:56
  • Try disabling remote proc transaction promotion. This forces a distributed transaction.
    – Robert L Davis
    Dec 6, 2011 at 18:07

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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
    Dec 6, 2011 at 17:52
  • I can do a regular select statement, just not a proc call.
    – SQLGuyChuck
    Dec 6, 2011 at 17:57
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.