Forcing access into SQL Server without a restart

As a DBA it can be difficult to gain access to a SQL Server instance you are authorised (and indeed required) to support.

Users can be hard to identify from outside the instance.
Users can be hard to get a hold of.
Users can be unwilling to cooperate.
Users can be completely absent where a vendor or application has done the install.

And as time goes on Microsoft beefs up the default security in each version of SQL Server released.

SQL Server 2005 and below granted sysadmin access by default to BUILTIN\Administrators.
SQL Server 2008 granted sysadmin access only to NT AUTHORITY\SYSTEM which could be used impersonated via PsExec.
In SQL Server 2012 and above neither has sysadmin access by default.

While it's possible to stop and restart and instance in single-user mode with the -m switch it can be difficult to get business approval for. An outage to a system with unknown contents? Please.

It is also possible to re-point the SQL Agent service executable to spawn a sqlcmd session; but this still requires a "potential" outage when it's restarted and any executing jobs are cancelled (for example critical SSIS ETL packages).

It turns out that if you're a Windows Administrator on the server then you can use a PowerShell v2 script to obtain access with no outage and no known risk. It does this by duplicating the login token of the SQL Server service even if it's running under an AD service account, virtual account, or protected with a per-service SID

Once impersonated you'll be unable to do much except interact with the SQL Server service through .NET objects.

Process

The code that does the heavy lifting is by Rune Mariboe but I forked it to fix something and otherwise clean it up into a module called HackSql. You can copy that into your PowerShell Modules folder and gain access to everything on the server by running an Administrator sessions with:

Import-Module HackSql
Start-HackSql

What it's doing is iterating the SQL Server services below and adding you to the sysadmin role wherever you're missing by using Rune's code and .NET:

$userName = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name

$services = Get-Service | Where { ($_.Name -eq 'MSSQLSERVER' -or $_.Name -like 'MSSQL$*') -and $_.Status -eq "Running" }
foreach ($service in $services) {
    if ($service.Name -eq "MSSQLSERVER") {
        $sqlName = ".\"
    } else {
        $sqlName = ".\$($service.Name.Substring(6))"
    }
    
    Write-Host "Attempting $sqlName"
    $serviceProcess = Get-WmiObject -Class Win32_Service -Filter "Name = '$($service.Name)'"

    Invoke-TokenManipulation -ProcessId $serviceProcess.ProcessID -ImpersonateUser | Out-Null
    $impersonatedUser = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name
    Write-Host "Service $($service.Name) on PID $($serviceProcess.ProcessID) will connect to $sqlName as $impersonatedUser"

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlName;Trusted_Connection=True")
    $sqlConnection.Open()
    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand("If Not Exists (Select Top 1 0 From sys.server_principals Where name = '$userName')
Begin
    Create Login [$userName] From Windows
End

If Not Exists (Select Top 1 0 From master.sys.server_principals sp Join master.sys.server_role_members srp On sp.principal_id = srp.member_principal_id Join master.sys.server_principals spr On srp.role_principal_id = spr.principal_id Where sp.name = '$userName' And spr.name = 'sysadmin')
Begin
    Exec sp_addsrvrolemember '$userName', 'sysadmin'
End", $sqlConnection)
    $sqlCommand.ExecuteNonQuery() | Out-Null
    $sqlConnection.Close()
    Invoke-TokenManipulation -RevToSelf | Out-Null
}

On many old and current setups with default accounts this will work, but it can fail if service accounts are not members of the sysadmin role. If this is the case then you'll need to take down the server in order to get access using Reset-SqlAdmin in dbatools.

Process

Share on: