none
sqlcmd - linkedserver - ALL queries fails after FIRST error from remote server - ANONYMOUS LOGON

    Question

  • Hello,

    I have very strange problem with sqlcmd , it stop working after first error returned from remote sql server (like permission denied),

    lets try show what I'm doing, configuration is quite simple


    C- client (windows 2003 x64, standalone sqlcmd client sp3)

    S1 - server1(windows 2003 x64, sql 2005 sp3)

    S2 - server2 (windows 2003 x64, sql 2005 sp3)


    I need connect via middle Linked Server (S1) from C to to S2, each machines have the same LOCAL Windows
    account C\LSUser, S1\LSUser, S2\LSUser with the same password, LSUser is not domain account, linked server is
    configured for Windows Account Impersonation (checked Impersonate)


    Example, query executed from client C:


    1>exec [LS1].[DB].[dbo ].[ListTab1]; - works well, no problem

    1>exec [LS1].[DB].[dbo ].[ListTab2]; - works well, no problem


    ok , now try modify permission on S2, I explicitly deny execute procedure [DB].[dbo ].[ListTab1] on S2 to S2\LSUser,

    1>exec [LS1].[DB].[dbo ].[ListTab1];

    Msg 229, Level 14, State 5, Server S2, Procedure ListTab1, Line 1
    The EXECUTE permission was denied on the object 'ListTab1', database 'DB', schema 'dbo'.

    expected message :), but look now:

    1> exec [LS1].[DB].[dbo ].[ListTab2];

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

    all remote queries STOP working from client C

    1> select top 10 * from [LS].[DB].[dbo ].[ListTab2]

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

    SQL Server logs: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: IP S1 server]


    The same queries executed in the same manner from S2 works well, I't looks like double hop problem or not?

    Why Sqlcmd stop working after firs error from remote server?


    --
    Regards

     

     

    Wednesday, September 08, 2010 3:18 PM