Converting SQL Server SIDs to login names

In the past, whenever you used MS SQL mirroring, or log shipping, you'd get this problem when you tried to bring a database back up after a failure event. The problem was that the security principals stored in a database mapped back to security principals stored in the master database, and they did so by SID. So even if you had security principals with the same name in your master database on your fail-over node, they wouldn't match up and you wouldn't be able to log in any more.

In SQL Server 2012, Microsoft introduced the idea of contained users, which are exactly what they sound like; users exist wholly within the database in which they're defined. So when you have a fail-over event, your database is back up and accessible straight away.

Unfortunately it created an issue. It's not really SQL Server's fault. We were developing some new applications that used Entity Framework as an ORM layer. We were seeing lots of errors turning up in the SQL Server run time logs similar to:

Login failed for user 'S-1-9-3-3987763894-1196622702-682210441-1728208786.'. Reason: Could not find a login matching the name provided. [CLIENT: XXX.XXX.XXX.XXX]

Now, for our own reasons many of our contained users had the same login name (and even the same password), but we had distinct connection strings for every database and data intent. And it would appear that connections to the SQL server were trying to cross domains and access databases they didn't have access to. Sometimes connections/queries were being attempted against the master database for no apparent (deliberate, explicit in code) reason.

I'm not sure exactly where to place the blame, that's the developer's call, but it could have been explicit code bugs, or a quirk in EF's connection pooling making assumptions where the server name and the login/user names and passwords that they must be interchangeable (which in the old world they may have been). But it would be really interesting to see which logins were trespassing. For what it's worth I also saw similar behaviour in Service Bus for Windows, despite it having distinct connection strings for Management, Container, and Gateway databases - I don't think they had contained users in mind at any point when making that software, which is odd given its origins in Azure.

Anyway, what's the problem? The problem is that the SID is locked away in the sys.database_principals table as a blob, and the only place you ever see the familiar string representation (S-1-9-3-...) is in the error log. If it were a Windows user SID (S-1-5-21-...) we could map it straight to a username easy-peasy, but not for SQL SIDs.

There are T-SQL functions for translating SIDs to login names and back again but they either didn't work for me, or expected/returned the binary representation of the SID which was useless.

So I wrote some Powershell to solve my problem. And here it is:

Get-DatabasePrincipalsList.ps1

param (
        [Parameter(mandatory=$true)]
        [string]$ConnectionString
)

function Get-SIDStringFromBytes ([byte[]] $bytes) {
  ( new-object System.Security.Principal.SecurityIdentifier($bytes,0) ).ToString()
}

$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $ConnectionString
try {
 $conn.Open()
} catch {
 Write-Error "Unable to open connection to the database server."
 Exit 1
}

try {
 $conn.ChangeDatabase( "master" )
} catch {
 Write-Error "Unable to change to master database: $_"
 $conn.Close()
 Exit 1
}

$dbListQuery = "SELECT name FROM sys.databases WHERE name <> 'master'"
$sqlCommand = new-object System.Data.SqlClient.SqlCommand( $dbListQuery, $conn )

try {
 $sqlReader = $sqlCommand.ExecuteReader()

 $databaseList = @()
 while ( $sqlReader.Read() ) {
  $databaseList += $sqlReader.Item("name")
 }

 $sqlReader.Close()
} catch {
 Write-Error "Error retrieving list of databases from master: $_"
 $conn.Close()
 Exit 1
}

$databaseList

$principalsQuery = "SELECT name,type_desc,sid FROM sys.database_principals WHERE sid IS NOT NULL AND type_desc IN ('SQL_USER','WINDOWS_USER')"

$principals = @()
  
foreach ( $database in $databaseList ) {
 try {
  $conn.ChangeDatabase( $database )

  $sqlCommand = new-object System.Data.SqlClient.SqlCommand( $principalsQuery, $conn )

  $sqlReader = $sqlCommand.ExecuteReader()

  while ( $sqlReader.Read() ) {
   $principal = "" | Select Name,Database,Type,SidHex,Sid

   $principal.Name = $sqlReader.Item("name")
   $principal.Database = $database
   $principal.Type = $sqlReader.Item("type_desc")
   $principal.SidHex = $sqlReader.Item( "sid" )
   try {
    $principal.Sid = Get-SidStringFromBytes $principal.SidHex
   } catch {
    $principal.Sid = "Unknown"
   }

   $principals += $principal
  }

  $sqlReader.Close()
 } catch {
  Write-Error "Something went horribly wrong reading database principals from $database"
 }
}

$principals

$conn.Close()

It takes a single argument, which is the initial connection string. If the user you're running the script as has sa rights on the database, for example, you can invoke it like this:

.\Get-DatabasePrincipalsList.ps1 -ConnectionString "Server=MyMSSQLServer;Integrated Security=true"

Any .Net SQL data provider connection string is fair game, but the first thing the script attempts to do is change to the master database. It's going to Just Work™ if you're using an account with sa, but I'm sure there are less brutal ways to get rights; I'm not a SQL Server guy.

After that, it's going to retrieve a list of databases from the sys.databases table, then iterate through them to collect a list of principals of type SQL_USER or WINDOWS_USER. Once we've got them we invoke some System.Security.Principal.SecurityIdentifier magic on the blob containing the binary representation of the SID (which just comes back as an array of bytes, byte[]) to turn it into the string format we know and love. That's this bit:

function Get-SIDStringFromBytes ([byte[]] $bytes) {
  ( new-object System.Security.Principal.SecurityIdentifier($bytes,0) ).ToString()
}

The script outputs an array of all the security principals found, so you can Where-Object your heart out to find the one you're looking for and the database it originated in.

For me, I get stuff like (details obfuscated to protect the innocent):

Name     : someusername
Database : SomeDatabase
Type     : SQL_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-9-3-3574901072-1311533442-3147823503-3515760278

Name     : AppUser
Database : SomeDatabase
Type     : SQL_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-9-3-2165426903-1168750583-1031006359-282436966

Name     : DOMAIN\JBloggs
Database : SomeDatabase
Type     : WINDOWS_USER
SidHex   : {1, 5, 0, 0...}
Sid      : S-1-5-21-1039932387-1978834618-1912882637-2728

Exciting, isn't it?