SMO "leaks" SQL login passwords (in memory) and (some) SecureStrings in clear-text

Updated 2018-11-04: Expanded test code, renamed the title of the post so it's clearer.

SMO (SQL Server Management Objects) are the .NET classes underpinning SSMS (SQL Server Management Studio) and all good PowerShell that interfaces with SQL Server.

SMO connects to SQL Server using the ADO.NET SQLClient library which has 13+ years of features which help mask the passwords you pass in for SQL Authentication. SMO bypasses some of those features to often leak the passwords in clear-text.

Even where it would normally be hidden.
Even where you use Persist Security Info introduced in 2005.
Even where you use System.Security.SecureString introduced in 2012.
Though thankfully not where you use System.Data.SqlClient.SqlCredential also introduced in 2012. However… there's some caveats here too.

We'll prove it through repeatable tests that can be used to track if Microsoft fix the problem or not.

Digging into the Server object

The head of SMO is the Microsoft.SqlServer.Management.Smo.Server class which has two different constructors.

A server instance string
Or a Microsoft.SqlServer.Management.Common.ServerConnection object which has its own constructors:
- A server instance string
- A server instance string, user name string, and password in clear-text
- A server instance string, user name string, and password in System.Security.SecureString
- A System.Data.SqlClient.SqlConnection object which has its' own constructors.
  - A connection string
  - A connection string and a System.Data.SqlClient.SqlCredential

Although it's not obvious in the above diagram you can also pass a SqlConnection directly to Server. This is because it gets cast to a ServerConnection along the way.

Regardless of however you construct the Server object you end up with the same set of nested objects:

The Server object
- Which has a ConnectionContext property which is a ServerConnection object
  - Which has a SqlConnectionObject property which is a SqlConnection object

In code it just looks like this:

$smo = New-Object Microsoft.SqlServer.Management.Smo.Server('DAC1N1')
$smo.ConnectionContext
$smo.ConnectionContext.SqlConnectionObject

SMO internal structure

Writing our initial test

For the sake of demonstration I'll be using raw ADO.NET here but it would all be done much cleaner with equivalent DbSmo commands. Furthermore, the test environment is as follows:

A SQL Server 2012 installation (I picked one node built by New-OftenOnLab).
Which includes the SqlServer module and the current Pester module.

Pester tests are just a bunch of commands in a file (LeakySmo.Tests.ps1) which are executed by running Invoke-Pester.

The basic structure of the tests will be:

Confirm the environment is set up correctly with a SQL Login and Mixed Mode authentication.
Clear the ERRORLOG.
Check for clear-text passwords that come out of various ways of building the SqlClient and SMO objects.
After each test check the ERRORLOG for failed logins (which is a problem I've previously seen but cannot reproduce at the moment).

Let's get started with some tests that confirm our basic assumptions.

A correct user name and password works.
An incorrect user name and password fails.
After you open a SqlConnection it hides any password that existed in the connection string. I hadn't mentioned that yet but it does.

Describe 'SMO leaks passwords in clear-text' {
    Import-Module SqlServer

    $serverInstance = 'DAC1N1'
    $userId = 'MicrosoftBugs'
    $password = 'MakeMeSoSad!'

    # Set
    $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)
    $query = "
        IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = '$userId')
            CREATE LOGIN [MicrosoftBugs] WITH PASSWORD = '$password';
    "
    $smo.ConnectionContext.ExecuteNonQuery($query)

    # Make sure that SQL Server is configured correctly
    $query = "
        DECLARE @Output TABLE (AuditLevel SYSNAME, Data INT);
        INSERT @Output
        EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode';
        INSERT @Output
        EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel';

        IF EXISTS (SELECT * FROM @Output WHERE Data < 2)
            RAISERROR('Please make sure failed logins are written to the ERRORLOG and SQL Server Authentication is enabled', 16, 1);
    "
    $smo.ConnectionContext.ExecuteNonQuery($query)

    # We need to clear out the ERRORLOG before each test
    BeforeEach {
        $smo.ConnectionContext.ExecuteNonQuery('EXEC sys.sp_cycle_errorlog;') | Out-Null
    }

    function Assert-LoginSuccess {
        $smo.ReadErrorLog() | Where-Object { $_.Text -like '*login failed for user*' } | Measure-Object | Select-Object -ExpandProperty Count | Should -Be 0
    }

    function Assert-LoginFailure {
        $smo.ReadErrorLog() | Where-Object { $_.Text -like '*login failed for user*' } | Measure-Object | Select-Object -ExpandProperty Count | Should -BeGreaterThan 0
    }

    It 'SqlConnection succeeds with a user name and password' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';")
        $sqlConnection.Open()
        Assert-LoginSuccess
    }

    It 'SqlConnection fails with an incorrect user name and password and writes a failed login to the ERRORLOG' {
        {
            $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='Wrong$password';")
            $sqlConnection.Open()
        } | Should -Throw
        Assert-LoginFailure
    }

    It 'SqlConnection accepts a clear-text password but hides it once opened' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';")
        $sqlConnection.ConnectionString | Should -Match $password
        $sqlConnection.Open()
        $sqlConnection.ConnectionString | Should -Not -Match $password
    }

    # Insert more tests here
}

And just to confirm it works so far…

Pester tests work so far

Now to get to the bugs.

Leaking passwords when you do nothing

Although SqlConnection will hide the password after a connection is opened, remember from earlier that SMO doesn't use it directly and instead wraps it in a ServerConnection object (under the ConnectionContext property). Let's now test the connection strings in that object.

    It 'Server objects built with a SqlConnection succeeds' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';")
        $testSmo = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)
        # Do some work
        $testSmo.Databases | Out-Null
        Assert-LoginSuccess
    }

    It 'Server objects built with a SqlConnection shouldn''t leak the password in the SqlConnection' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';")
        $testSmo = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)
        # Do some work
        $testSmo.Databases | Out-Null
        # Checks for an exposed password in the underlying object
        $testSmo.ConnectionContext.SqlConnectionObject.ConnectionString | Should -Not -Match $password
        Assert-LoginSuccess
    }

    It 'Server objects built with a SqlConnection shouldn''t leak the password in the ConnectionContext' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';")
        $testSmo = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)
        # Do some work
        $testSmo.Databases | Out-Null
        # Check for an exposed password in ConnectionContext
        $testSmo.ConnectionContext.ConnectionString | Should -Not -Match $password
        Assert-LoginSuccess
    }

Pester tests start to fail

Leaking passwords despite Persist Security Info

Since 2005 connection strings have a Persist Security Info property which is set to False by default, and this is what causes the password to be hidden after the connection is made.

Let's confirm it's working properly just in case it's somehow related.

    It 'SqlConnection Persist Security Info=True accepts a clear-text password and doesn''t hide it once opened' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';Persist Security Info=True;")
        $sqlConnection.ConnectionString | Should -Match $password
        $sqlConnection.Open()
        $sqlConnection.ConnectionString | Should -Match $password
    }

    It 'SqlConnection Persist Security Info=False accepts a clear-text password and hides it once opened' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';Persist Security Info=False;")
        $sqlConnection.ConnectionString | Should -Match $password
        $sqlConnection.Open()
        $sqlConnection.ConnectionString | Should -Not -Match $password
    }

    It 'Server objects built with a SqlConnection Persist Security Info=False shouldn''t leak the password in the ConnectionContext' {
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;User Id=$userId;Password='$password';Persist Security Info=False;")
        $testSmo = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)
        # Do some work
        $testSmo.Databases | Out-Null
        # Check for an exposed password in ConnectionContext
        $testSmo.ConnectionContext.ConnectionString | Should -Not -Match $password
        Assert-LoginSuccess
    }

Here's the output of those tests.

Persist Security Info doesn't improve the situation

SqlCredential and SecureString

Let's move onto the more interesting stuff. We know that the password leaks but since 2012 a new feature has been added to prevent this, SqlCredential, which is a combination of a clear-text User Id and a SecureString password.

Server doesn't have a constructor that accepts either of these but going back to our earlier map it does accept a ServerConnection which can be built with a SecureString password, or with a SqlConnection which in turn can be built with a full SqlCredential.

Let's test each of those in turn.

    It 'SqlConnection objects built with a SqlCredential shouldn''t leak the password' {
        $secureString = ConvertTo-SecureString $password -AsPlainText -Force
        $secureString.MakeReadOnly()
        $sqlCredential = New-Object System.Data.SqlClient.SqlCredential($userId, $secureString)
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;", $sqlCredential)
        $sqlConnection.ConnectionString | Should -Not -Match $password
        $sqlConnection.Open()
        $sqlConnection.ConnectionString | Should -Not -Match $password
        Assert-LoginSuccess
    }

    It 'Server objects built with a SqlCredential shouldn''t leak the password in the ConnectionContext' {
        $secureString = ConvertTo-SecureString $password -AsPlainText -Force
        $secureString.MakeReadOnly()
        $sqlCredential = New-Object System.Data.SqlClient.SqlCredential($userId, $secureString)
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$serverInstance;", $sqlCredential)
        $testSmo = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlConnection)
        # Do some work
        $testSmo.Databases | Out-Null
        # Check for an exposed password in ConnectionContext
        $testSmo.ConnectionContext.ConnectionString | Should -Not -Match $password
        Assert-LoginSuccess
    }

    It 'Server objects built with a ServerConnection built with a SecureString shouldn''t leak the password in the ConnectionContext' {
        $secureString = ConvertTo-SecureString $password -AsPlainText -Force
        $secureString.MakeReadOnly()
        $serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverInstance, $userId, $secureString)
        $testSmo = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)
        # Do some work
        $testSmo.Databases | Out-Null
        # Check for an exposed password in ConnectionContext
        $testSmo.ConnectionContext.ConnectionString | Should -Not -Match $password
        Assert-LoginSuccess
    }

Using a SqlCredential does protect the password. Using a SecureString in the ServerConnection though doesn't.

SqlCredential doesn't leak the password but ServerConnection does

More about SqlCredential

So aside from leaking passwords where you don't use a SqlCredential, and leaking passwords where you use a SecureString, you can just use that SqlCredential, right?

I was investigating that last year when I observed a weird issue where SMO would generate a lot of failed logins with no username to servers when iterating certain properties (specifically the PageVerify setting of a database in an AG - if you can believe it). I raised that on UserVoice but aside from a now deleted comment acknowledging there were issues it didn't progress further.

Weirdly though upon going back and trying to reproduce the behaviour with my provided tests I can no longer make it happen. It's possible it was only with a certain configuration I can no longer recreate.

Anyway, the full test script for the above can be found here. Microsoft could fix each test so it passes as that would be consistent with how SQLClient works.