Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » adding login with renamed AD account
Add to briefcase

adding login with renamed AD account Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Robert klimes
Posted Wednesday, September 14, 2011 2:35 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: Yesterday @ 2:16 PM
Points: 890, Visits: 1,863
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
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1175282
Jack Corbett
Posted Thursday, September 15, 2011 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871
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
Robert klimes
Posted Thursday, September 15, 2011 12:29 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: Yesterday @ 2:16 PM
Points: 890, Visits: 1,863
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
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1175992
Robert klimes
Posted Monday, September 19, 2011 8:16 AM


SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 2:16 PM
Points: 890, Visits: 1,863
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
-----------------------------------------------------------------------------
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1177259
NJ-DBA
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: Tuesday, May 21, 2013 11:49 AM
Points: 858, Visits: 1,329
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
chip_cary
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: Wednesday, May 15, 2013 8:40 AM
Points: 1, Visits: 69
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
ddezwaan
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: Tuesday, January 29, 2013 3:20 PM
Points: 3, Visits: 51
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.