question

zhiqingzhou-0982 avatar image
0 Votes"
zhiqingzhou-0982 asked zhiqingzhou-0982 commented

Where does SQL server store AD user credential

I have an user account ESCxxx\xxxport (AD account), created as sql login in two sql servers. One is showing ESCxxx\xxxportnew, another shows normally as ESCxxx\xxxport.
when I login on a PC and open SSMS with that AD user, the two sql server instances showing different name (see screenshot 1)
Screenshot2 shows this user's actual name is ESCxxx\xxxport in AD
screenshot3 shows when I open a sql agent job in the weird instance , it attempted to authenticate with the user in another domain (decommissioned domain) , not sure how it works and where it stored the old domain user info,
contacted MS support, but they need more time to do research. Just wondering if anyone has some answers.
104424-s1.png104405-s2.png104406-s3.png


sql-server-general
s1.png (42.4 KiB)
s2.png (17.4 KiB)
s3.png (39.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered zhiqingzhou-0982 commented

Hello,

SQL Server stores the Windows login SID and name on creation of the SQL Server login and newer update it.
If you rename the Windows login name in AD, the name stored in SQL Server remain, until you manually update it.
The login will still work, because for auth SQL Server always uses the SID only and not the name.

You can update the name with ALTER LOGIN, like

 ALTER LOGIN [Domain\OldName] WITH NAME = [Domain\NewName];


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BTW, you can query the stored logins with

 select *
 from sys.server_principals
0 Votes 0 ·

thank you Olaf always.
The user does not exist in syslogin or server_principals . cannot alter the name.
But with the hint you provided, found it was possibly stored somewhere in Windows somewhere.

104807-s4.png104862-s5.png104858-s6.png104808-s7.png


0 Votes 0 ·
s4.png (91.4 KiB)
s5.png (17.7 KiB)
s6.png (23.6 KiB)
s7.png (24.2 KiB)
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered zhiqingzhou-0982 commented

Hi zhiqingzhou-0982,

Welcome to Microsoft Q&A.
We can use master.sys.syslogins and master.sys.server_principals to view user information.
Could you please check if wrh\xxxport is existing in the master.sys.syslogins?
Have you tried to recreate a new job?

Best Regards,
Amelia


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Amelia, thanks for the info. the login name doesn't exist in either syslogins or server_principals.
I looked both views, no wrh\xxxport record found. it is not in local admin group either
I initially did recreate the job first noticed, it turned out nothing to do with particular job, it is related all jobs owned by various users.

I attempted to create the login in SQL server, but it only accept the once a temporary username, not accepting the current AD user name.
I created this user on difference sql instance with current AD username, there is no issues.

see my new screenshot replied to Olaf.

0 Votes 0 ·