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 1234»»»

Renamed AD users' accounts, now some SQL Servers can't get info Expand / Collapse
Author
Message
Posted Wednesday, July 11, 2012 11:00 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 428, Visits: 939
We changed some users AD accounts to a new name (samaccountname, displayname, logon, etc..)

On one SQL server (2 instances), we get the following error on setuser.
Could not obtain information about Windows NT group/user 'cipsewname', error code 0xffff0002

On 4 other SQL servers here, setuser with new name works fine.

Any ideas? I've forced replication of the AD controllers; everything appears OK in AD, there is no trace of the old name.

Win2K8 R2, SQL 2K8 R2.

Any ideas?




Post #1328455
Posted Wednesday, July 11, 2012 11:09 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 428, Visits: 939
I should have noted that the users are in AD groups, and access the SQL Servers via their group memberships.

Renaming the users accounts does not affect their group membership.

Moreover, it's one server out of 5, all on the same domain.



Post #1328459
Posted Wednesday, July 11, 2012 12:33 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 428, Visits: 939
More info:

1) For the user in question, a newname account was created as a copy of their oldname account, then deleted. Then along comes me and renames the oldname account to newname.
I don't see why this should be a problem. It's all about SIDs anyway, right?

2) We have determined that none of our DCs knows anything about the oldname account.
I explicitly connected to them in Powershell, and ran get-qaduser oldname: All the DCs returned NULL
They all returned user objects for the newname.

3) The problematic SQL Server is not connecting to the same DC as the rest. However, it is connecting to a DC that we have verified in 2) above.

4) On the problematic SQL server, the oldname works with setuser!
On one of our other servers, both the oldname and newname work with setuser!!!

5) When we check AD for that newname from the Windows server on that SQL server, it correctly identifies the newname.

I can't think of anything other than SQL Server must be caching AD group memberships somewhere?






Post #1328497
Posted Wednesday, July 11, 2012 12:41 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:18 AM
Points: 562, Visits: 2,617
schleep (7/11/2012)
We changed some users AD accounts to a new name (samaccountname, displayname, logon, etc..)

On one SQL server (2 instances), we get the following error on setuser.
Could not obtain information about Windows NT group/user 'cipsewname', error code 0xffff0002

On 4 other SQL servers here, setuser with new name works fine.

Any ideas? I've forced replication of the AD controllers; everything appears OK in AD, there is no trace of the old name.

Win2K8 R2, SQL 2K8 R2.

Any ideas?



Without looking at your machine I suspect that one of those users owned the dbo schema. Go into the DB > Security > Users, open the dbo properties and look at the login name. Does one of those users show up for login name?

I ran into this recently when removing an old DBA's account from the server - she was the owner of the dbo schema. It had the same error. Setting the owner to a valid user fixed the issue.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1328503
Posted Wednesday, July 11, 2012 12:50 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 428, Visits: 939
Thanks Ninja, but we're not even getting close to a DB yet.

Our prod SQL Server can't get info for the new name, let alone allow a login to proceed.
All the others servers to have no problem, and one of them still gets a return on setuser for her oldname, which we've confirmed no longer exists in our domain.

All our users connect as members of a group. There is no way for the individual to own anything.



Post #1328510
Posted Wednesday, July 11, 2012 1:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 PM
Points: 7,097, Visits: 12,598
What does this yield on the working instance? How about the non-working instance?

EXEC sys.xp_logininfo 
@acctname = N'GROUPNAME',
@option = 'members';



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1328539
Posted Wednesday, July 11, 2012 1:47 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 428, Visits: 939
Her Newname is listed as a member of various groups having server access, on all servers.




Post #1328546
Posted Wednesday, July 11, 2012 2:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 PM
Points: 7,097, Visits: 12,598
On the problem instance can you do this?

EXECUTE AS LOGIN = 'NewLoginName';
GO
REVERT;
GO
EXECUTE AS LOGIN = 'OldLoginName';
GO
REVERT;
GO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1328562
Posted Wednesday, July 11, 2012 2:37 PM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 8:10 AM
Points: 428, Visits: 939
Newlogin fails. Oldlogin succeeds.



Post #1328582
Posted Wednesday, July 11, 2012 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:38 PM
Points: 7,097, Visits: 12,598
Grasping now...

Are you using Kerberos or NTLM? I am thinking cached tickets.

Have you tried bouncing the SQL service? The server?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1328598
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse