Decryption of saved passwords in MS SQL Server

A long time ago, in a distant galaxy, the previous administrator of your SQL Server set the linked server in it, using the created account with the generated password specially for this purpose. Now you need to do something with this link, for example, transfer it to another SQL Server; but just because it does not, because no one knows the password from that account. Common situation?

Although MSSQL does not store passwords for their accounts, but stores only their hashes, this will not work with linked servers, because in order to successfully authenticate, you need to have an open password in front of an external server. Passwords for linked master.sys.syslnklgns are stored in encrypted form in the master.sys.syslnklgns table:


')
But not everything is so simple. First, this table is not available from a normal SQL connection, but is only available from the Dedicated Administrative Connection . There are significant restrictions on the DAC: only a user with the sysadmin privilege can open the DAC, and only one DAC can be opened to one server at a time. If you have local administrator rights on the server, but you cannot log in to MSSQL with sysadmin rights, then the workaround is to log in not from under your account, but from under an account of the MSSQL service or even from under LocalSystem.

Secondly, despite the fact that the field with an encrypted password is called pwdhash - this is not any hash, but encrypted data. The decryption key is stored in the master.sys.key_encryptions system table:



This key is stored in two copies: the first ( thumbprint=0x01 ) allows using only from under the account of the MSSQL service, the second ( thumbprint=0x0300000001 ) - from under any account on the server. Please note that none of the stored keys are suitable for “offline decryption” of passwords outside the server, so that if an attacker manages to steal data from both these system tables, it will give him nothing.

Thirdly, the decryption key itself is encrypted, and the “key key” is stored in the registry in HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$InstanceName\Security\Entropy :



To read this value from the registry, again, local administrator rights are required on the server.

To get all three components and decrypt the saved passwords, the author has created a convenient PowerShell script .

If you run it from under the local administrator account on the server, it will delight you with something like this:



If you don’t want to run scripts on a production server, you can perform the decoding itself without administrator rights, if you first pull out the three components using SQL Studio and regedit, and paste them into the script explicitly. The first decryption step ( $ServiceKey = [System.Security.Cryptography.ProtectedData]::Unprotect($SmkBytes, $Entropy, 'LocalMachine') ) must be performed on the server, but the second ( $Decrypt = $Decryptor.​CreateDecryptor($ServiceKey,​$Logins.iv) and subsequent work with CryptoStream) can be performed offline.

The credentials saved in the database for executing commands ( xp_cmdshell , etc.) on behalf of less privileged accounts than the MSSQL service are also decrypted in the same way .

On the one hand, all this seems to be a blatant example of security through obscurity: if the decryption of passwords for a connection with linked servers has already been implemented in MSSQL, then why is it not possible to show these passwords to a forgetful administrator? On the other hand, from a security point of view, everything is quite good: to decrypt passwords, you need access to the server with local administrator privileges, and if an attacker has received such access, then he can already do whatever he wants with the server. Unwanted privilege escalation is possible only if the password from some linked server is used additionally for something important, for example, as the administrator password for the same server: ^)