How is SQL Change Automation helping you? Share to win DevOps books.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' in baseline script - but delegation seems fine

I've installed Change Automation in SSMS for a fresh start on our existing database. It has shown us a lot of problems with stored procedures and missing objects, but that's fine, we've cleaned that up.

The problem seems to be now with a linked server. Let's say Server 1 is S1, Server 2 is S2, etc, the database in question is DB1, and so on.

The DB we want to source control is S1.DB1, so that's the baseline I've created. There is a copy on our dev server, so S2.DB1, which is our development target. One of the stored procedures in DB1 reads a table from a linked server S3, and that connection is fine - procedure works in production and I can run scripts manually on both S1 and S2. However, when I go to the Generate Migrations or Verify tabs in SQL Change Automation, it creates the shadow S2.DB1_SHADOW and runs the script, then errors with "Login failed for user NT AUTHORITY\ANONYMOUS LOGON'

Now, I know that's a kerberos double-hop issue, but we've checked the settings in Server 2 and Server 3. They seem to have delegation set up in the Active Directory, and both service accounts seem to have had the correct Service Provider names set up. I'm not sure what else to try.

Answers

  • Eddie DEddie D Posts: 1,681 Rose Gold 5
    Hi,
    Thank you for your forum post.

    Can you please increase the minimum logging level to Debug?  Help menu ->Logging ->Log Level and Set the Debug option.  The default is Info.

    Repeat the previous steps:
    However, when I go to the Generate Migrations or Verify tabs in SQL Change Automation, it creates the shadow S2.DB1_SHADOW and runs the script, then errors with "Login failed for user NT AUTHORITY\ANONYMOUS LOGON'

    When the problem occurs, locate the current log file and sent a copy to [email protected]  This action will create a support ticket and we can then further investigate the problem.

    Many Thanks
    Eddie


    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • Dylan01pDylan01p Posts: 4 New member
    I ended up working with someone else to get an SPN and delegation set up on S2. I also used the full connection string for the SQL CA projects. So rather than our alias DW01 I used the xxxsrv01,8484 type pattern.

    Anyway, fixed now.
Sign In or Register to comment.