Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

adding login with renamed AD account Expand / Collapse
Author
Message
Posted Wednesday, September 14, 2011 2:35 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,442, Visits: 2,724
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 name	type	privilege	mapped login name	permission path
DOMAIN\oldusername user user DOMAIN\oldusername DOMAIN\group1
DOMAIN\oldusername user user DOMAIN\oldusername DOMAIN\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.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1175282
Posted Thursday, September 15, 2011 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 10,295, Visits: 13,279
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1175783
Posted Thursday, September 15, 2011 12:29 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,442, Visits: 2,724
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.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1175992
Posted Monday, September 19, 2011 8:16 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,442, Visits: 2,724
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?


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1177259
Posted Monday, September 19, 2011 12:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:47 AM
Points: 885, Visits: 1,424
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.
Post #1177397
Posted Tuesday, July 31, 2012 1:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 2:34 PM
Points: 1, Visits: 109
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.
Post #1338128
Posted Tuesday, January 29, 2013 3:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:06 PM
Points: 4, Visits: 55
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
Post #1413360
Posted Monday, February 24, 2014 8:54 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 23, 2014 5:03 PM
Points: 480, Visits: 469
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



Post #1544756
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse