4

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.

migrated from stackoverflow.com Sep 10 '14 at 15:37

This question came from our site for professional and enthusiast programmers.

  • Let us continue this discussion in chat. – Max Vernon Sep 10 '14 at 16:55
  • 1
    @ChadDecker, It is noted that connect item that the issue shows to be resolved in SP1 CU7. – Shawn Melton Sep 10 '14 at 22:28
  • Installing the update seems to have helped although I can't say for sure. It's been over 24hrs and the error hasn't occurred. Thanks for all the help. – Chad Decker Sep 12 '14 at 15:16
2

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';

Thanks goes to @James for pointing out that the sid might be in master.sys.server_principals and to @AaronBertrand for pointing me to a great piece of code that will translate SQL Server binary sids to the Windows format.

N.B.: 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.

  • 1
    Returns zero results. ** For some reason I can't see the responses from James or AaronBertrand that you're referring to. – Chad Decker Sep 10 '14 at 20:38
  • @ChadDecker Max didn't get those responses here – Lamak Sep 10 '14 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.