SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Renamed AD users' accounts, now some SQL Servers can't get info


Renamed AD users' accounts, now some SQL Servers can't get info

Author
Message
schleep
schleep
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 1463
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?



schleep
schleep
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 1463
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.



schleep
schleep
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 1463
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?



Alan Burstein
Alan Burstein
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24264 Visits: 8349
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


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

schleep
schleep
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 1463
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.



Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78890 Visits: 14499
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
schleep
schleep
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 1463
Her Newname is listed as a member of various groups having server access, on all servers.



Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78890 Visits: 14499
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
schleep
schleep
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5158 Visits: 1463
Newlogin fails. Oldlogin succeeds.



Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78890 Visits: 14499
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search