SQL Linked Server Linked Server Query fails with Msg 18452, Level 14, State 1 Login failed for user '(null)'

Ajukan pertanyaan

27 Februari 2012 13:56

0

We have windows authentication problem on some VM desktops with XP Professional sp3. A set of desktop is not able to execute linked server queries while other set do.

Have gone through all of the suggested steps that is in http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx and that uncovered the fact that instead of KERBEROS that those problem desktops are using TCP, NTLM*.

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

------------------------------------------

A - is the middle SQL server 2008 R2 Ent Edition that has a linked server object

B - Linked Server instance(SQL Server 2000 Enterprise Edition)

C - Client Machine(Windows XP desktop)

{net transport, auth_scheme}

C to A

A to B

1

TCP, NTLM*

TCP KERBEROS
Domain Info

--------------------------------------------
1. All A, B, C are running under the same domain
2. SQL Server service domain account is common on both A and B
Error reported while trying to execute linked server query:

------------------------------------------------

Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

After I enabled KERBEROS logging, I found below in the system event log of SQL Server machine (Computer B in your example) :
1. STATUS_ACCESS_DENIED
2. KERB_ERROR_RESPONSE_TOO_BIG
Any help would be highly appreciated!!!!

Script of Link Server

-------------------------------------

/****** Object: LinkedServer [PROD2K] Script Date: 02/27/2012 10:48:19 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'PROD2K', @srvproduct=N'SQLServer', @provider=N'SQLNCLI', @datasrc=N'kew.mondrian.mipl.com'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROD2K',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROD2K',@useself=N'True',@locallogin=N'Interface',@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'rpc', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'rpc out', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

Warm Regards, Ajay

{net transport, auth_scheme}	C to A	A to B
1	TCP, NTLM*	TCP KERBEROS

SQL Linked Server Linked Server Query fails with Msg 18452, Level 14, State 1 Login failed for user '(null)'

Semua Balasan