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

  • 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?

  • 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.

  • 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?

  • 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.

    "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

  • 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.

  • 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

  • Her Newname is listed as a member of various groups having server access, on all servers.

  • 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

  • Newlogin fails. Oldlogin succeeds.

  • 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

  • So I come in last night after hours to bounce the service and, failing that, the server.

    But first I tried the EXECUTE AS... and it worked. For the old and new names...

    Thinking problem solved, I left well enough alone.

    Yet this morning, the applications are still not working. The users are getting the same error message, Cannot retrieve profile for domain\longusername. But running a trace, I notice that it's their oldname in the NTUserName and LoginName columns!!!! They are logged into the network with their newnames.

    I believe we're using Kerberos.

  • Correction: not working again this morning, same condition as yesterday.

  • I rarely recommend it, but in this case I would try bouncing the server.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • If we can't resolve this today, I'll do that tonight and see what happens.

  • How is it going?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 15 posts - 1 through 15 (of 33 total)

You must be logged in to reply to this topic. Login to reply