September 14, 2011 at 2:35 pm
I am trying to add a login to a SQL server 2008 R2 SP1 active/passive server on windows 2008 R2 SP1. When I try to add login through either ssms or create login statement I receive the following error. The account i am trying to add has recently had the name changed.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'DOMAIN\username' not found. Check the name again.
If I try to add the previous name it works fine.
when i run xp_logininfo for the currently named account I receive this error
xp_logininfo 'DOMAIN\currentusername','all'
Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'DOMAIN\currentusername', error code 0xffff0002.
if I run xp_logininfo with the old name I get two results with permission path in two active directory groups that are logins on the server.
xp_logininfo 'DOMAIN\oldusername','all'
account nametypeprivilegemapped login namepermission path
DOMAIN\oldusernameuseruserDOMAIN\oldusernameDOMAIN\group1
DOMAIN\oldusernameuseruserDOMAIN\oldusernameDOMAIN\group2
I then proceed to do an xp_logininfo on both groups.
xp_logininfo 'DOMAIN\group1','members'
group1 does not have either the current or old account name listed but group2 is a member of group1. group2 has the currentusername listed as a member but not the oldusername.
I have tried removing and re-adding the account to the groups in AD. I have also removed and re-added group1 (could not remove group 2 *** many user would be affected).
any help on why this is occurring and possibly how to resolve this would be greatly appreciated.
September 15, 2011 at 8:48 am
I'm not an expert, but it sounds like the change has not replicated to the DC that the SQL Server is querying. I could be wrong though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2011 at 12:29 pm
That was my first thought so I checked all the domain controller manually. They all appear to be correct.
When adding the login on any other sql server it works fine with the new name, old name does not work at all.
September 19, 2011 at 8:16 am
I was able to add the login after I had failed-over the server to the other node.
My investigation into this led me to another question. When trying to add the login through sql or SSMS and even searching for login in SSMS did not cause any traffic to active directory. I had found this by setting up a packet capture on the database server scanning for any traffic on ports 389,88,139.
So my question at this point would be: Does sql server cache active directory credentials for all its logins and all AD acount withing and group login? If so, is there any way to clear this cache?
September 19, 2011 at 12:05 pm
Robert klimes (9/19/2011)
I was able to add the login after I had failed-over the server to the other node.My investigation into this led me to another question. When trying to add the login through sql or SSMS and even searching for login in SSMS did not cause any traffic to active directory. I had found this by setting up a packet capture on the database server scanning for any traffic on ports 389,88,139.
So my question at this point would be: Does sql server cache active directory credentials for all its logins and all AD acount withing and group login? If so, is there any way to clear this cache?
Hmm, I always thought of it like this: SQL Server talks to Windows, which DOES cache authentication information. If Windows needs to talk to AD it will based on expiration criteria... could be wrong on this, would appreciate know this for sure if someone else has detailed knowledge of the internals.
July 31, 2012 at 1:21 pm
I know this thread is old but I was having a similar issue with cached AD authentication credentials. I found that the SQL service account (AD account) was using cached Kerberos tickets and not getting updated info from AD. This is on the OS end and there isn't anything in SQL that will refersh this. However I found I can run:
EXEC xp_cmdshell 'klist.exe purge'
which purges all Kerberos tickets for the current user. Since xp_cmdshell runs under the context of the service account all the Kerberos tickets were purged and new ones generated. This solved my problem and I hope someone else will benefit from this info too.
January 29, 2013 at 3:22 pm
I had the same problem with caching of an AD login with a name change, and this article provided the solution: http://support.microsoft.com/kb/946358
February 24, 2014 at 8:54 pm
I had a similar issue, a user changed their AD Login details and for some reason the old username was still being passed to SQL Server. The user had access to the Database via a security group so no changes there were required.
We confirmed the change had replicated to the DC, the issue was happening on multiple different machines ie it wasn't cached on a specific workstation.
When profiling the old username was appearing but they were able to connect, I'm assuming via the Security Group.
When running xp_logininfo 'DOMAIN\oldname' & xp_logininfo 'DOMAIN\tnewname' both commands would return details of the user as if they both existed on the DC. Running the same command would fail for the 'DOMAIN\oldname' from any other server.
So by this point the issue was narrowed down to the SQL Server itself, something was caching the user details. I tried clearing the TokenAndPermUserStore hoping that would solve the problem but no luck.
Due to time constraints I ended up dumping the cache (DBCC FREEPROCCACHE & DBCC DROPCLEANBUFFERS) and it solved the problem.
So now I'm trying to work out where exactly it was cached so if it happens again I can fix it in a nicer cleaner way.
Any ideas?
Cheers
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply