October 28, 2022 at 2:59 pm
I have a truly odd one and wonder if anyone else has even seen this.
I have a situation where multiple accounts were accidentally got deleted from the domain. The accounts were not able to be recovered so they were recreated and I have been going through the servers dropping the users from the DBs and dropping the logins, then recreating the logins and granting the new login rights to the DBs so the SID matches the way it should. I can't get it to work on one server.
On the problem server, which is the primary in an AG, every time I create the account it gets a different SID than all of the other servers that use this account, including the Secondary of this AG. As the secondary received the SID in the AG DBs from the primary, if the SID does not match then the login will not be able to access the DB on the secondary. Not that this matters right now, because since the SID is wrong on the Primary, the account cannot connect to that server at all, much less any of the DBs.
The only thing I can think of is that the domain controllers, which the primary and secondary use different ones (logon server), are not syncing.
Anyone else have any ideas?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 28, 2022 at 8:48 pm
Not sure I understand the issue - have you removed the user from all databases on the primary (both databases in the AG and those not in the AG) and from all non-AG databases on all secondary replicas?
And then added the new login to all nodes in the cluster?
And finally, added to the user back into all databases?
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
October 31, 2022 at 1:48 pm
Yes, I removed the user from all DBs first. Then deleted the login and recreated the login. At this point the DB user doesn't matter because the login does not get the correct SID when created from a Windows login.
When I did the same on the other node, and the rest of the servers in this environment, Prod, the login got a different SID. The SID is the same on all of the other servers except this one node. The SID on all of the other servers is correct, as I can login to those servers using the login ID.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 31, 2022 at 8:21 pm
That is unusual - is there anything specific about that one node that is different than the other servers? I would check the default DNS settings for the server and check to see if it is connecting to the same domain controller. If that doesn't seem to help - maybe an 'ipconfig /flushdns' would help.
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
November 1, 2022 at 5:21 pm
I checked the logon server for each node in this cluster, and they are using different DCs. I am trying to engage the Windows team to help me work through this, but have not had much luck yet.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 4, 2022 at 6:31 pm
Ran into a similar situation some years ago. After trying everything else, we stopped and re-started the SQL Server service and problem solved.
November 17, 2022 at 3:39 pm
I finally got this fixed, although I still have no definitive reason for it. I had a Windows admin get involved and they found that the account with the incorrect SID was stored in the registry on the server, I believe as the account had logged in to the local console at some point in the past. This account should not log into the console of the server, and that may have been for troubleshooting purposes.
The Windows admin deleted the account from the registry and I was able to add the account to SQL Server with the correct SID.
I have asked the Windows admin to gather more information as to why this happened and what relevance this has to SQL Server but have not heard back yet. My guess is that since Windows had the account profile stored with the old SID, it couldn't discern the SIDs when the new account with the same name was attempted to be added to SQL Server. This, though, shouldn't matter since my understanding of Windows is that it goes by SID, and the account name is only for us humans to use, kind of like an IP address being used instead of the binary address the computer uses.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply