July 8, 2013 at 7:06 am
Hello,
I have a problem with Windows authentication on a SQL server.
The problem occurs when in my company, a woman marries and changes her last name (our Windows logins are based on our last name)!
Indeed, in these cases, the infrastructure service (in charge of our Active Directory) renames the Windows login and not creates a new one.
The problem is that SQL side, when the user logs in and I do a SQL trace (or with the SUSER_SNAME() function), SQL still sees the old login "domain\old_login" and not the new "domainew_login" while on another server everything is ok!
The infrastructure service told me that all AD are synchronized.
So I would like to know what could be the problem :
- how authentication takes place?
- Is that SQL maintains a "cash"?
- Is it Windows that sends the login to SQL or is it SQL which queries Windows?
- If it's Windows, that it send the login or the SID?
- If it's a SID, how SQL find the login?
- If it's a problem of synchronization of the server with the ad, how to force sync?
...
Thank you in advance.
July 8, 2013 at 5:56 pm
Chances are you need to issue ALTER LOGIN [DOMAIN\OLD.NAME] SET NAME = [DOMAIN\NEW.NAME]; to rename the SQL Server Login to have the same name as the Active Directory Account. This will not affect the SID or the Login's access to the Instance or links to any Database Users. While the SID is used to authenticate to Active Directory, the Login name is also stored in the system table underlying sys.server_principals.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 9, 2013 at 2:09 am
Well, I didn't know that......
July 9, 2013 at 2:39 am
Hello,
the problem is that the login is not defined directly on the server, it's a member of a Windows security group and this is the group that is defined.
So I can't alter the login!
July 9, 2013 at 3:42 am
In that case you might try creating a Login from the Windows Account and then immediately dropping it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 9, 2013 at 3:55 am
Ok it would be a workaround but why that happens?
Where SQL finds this old login?
July 9, 2013 at 4:09 am
I am assuming it is stored in a system table the first time it is resolved so it does not have to reach out to Active Directory every time you call SUSER_SNAME(). You could argue its a performance decision.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 9, 2013 at 5:00 am
you don't have an idea of the name of this table or view?
July 9, 2013 at 5:27 am
It might be the table referred to by sys.server_principals. It's likely not documented.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 9, 2013 at 5:45 am
Had a similar problem some months ago with renamed Windows accounts.
http://www.sqlservercentral.com/Forums/Topic1328455-1550-3.aspx#bm1352733
After banging our heads against the proverbial wall for a week, we stumbled upon the solution: re-boot the SQL Server.
July 9, 2013 at 5:51 am
schleep (7/9/2013)
Had a similar problem some months ago with renamed Windows accounts.http://www.sqlservercentral.com/Forums/Topic1328455-1550-3.aspx#bm1352733
After banging our heads against the proverbial wall for a week, we stumbled upon the solution: re-boot the SQL Server.
Might I suggest then that the cache that OPC was talking about is in TempDB? It would make some sense.....
July 9, 2013 at 6:27 am
I checked in tempdb and saw nothing special: (
I checked the CREATE VIEW [system]. [Server_principals_new] but it uses system tables that are not queryable as master.sys.sysxlgns, sys.syspalnames... !
For the reboot, it's not so easy in production 🙂
July 9, 2013 at 6:38 am
I know. It was frustrating, since we had to schedule our AD changes around the 15 minute nightly window for re-booting. Even more frustrating as it was only the primary prod box that required the re-boot. Most of the other SQL servers did not show this behaviour.
July 9, 2013 at 7:03 am
You can select from those system tables only when logged in using the DAC.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 9, 2013 at 7:55 am
I done some tests on a dev environnement and in the master.sys.sysxlgns there is only logins that are defined on the server, I dont see logins that are connected via a group !!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply