"A severe error occurred" - Login Failed (but only occasionally)

I'm using a C# application to execute a query against SQL Server. Here's the relavent portion of an exception I'm encountering once every twenty-four hours or so:

System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'S-1-9-3-3788657366-1166700905-846186909-3339902698'.
A severe error occurred on the current command. The results, if any, should be discarded.

Here are the salient points:

• I've been running this exact query for years, usually several

thousand times per day, with no problem.

• The problem started immediately after updating to SQL Server 2014

from SQL Server 2008.

• Unlike in SQL Server 2008, where I used a server-level user for

login, I'm now using a database-level login. That database user is
mapped to DBO. I'm not sure if this is even relevant but since the
error message says "login failed" I figured I'd mention it.

Again, the problem occurs only once in a thousand times -- maybe once every 24 hrs.

Is there a way I can reverse lookup the user in question given the user account ID? Recall that the error message provided me with this user ID: S-1-9-3-3788657366-1166700905-846186909-3339902698. Not that it should matter since my C# app only uses this single user account.

For reference, here is my connection string:

Server=inst2;Database=ema;User ID=emauser;Password=myPasswordGoesHere

** UPDATE *

I invested a few hours on this and received some great help from people on this site. There doesn't appear to be any workable solution right now.

This error message, specifically when the username is omitted and replaced with a bogus SID that doesn't correspond to any particular user on the database server or calling client machines, is just a bug in SQL Server that occurs when using contained users. The following links touch on the subject:

https://stackoverflow.com/questions/18629840/sql-login-failed-for-user-s-1-5-21-1482476501-1214440339-839522115-500-long-id (see SiHoran's answer)

and

https://connect.microsoft

Just in case this helps, I'm posting some code that might help you work out what account that sid represents:

USE tempdb;
GO
-- to translate SID in binary format to AD format
CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1))))
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, 
            REVERSE(CONVERT(VARBINARY, @val)))))
    SET @j = @j + 4
  END
  RETURN ( @StringSID )
END
GO

SELECT dbo.fn_SIDToString(sp.sid), sp.*
FROM sys.server_principals sp
WHERE dbo.fn_SIDToString(sp.sid) = 'S-1-9-3-3788657366-1166700905-846186909-3339902698';

I put this function in tempdb to avoid creating it in a permanent place. You may want to put it somewhere else for future use, since tempdb gets recreated automatically upon SQL Server startup, and thus the function here will not survive service restarts or machine reboots, etc.

StackExchange Database Administrators Q#76226, answer score: 2