Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 '11 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 '11 at 17:28
    
How is security configured for the linked server? –  Robert L Davis Dec 6 '11 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 '11 at 17:56
    
Try disabling remote proc transaction promotion. This forces a distributed transaction. –  Robert L Davis Dec 6 '11 at 18:07

2 Answers 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.

share|improve this answer

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.

share|improve this answer
    
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 '11 at 17:52
    
I can do a regular select statement, just not a proc call. –  SQLGuyChuck Dec 6 '11 at 17:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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