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
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.
Best Answer
Just in case this helps, I'm posting some code that might help you work out what account that sid represents:
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, sincetempdb
gets recreated automatically upon SQL Server startup, and thus the function here will not survive service restarts or machine reboots, etc.