On my machine (named SQLPAPA), I had a local account called blakhani. When I was trying to login to SQL with that account, I was getting error.

TITLE: Connect to Server
——————————
Cannot connect to SQLPAPA\SQL2014.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘SQLPAPA\blakhani’. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
——————————
BUTTONS:
OK
——————————

When I checked ERRORLOG, I found below

2014-05-20 06:32:47.510 Logon        Error: 18456, Severity: 14, State: 5.
2014-05-20 06:32:47.510 Logon        Login failed for user ‘SQLPAPA\blakhani’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

“Reason” provided in error message is self-explanatory. The error appears when an incorrect account name is used to connect to SQL Server. But wait… when I checked catalog view, I was surprised to see that the account exists in sys.logins.

This means that SQL doesn’t use name for user authentication but used SID. My next task was – how to convert the value in sid column to something which windows operating system knows. While searching I came across this blog which asked to use ConvertStringtoSID to get the windows format of hex code. Blog also gives logic to convert raw hex value to SID. I have broken them into human understandable steps.

• 0x01050000000000051500000043379EC8D8B415550A542B51FE030000
• 0x01 05 000000000005 1500 0000 4337 9EC8 D8B4 1555 0A54 2B51 FE03 0000  (total 5 groups so break them in group of 4)
• 0x01 05 000000000005 0000 1500 9EC8 4337 1555 D8B4 2B51 0A54 0000 FE03  (reverse the bits in pair of two)
• 0x01 05 000000000005 00 00 15 00 9E C8 43 37 15 55 D8 B4 2B 51 0A 54 00 00 FE 03 (break them in group of 2)
• 0x01 05 000000000005 00 00 00 15 C8 9E 37 43 55 15 B4 D8 51 2B 54 0A 00 00 03 FE (reverse the bit in pair of two)
• 0x01 05 000000000005 00000015 C89E3743 5515B4D8 512B540A 000003FE (join them together)
• number of dashes = second number + 2 = 7
• Use calculator and convert hex to number
• Final SID = S-1-5-21-3365812035-1427485912-1361794058-1022

Then I used PsGetSID tool from sysinternals and found SID for SQLPAPA\blakhani

As we can see there is a mismatch in SID stored in SQL (last piece is 1022 vs 1023) and that’s why SQL is not able to authenticate the login.

To make my task little easier for future, I have written small piece of code to convert hex value in SQL to SID.

 DECLARE @varBinarySID VARBINARY(85)
-- assign the value in below variable
SELECT @varBinarySID = 0x01050000000000051500000043379EC8D8B415550A542B51FE030000
DECLARE @StringSID VARCHAR(100)
DECLARE @len AS INT
SET @len = LEN(@varBinarySID)
DECLARE @loop AS INT
SELECT @StringSID = 'S-'
SELECT @StringSID = @StringSID + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@varBinarySID, 1, 1))))
SELECT @StringSID = @StringSID + '-'
SELECT @StringSID = @StringSID + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@varBinarySID, 3, 6))))
SET @loop = 9
WHILE @loop < @len
BEGIN
    DECLARE @temp_var BINARY (4)
    SELECT @temp_var = SUBSTRING(@varBinarySID, @loop, 4)
    SELECT @StringSID = @StringSID + '-' + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @temp_var)))))
    SET @loop = @loop + 4
END
SELECT @StringSID 'String SID'

I have tested the code above and it was able to convert all sid correctly on my machine.

The solution of the problem which I faced was to drop and recreate the login in SQL. While creating login, SQL would take latest SID from operating system and would store in logins.