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

Question

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.com/SQLServer/feedback/details/772069/login-failure-with-connection-pooling-in-contained-db-after-connection-has-added-contained-user

It seems to rear its head when making a call from C# / .NET program.

As far as I can tell, the "contained user" functionality is just a bit flaky at the moment. Presumably switching back to an instance-level user (e.g. "sa") will fix the problem. Will try that next.

Answer 1

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.