Message from discussion
SQL2005 Linked server authentication problem
Thread-Topic: SQL2005 Linked server authentication problem
thread-index: AcdgBbxgilzcyPTWQJGHkl1nEfMEbQ==
X-WBNR-Posting-Host: 194.129.212.98
From: =?Utf-8?B?V29v?= <W...@discussions.microsoft.com>
Subject: SQL2005 Linked server authentication problem
Date: Tue, 6 Mar 2007 07:40:15 -0800
Lines: 67
Message-ID: <8A5BAEF9-337E-4116-AD47-B67641E3B4FE@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 8bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2757
Newsgroups: microsoft.public.sqlserver.security
NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
Path: g2news2.google.com!news2.google.com!news3.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newshub.sdsu.edu!msrtrans!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGHUB02.phx.gbl
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
|
