locked
SQL Linked server Anonymous logon RRS feed

  • Question

  • Greetings!

    I have an interesting problem I am yet to find a solution for.

    Imagine two domains with two-way trusts between them. (Domain A & Domain B)

    Domain A is the user domain and Domain B is the Application domain. There is one SQL server 2014 and one SQL 2016 instance on two separate SQL clusters in AlwaysOn. Both instances were configured with the proper SPNs and Kerberos authentication is working.

    Now to the strange part. SQL Linked servers were configured both ways between the two instances using 'current identity security context'. If the database  on Instance A (with SQL 2014) is accessed from Instance B (SQL 2016) using SQL Management Studio, then I am able to query it (DB on instance A).

    If I leave SSMS open during the night and in the morning try to run a query against Instance A again, then it fails. (NT Authority\Anonymous logon) In the list of Kerberos tickets I can see that it was refreshed, however it is still not working. One n eeds to close SSMS and open it again and after some minutes querying is again possible. That is always true from Domain A. In Domain B it is strangely enough to open another SSMS window and querying the linked server works in the SSMS instance from the previous day.

    I suspect this has to do with some ticket expiration and the process of Kerberos delegation, but it seems really strange that SSMS is not able to take into account the refreshed ticket... Maybe I am jumping to conclusions here...

    Did anyone face something like this and maybe knows how to fix it or at least explain it?


    [SharePoint lurker]


    • Edited by Vadim Shupak Friday, July 10, 2020 6:47 AM Fixed title
    Friday, July 10, 2020 6:47 AM

All replies

  • Hi, 

    check your default domain policy on both domain 

    Computer Configuration > Policies > Windows Settings > Security Settings > Account Policies > Kerberos Policy

    best regards

    Friday, July 10, 2020 7:04 AM
  • It is exactly the same in both.

    [SharePoint lurker]

    Friday, July 10, 2020 7:52 AM