Post reply

Weird Issue - Availability Group Logins not working on secondary(s) but they do work on primary replica

  • July 12, 2018 at 1:18 pm

    #354380

    We have an AG with three nodes, one primary and two secondary nodes.  If I make a Windows Login (or AD Group) on the primary, and then on both secondary nodes, the SIDs match perfectly.  So far, so good.  If I then map a database user on the primary node to a replicated database, granting db_datareader privileges, this gets replicated immediately and all three nodes show that database user, and indicate they are associated with the Windows login and have the correct permissions.  That user can now log into the primary node and query the database normally.  However, this same user is denied on either of the secondary nodes (see screen cap below).  I can create a SQL Login on the primary, then use the created SID to create identical logins on the replicas, and they can log in without any problems on any machine.

    Any thoughts on this?

  • July 12, 2018 at 3:39 pm

    #1997411

    Have you looked in the SQL Server logs for the error state code for that error 18456?  If so, what was it?  If not, then go find it!

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • July 12, 2018 at 4:22 pm

    #1997413

    ThomasRushton - Thursday, July 12, 2018 3:39 PM

    Have you looked in the SQL Server logs for the error state code for that error 18456?  If so, what was it?  If not, then go find it!

    I have and it states that " Token-based server access validation failed with an infrastructure error. Check for previous errors."  Checking into that, I find a couple of things that point to the principle not having the CONNECT_SQL permission, but they do have that.  Still looking, but I should have included this post in my original.  Sorry, just getting frustrated I guess.

  • July 12, 2018 at 5:44 pm

    #1997421

    Social Exodus - Thursday, July 12, 2018 4:22 PM

    ThomasRushton - Thursday, July 12, 2018 3:39 PM

    Have you looked in the SQL Server logs for the error state code for that error 18456?  If so, what was it?  If not, then go find it!

    I have and it states that " Token-based server access validation failed with an infrastructure error. Check for previous errors."  Checking into that, I find a couple of things that point to the principle not having the CONNECT_SQL permission, but they do have that.  Still looking, but I should have included this post in my original.  Sorry, just getting frustrated I guess.

    Sometimes you need to look for both grants as well as denies.

    Sue

  • July 13, 2018 at 11:45 am

    #1997514

    Social Exodus - Thursday, July 12, 2018 4:22 PM

    ThomasRushton - Thursday, July 12, 2018 3:39 PM

    Have you looked in the SQL Server logs for the error state code for that error 18456?  If so, what was it?  If not, then go find it!

    I have and it states that " Token-based server access validation failed with an infrastructure error. Check for previous errors."  Checking into that, I find a couple of things that point to the principle not having the CONNECT_SQL permission, but they do have that.  Still looking, but I should have included this post in my original.  Sorry, just getting frustrated I guess.

    When I have seen this type of error - it was generated because that node was no longer communicating with the domain controllers or there was a problem with how the SPN was created.  This issue doesn't have anything to do with AlwaysOn or the user access to a specific database - rather it is with authenticating the login on that server.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 13, 2018 at 3:43 pm

    #1997567

    I am checking into that right now actually.  I logged into the secondary node machine with my elevated credentials, then ran SSMS with my non elevated credentials to see if trying locally would work, and it does not.  I think you are correct that it is not communicating with our DC at this point because even if I assign no database access, I ought to be able to connect.  I even explicitly granted connect and I also checked for any deny permissions and while there were some, my non elevated account is not in that group.

  • July 17, 2018 at 9:57 am

    #1997858

    Turns out that a group had DENY on it, and the reason that was missed at first was because it was highly obscured that the folks involved were even part of that group.  It's all good now, and thanks for the replies to all 🙂

  • July 17, 2018 at 10:38 am

    #1997863

    Social Exodus - Tuesday, July 17, 2018 9:57 AM

    Turns out that a group had DENY on it, and the reason that was missed at first was because it was highly obscured that the folks involved were even part of that group.  It's all good now, and thanks for the replies to all 🙂

    That actually happens a lot and gets missed at first - that's why I mentioned searching for the deny.
    Thanks for posting back!

    Sue

Viewing 8 posts - 1 through 7 (of 7 total)

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