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

bad Windows login when connect to SQL Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 7:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:33 AM
Points: 51, Visits: 213
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.
Post #1471166
Posted Monday, July 8, 2013 5:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, June 27, 2014 5:59 AM
Points: 7,079, Visits: 12,567
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
Post #1471397
Posted Tuesday, July 9, 2013 2:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 306, Visits: 472
Well, I didn't know that......
Post #1471470
Posted Tuesday, July 9, 2013 2:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:33 AM
Points: 51, Visits: 213
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!
Post #1471476
Posted Tuesday, July 9, 2013 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, June 27, 2014 5:59 AM
Points: 7,079, Visits: 12,567
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
Post #1471506
Posted Tuesday, July 9, 2013 3:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:33 AM
Points: 51, Visits: 213
Ok it would be a workaround but why that happens?
Where SQL finds this old login?
Post #1471513
Posted Tuesday, July 9, 2013 4:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, June 27, 2014 5:59 AM
Points: 7,079, Visits: 12,567
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
Post #1471522
Posted Tuesday, July 9, 2013 5:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 9:33 AM
Points: 51, Visits: 213
you don't have an idea of the name of this table or view?
Post #1471535
Posted Tuesday, July 9, 2013 5:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, June 27, 2014 5:59 AM
Points: 7,079, Visits: 12,567
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
Post #1471549
Posted Tuesday, July 9, 2013 5:45 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 426, Visits: 915
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.



Post #1471557
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse