SQL2005 Linked server authentication problem

Woo

unread,

Mar 6, 2007, 10:40:15 AM3/6/07

to

Hi,

I am having an authentication issues with all my SQL2005 servers when using
double-hop Kerberos authentication. The user I am authenticating is a domain
admin.

The problem occurs when I tried to connect from a client PC, to a SQL
database where the query uses a linked server to retrieve the information
from a different server e.g.

Running on a PC with SQL Native Client:
sqlcmd -S SERVER1 -E -I -t 15 -Q "select * from server2.database.dbo.table"

When I try from a client to a SQL2005 then onto SQL2000 via linked server, I
get this error:

OLE DB provider "SQLNCLI" for linked server "dbaserver" returned message
"Communication link failure".
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.
HResult 0x4814, Level 14, State 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.

When I try from a client to a SQL2005 then onto SQL2005 via linked server, I
get this error:

Msg 18456, Level 14, State 1, Server WEBSTAGING, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I have seen various articles with solutions they are all either irrelevant
or dont make any difference:

1) http://support.microsoft.com/kb/919710
---We dont use certificates

2)Use SQL Server authentication
---Not an option

4) SynAttackProtect:
http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx
---Set this up in registry, rebooted server, made no difference

I checked this article and all steps were correct up to point 7:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

BUT when I run “select net_transport, auth_scheme from
sys.dm_exec_connections where session_id=@@spid”

I get :

net_transport auth_scheme
----------------- ---------------
Shared memory NTLM

I am at a loss!

Any ideas??

ta
Wendy

Russell Fields

unread,

Mar 6, 2007, 11:14:36 AM3/6/07

to

Wendy,

You might want to read this document, which is not specific to SQL Server.
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

The one time I saw this problem it turned out to be this topic within the
above document: Multiple principal entries in database

RLF
"Woo" <W...@discussions.microsoft.com> wrote in message
news:8A5BAEF9-337E-4116...@microsoft.com...

Sue Hoegemeier

unread,

Mar 6, 2007, 10:27:53 PM3/6/07

to

The results from sys.dm_exec_connections tells you it's a
local connection - maybe you are picking up cached
connection? You probably have to clear out all caches -
clearing the distributed query cache with DBCC
FREESESSIONCACHE isn't likely to do it. You'd need to use:
DBCC FREESYSTEMCACHE ('ALL')

-Sue

Woo

unread,

Mar 8, 2007, 10:16:50 AM3/8/07

to

Hiya,

Tried this and am now getting:

From Client to Server1:

net_transport auth_scheme
----------------- ---------------
TCP KERBEROS

From server1 to Server2:

net_transport auth_scheme
----------------- ---------------
Shared memory NTLM

Any ideas?

Thanks
Wendy

Sue Hoegemeier

unread,

Mar 9, 2007, 11:05:59 PM3/9/07

to

Check the client protocols on server1 using Configuration
Manager. log onto Server1 and check things when you use
sqlcmd to connect from Server1 to Server2. It should only
being using Shared Memory when connect locally.

-Sue

Lijun Zhang

unread,

Mar 14, 2007, 4:13:48 PM3/14/07

to

I am having the same problems as yours. It seems works again after I reboot
the client machine. But this is not a solution since our application uses
Component Service to access the database. Rebooting the middle tier machines
during work hours is not an option.

Please let me know how you solve the problem.

Lijun

"Woo" <W...@discussions.microsoft.com> wrote in message
news:8A5BAEF9-337E-4116...@microsoft.com...

Woo

unread,

Mar 15, 2007, 6:04:05 AM3/15/07

to

Sue,

I checked this out and the Client Protocols are setup like this:

Name Order Enabled
-----------------------------------------------------
Shared Memory 1 Enabled
TCP/IP 2 Enabled
Named Pipes Disabled
VIA Diabled

Should the order by reversed??

Thanks for your help with this
Wendy

Woo

unread,

Mar 15, 2007, 7:58:13 AM3/15/07

to

Hi again sue,

I am a bit confused to what you want me to check.

Do you want me to log onto Server1 and run something like:

sqlcmd -S SERVER1 -E -I -t 15 -Q "SELECT * from SERVER2.Database.dbo.TABLE"

I am not 100% sure how I am supposed to check if it is connecting with
shared memory or not. Do I just run this below query in the same CMD window
as I ran the SQLCMD or not??

select net_transport, auth_scheme
from sys.dm_exec_connections where session_id=@@spid

I am finding it hard to capture anything about the SQLCMD from this table
while it is running.

Sue Hoegemeier

unread,

Mar 15, 2007, 8:36:36 AM3/15/07

to

Log onto Server1 and use sqlcmd to connect to Server2. The -S
ServerName should be Server2.

-Sue

Woo

unread,

Mar 15, 2007, 9:31:16 AM3/15/07

to

Hi Sue,

I have done this and I can connect, it is double hopping that isnt working.

I am not sure what you want me to check after I have run my sqlcmd from
Server1:

SQLCMD -S Server2 -E -I -t 15 -Q "select getdate()"

The client protocols are set up like:

Name Order Enabled
-----------------------------------------------------
Shared Memory 1 Enabled
TCP/IP 2 Enabled
Named Pipes Disabled
VIA Diabled

Wendy

Sue Hoegemeier

unread,

Mar 19, 2007, 8:32:38 PM3/19/07

to

You would have wanted to check what protocol is being used -
specifically if shared memory is used to connect. Are you
sure the instances Server1 and Server2 are not on the same
box? If shared memory is used between the two then the
instances appear to be on the same server. So that's not a
double hop.

-Sue

On Thu, 15 Mar 2007 06:31:16 -0700, Woo

Woo

unread,

Mar 20, 2007, 4:32:05 AM3/20/07

to

Hi,

These instances are not on the same server and I have tried this with
different combinations of servers, it is not server specific it must be
something to do with the standard SQL Server 2005 install and delegation.

I am trying to double hop, that is the problem, it isnt working!

If it is using shared memory this isnt because they are on the wrong
server,it is because something is wrong and this is the problem. How do I
change this?

thanks
wendy

Sue Hoegemeier

unread,

Mar 20, 2007, 7:58:25 PM3/20/07

to

I understand that you are trying to authenticate across
servers and the double hop issue.
But...when you say "if it is using shared memory"...so we
don't even know that for sure. If it is using shared memory
and the instances are on different servers then there could
be something wrong on the network end of things if it is
seeing the other server as a local resource. If it really
isn't using shared memory, you could just have a mess with
your SPNs. The SPN for a server being incorrect could be at
the root of all of it.
At this rate, it would likely take months to get at all the
information needed through newsgroup posts. I would suggest
that you open a case with Microsoft PSS and have them walk
through the whole thing by phone.

-Sue

Woo

unread,

Mar 21, 2007, 5:59:00 AM3/21/07

to

Thanks Sue.

Our SPNs are fine as the SQL2005 ones were setup the same as SQL2000 and
they work ok.

I have opened a call with microsoft to try and get to the bottom of this.

Woo

unread,

Mar 29, 2007, 9:56:02 AM3/29/07

to

Hiya,

Just to let you know I raised a call with MS about this issue and it turns
out this double-hop kerberos issue with linked servers in SQL2005 is an
actual MS bug and is resolved with SP2:
http://support.microsoft.com/default.aspx?scid=kb;en-us;921896

or hotfix 925843 (http://support.microsoft.com/kb/925843/)

Hope this helps!

Cheers
Wendy

Woo

unread,

Mar 29, 2007, 9:58:03 AM3/29/07

to

Hiya,

Just to let you know I raised a call with MS about this issue and it turns
out this double-hop kerberos issue with linked servers in SQL2005 is an
actual MS bug and is resolved with SP2:
http://support.microsoft.com/default.aspx?scid=kb;en-us;921896

or hotfix 925843 (http://support.microsoft.com/kb/925843/)

Cheers
Wendy

Lijun Zhang

unread,

Mar 30, 2007, 12:02:55 PM3/30/07

to

Thanks!

"Woo" <W...@discussions.microsoft.com> wrote in message

news:AC317BCE-283D-41AE...@microsoft.com...