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

    Pergunta

  •  

    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



  • Editado ajaymalloc segunda-feira, 27 de fevereiro de 2012 14:04
segunda-feira, 27 de fevereiro de 2012 13:56

Todas as Respostas

  • Hi Ajaymalloc,
    Regarding to your description, please verify the Server A and Server B are using Kerberos authentication first. After you connect to a instance run the following command. If SQL Server is using Kerberos authentication, a character string that is listed as "KERBEROS" appears in the auth_scheme column in the result window.
    Select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid 
    
    After that, please test linked server in single hop setting from Server A to Server B. To test linked server query, run query at SQLA as : 
    select * from LinkedServer.master.dbo.sysdatabases.
    

     If it works, now you need to register a SPN for Server A in a domain. Please follow the steps from this article. And then Configure an Application to Use Constrained Delegation

     >> KERB_ERROR_RESPONSE_TOO_BIG
    According to this erroror message, which might be related network maximum size of datagram packets issue. In order to support the User-to-user extension to the Kerberos protocol you must force Kerberos authentication to use TCP. For more information, please refer to this this article

    If you have any problem please let me know, meanwhile any error launches please post error log for further troubleshooting.

    Regards, Amber zhang


    • Editado amber zhangModerator quarta-feira, 29 de fevereiro de 2012 08:15
    • Sugerido como Resposta Papy NormandModerator quarta-feira, 29 de fevereiro de 2012 12:39
    • Marcado como Resposta Stephanie Lv segunda-feira, 5 de março de 2012 09:30
    • Não Marcado como Resposta ajaymalloc terça-feira, 6 de março de 2012 14:35
    • Não Sugerido como Resposta ajaymalloc quarta-feira, 7 de março de 2012 14:10
    quarta-feira, 29 de fevereiro de 2012 08:12
  • That did not resolve the issue. We have narrowed down this issue as with the installation of SQL Server 2008 R2 client. The same query works with just SQL Server 200 Query analyser installed, but as soon as we install SQL Server 2008 R2 client, the link server breaks.

    Today I have raised a case with MS as well.


    Warm Regards, Ajay


    • Editado ajaymalloc terça-feira, 6 de março de 2012 15:31
    terça-feira, 6 de março de 2012 15:30