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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Renamed AD users' accounts, now some SQL...

33 posts, Page 1 of 4

1 2 3 4 »»»

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

Rate Topic

Display Mode

Topic Options

Author

Message

schleep

Posted Wednesday, July 11, 2012 11:00 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 348, Visits: 623

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

schleep

Posted Wednesday, July 11, 2012 11:09 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 348, Visits: 623

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

schleep

Posted Wednesday, July 11, 2012 12:33 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 348, Visits: 623

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

Alan.B

Posted Wednesday, July 11, 2012 12:41 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 238, Visits: 1,201

schleep (7/11/2012)

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.

-- AJB
xmlsqlninja.com

Post #1328503

schleep

Posted Wednesday, July 11, 2012 12:50 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 348, Visits: 623

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

opc.three

Posted Wednesday, July 11, 2012 1:40 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1328539

schleep

Posted Wednesday, July 11, 2012 1:47 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 348, Visits: 623

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

Post #1328546

opc.three

Posted Wednesday, July 11, 2012 2:10 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

On the problem instance can you do this?

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

Post #1328562

schleep

Posted Wednesday, July 11, 2012 2:37 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 5:19 AM
Points: 348, Visits: 623

Newlogin fails. Oldlogin succeeds.

Post #1328582

opc.three

Posted Wednesday, July 11, 2012 3:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1328598

« Prev Topic | Next Topic »

33 posts, Page 1 of 4

1 2 3 4 »»»

Permissions