Decryption of saved passwords in MS SQL Server

A long time ago, in a distant galaxy, the pre-previous administrator of your SQL Server set up a 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 can’t do it because no one knows the password from that account. Common situation?

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



But not everything is so simple. Firstly, this table is not accessible from a regular SQL connection, and is accessible only fromDedicated Administrative Connection . Significant restrictions are imposed 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 privileges, then there is a workaround - not to log in from your account, but from the MSSQL service account or even from LocalSystem.

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



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

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



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

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

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



If you do not want to run scripts incomprehensibly by someone on the production server, then the decryption itself can be performed without administrator rights, if you first pull out the three components using SQL Studio and regedit, and insert them into the script explicitly. The first step of decryption ( $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. Credentials stored in the database for executing commands ( etc.) on behalf of less privileged accounts than the MSSQL service are

also decrypted in a similar wayxp_cmdshell .

On the one hand, all this seems a blatant example of security through obscurity: if password decryption for connecting to linked servers is already 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 very good: to decrypt passwords, you need access to the server with local administrator rights, and if an attacker gained such access, he can already do whatever he wants with the server. Undesired privilege escalation is possible only if the password from some linked server is used for something important, for example, as the administrator password of the same server: ^)