I've done some testing with what happens to Windows accounts which have been renamed after being added to SQL Server. I'm going to demonstrate using local user accounts and groups though the same behaviour will be apparent in Active Directory.

My VM is named W08R2S14 and here's the original user cleverly named OriginalName and member of OriginalGroup.

![][1]

![][2]

And the SIDs.

PsGetsid.exe OriginalName

PsGetSid v1.44 - Translates SIDs to names and vice versa
Copyright (C) 1999-2008 Mark Russinovich
Sysinternals - www.sysinternals.com

SID for W08R2S14\OriginalName:
S-1-5-21-2640196809-3402802471-4060526964-1009

PsGetsid.exe OriginalGroup

PsGetSid v1.44 - Translates SIDs to names and vice versa
Copyright (C) 1999-2008 Mark Russinovich
Sysinternals - www.sysinternals.com

SID for W08R2S14\OriginalGroup:
S-1-5-21-2640196809-3402802471-4060526964-1010

First I create a login within SQL Server for OriginalName and confirm its SID translation using the Outer Apply method [shown in my previous post][3].

Create 	Login [W08R2S14\OriginalName] From Windows

Select	sid_string
From	sys.server_principals
-- Snip
Where	name = 'W08R2S14\OriginalName'
S-1-5-21-2640196809-3402802471-4060526964-1009

I created a SQL Server Profiler trace to capture the Security Audit - Login Audit event with all fields and write them to master.dbo.LoginTrace on the same instance. In the real world we set up traces to write these to rolling files, which are queried remotely and stored on a centralised server for alerting and analysis.

![][4]

I'll test the login and verify it shows up in the trace, along with the addition of the SID translation routine which I've tweaked because of the different data types involved. I've split up the output so you can see everything that is possible to capture.

runas.exe /user:OriginalName "sqlcmd -S .\SQL2014 -E -Q \"Select 1\"" 
Select	*
From	master.dbo.LoginTrace sp
Outer Apply (
	Select	'S-' +
					Convert(Varchar, Convert(Int, Substring(sp.LoginSid, 1, 1))) +
					'-' +
					Convert(Varchar, Convert(Int, Substring(sp.LoginSid, 3, 6))) +
					'-' +
					Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 9, 4))))) +
					Case 
					When	Datalength(sp.LoginSid) > 13 
					Then	'-' + 
							Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 13, 4)))))
					Else 	'' 
					End +
					Case 
					When 	Datalength(sp.LoginSid) > 17 
					Then	'-' + 
							Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 17, 4)))))
					Else 	'' 
					End +
					Case 
					When 	Datalength(sp.LoginSid) > 21 
					Then	'-' +
							Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 21, 4)))))
					Else 	'' 
					End +
					Case 
					When 	Datalength(sp.LoginSid) > 25 
					Then	'-' +
							Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 25, 4)))))
					Else 	'' 
					End +
					Case 	
					When 	Datalength(sp.LoginSid) > 29 
					Then	'-' + 
							Convert(Varchar, Convert(Bigint, Convert(Varbinary, Reverse(Substring(sp.LoginSid, 29, 4)))))
					Else 	'' 
					End 
			As LoginSidString
	) sp2
Where	ApplicationName = 'SQLCMD'
And     LoginSidString = 'S-1-5-21-2640196809-3402802471-4060526964-1009'
RowNumber EventClass ApplicationName BinaryData ClientProcessID DatabaseID DatabaseName
116 14 SQLCMD 0x2000002838F4010000000000 880 1 master
EventSequence EventSubClass GroupID HostName IntegerData IsSystem LoginName
195 1 1 W08R2S14 4096 NULL W08R2S14\OriginalName
LoginSid NTDomainName NTUserName RequestID SPID
0x010500000000000515000000C9345E9D27A5D2CA74B906F2F1030000 W08R2S14 OriginalName 0 51
ServerName SessionLoginName StartTime Success TextData Type LoginSidString
W08R2S14\SQL2014 NULL 2015-06-03 21:44:06.833 1 -- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed 1 S-1-5-21-2640196809-3402802471-4060526964-1009 </tr> </tbody> </table> Now the fun begins. Using lusrmgr.msc I've renamed OriginalName to NewName. Naturally this does not change the name of the login stored in SQL Server. ## Q. What happens to the login trace after we change the login name in Windows? Nothing. It remains exactly the same. Despite logging in with a different login name only the old login name is displayed in each of the trace fields, because they're all filled in by SQL Server. Authentication is done on the SID. Weirdly however the ERRORLOG will show a different story. I've tested here using the NewName login, and then re-tested it when the login is disabled. Both show NewName instead of OriginalName. ``` sql Exec master.sys.xp_readerrorlog 0, 1, N'W08R2S14' ```
LogDate ProcessInfo Text
2015-06-03 21:58:59.790 Logon Login succeeded for user 'W08R2S14\NewName'. Connection made using Windows authentication. [CLIENT: <local machine>]
2015-06-03 22:07:21.910 Logon Login failed for user 'W08R2S14\NewName'. Reason: The account is disabled. [CLIENT: <local machine>]
This is extremely unfair. However it also means if you're capturing login traces it is unsafe to do auditing by matching login names to server principal names. You can however rely on matching up the SIDs (to a degree, it becomes more complicated when you bring Active Directory and multiple servers into the mix). ## Remediation. I had read it was possible to find these renamed logins using sp\_validatelogins (and also to find logins that have been disabled or removed from AD entirely, though this didn't appear to be entirely true). And it's true in this case it does pick up that the old login name has an issue... ``` sql Exec master.sys.sp_validatelogins ```
SID NT Login
0x010500000000000515000000C9345E9D27A5D2CA74B906F2F1030000 W08R2S14\OriginalName
However I tested this same procedure with a renamed Active Directory account and it did not pick up on the name difference; so it's not a reliable method. If you do happen find differences through this or another method (such as what I use which is scraping Active Directory) you can rename Windows logins within SQL Server just as you can rename SQL logins; with some caveats. ``` sql Alter Login [W08R2S14\OriginalName] With Name = [W08R2S14\NewName] ``` With a Windows login the above command checks to make sure that the new name exists; this prevents you from undoing your change unless you rename the Windows account back as well (inducing seizures in the Change & Release department). It also checks that the account you're renaming it to has a matching SID (I believe this also works with Active Directory SID History but didn't test it). Otherwise you may receive one of these error messages:

Msg 15401, Level 16, State 1, Line 5
Windows NT user or group 'W08R2S14\OriginalName' not found. Check the name again.

Msg 15098, Level 16, State 1, Line 8
The name change cannot be performed because the SID of the new name does not match the old SID of the principal.

**I would not go and just fix up logins where you find them. It could have unintended consequences if you're using impersonation, Resource Governor, or have schemas tied into the login names.** For my next test I disable the login, add the group login, and run a login test again. ``` sql Drop Login [W08R2S14\OriginalName] Create Login [W08R2S14\OriginalGroup] From Windows ``` ## Q. What will be in the trace after disabling the login and adding a group login? A login failure from W08R2S14\NewName. __Even though the group exists, it cannot login when the more specific login is disabled.__ ## Q. What will be in the trace after removing the login and adding a group login? The names in the trace change to reflect the account name (NewName) but otherwise nothing different. - LoginName = W08R2S14\NewName - NtUserName = NewName - LoginSid_String = S-1-5-21-2640196809-3402802471-4060526964-1009 Even though we're authenticating against a group, or possibly multiple groups, only the direct login name information and SID will be reported. If you check in your server principals you will find nothing relevant to tell you how this account just logged in. If you're optimistic you may use this to try to work it out: ``` sql Exec master.sys.xp_logininfo 'W08R2S14\NewName', 'all' ```
account name type privilege mapped login name permission path
W08R2S14\NewName user user W08R2S14\NewName W08R2S14\OriginalGroup
But I wouldn't bother, it's well known that this function is incapable of recursing nested groups which are extremely common in Active Directory setups; often you'll get no relevant information back at all. ## Q. What happens if I disable the group? You can't disable groups in SQL Server, only drop them. You cannot disable groups in Active Directory either, however you can convert them from a Security Group to a Distribution Group... note though that allegedly some applications use Distribution Groups for security authentication regardless Here's what that looks like. ``` powershell Import-Module ActiveDirectory # Security Group Get-ADGroup TestGroup DistinguishedName : CN=TestGroup,CN=Users,DC=codykonior,DC=com GroupCategory : Security GroupScope : Global Name : TestGroup ObjectClass : group ObjectGUID : 0a2a61fb-ed60-47de-9a6a-cac2abd0c513 SamAccountName : TestGroup SID : S-1-5-21-3862251885-2122243004-2026543712-1121 # After change to Distribution Group Get-ADGroup TestGroup DistinguishedName : CN=TestGroup,CN=Users,DC=codykonior,DC=com GroupCategory : Distribution GroupScope : Global Name : TestGroup ObjectClass : group ObjectGUID : 0a2a61fb-ed60-47de-9a6a-cac2abd0c513 SamAccountName : TestGroup SID : S-1-5-21-3862251885-2122243004-2026543712-1121 ``` To really solve these problems requires a lot more involvement; scraping Active Directory and each machine for domain, computer, user, group, and membership information, and then matching it all up with server principals and login traces. It isn't easy. [1]: /images/2015/06/l1.jpg [2]: /images/2015/06/l2.jpg [3]: /2015/05/31/how-to-convert-an-sql-login-sid-to-a-readable-string [4]: /images/2015/06/l3.jpg