Post reply

Login failed for user NT AUTHORITY\ANONYMOUS LOGON

  • December 16, 2015 at 9:30 am

    #323083

    I have a situation when I connect a SQL instance with my AD credentials, and run the query below, I get the "Login failed for user NT AUTHORITY\ANONYMOUS LOGON". When I connect using a SQL login account, the query works fine. Both accounts are setup as sysadmins on MyTest. What am I missing here?

    declare @sqlcmd nvarchar(MAX)

    DECLARE @InstanceName VARCHAR(200)

    SELECT @InstanceName = 'MyTest'

    SELECT @sqlcmd = 'select a.* from openrowset(''SQLNCLI'', ''Server='

    + @InstanceName

    + ';Trusted_Connection=yes;'', ''SELECT @@SERVERNAME'') as a'

    EXEC sp_executeSQL @sqlcmd

  • December 16, 2015 at 9:37 am

    #1846356

    It's the double hop authentication issue. Your user credentials are being passed first to SQL Server and then to the remote server, which doesn't work unless you're using Kerberos. I think your options are to use Kerberos, stick with the SQL login, or look at a different way of running the query, such as SSIS.

    John

  • December 16, 2015 at 10:10 am

    #1846361

    Thanks John for the information. I will look into setting up Kerberos authentication.

    Thanks,

    MC

  • December 17, 2015 at 7:27 am

    #1846571

    What causes the double hop authentication issue and is there a way of preventing it?

    Thanks,

    MC

  • December 17, 2015 at 7:57 am

    #1846585

    https://msdn.microsoft.com/en-gb/library/dd352356.aspx?f=255&MSPPError=-2147217396

    Presuming you're in a Windows domain, give the account that's running your SQL Server services permissions in AD to read/write service principle names, restart the services and they'll automatically create the SPN's required. Or, use setspn command line utility to add SPN's manually. Then once the SPN's are created, in AD (dsa.msc), find your service account, and on the delegation tab, trust the specific SQL Server services...

    That should do it....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply