A while ago I posted a blog on how to decrypt SQL Server link passwords (https://blog.netspi.com/decrypting-mssql-database-link-server-passwords/). By using the same technique it is possible to decrypt passwords for SQL Server Credentials as well. I modified the previously released password decryption script a little, namely by just changing the location where the encrypted passwords are stored, and released an updated PowerShell script for Credential decryption.
Similar remarks as with link password decryption… From the offensive point of view, this is pretty far into post exploitation as sysadmin privileges are needed on the SQL server and local administrator privileges are needed on the Windows server. From the defensive point of view, I guess this would be just another reminder that there is a way to disclose most saved passwords. So do not leave unnecessary credentials on database servers and do not grant excessive privileges for credentials used to access external resources.
SQL Server Credentials
Microsoft SQL Server allows users to add Credentials to a database. The credentials, typically Windows usernames and passwords, can be used to access resources outside SQL Server. A single credential can be used by multiple SQL logins for external access.
A simple example of credential use is the SQL Server proxy account. When xp_cmdshell is executed, by default it uses the permissions of the SQL Server service account. However, by configuring a proxy account for the server, it is possible to set xp_cmdshell to use a least privileged account for OS access rather than (quite often excessive) service account permissions.
When credentials are added to a SQL Server, passwords have to be saved to the database using reversible encryption to allow for proper use of the credentials. It is possible to decrypt saved credentials password as explained in this blog.
Credential Password Storage
MSSQL stores credential passwords to the master.sys.sysobjvalues table. I was able to figure out the location of the encrypted passwords after looking at the definition of the master.sys.credentials view using the following query:
Microsoft gives a pretty vague description for the table: “Exists in every database. Contains a row for each general value property of an entity.” Master.sys.sysobjvalues has a lot of data in it, but credential information appears to have valueclass 28. And encrypted passwords are stored in imageval column with valclass=28 and valnum=2. I could not find documentation about valclass and valnum but those values seemed to work on my test systems.
Depending on the version of the MSSQL server, the credential passwords are encrypted using AES (MSSQL 2012+) or 3DES (MSSQL 2008 and older). Passwords stored in sys.sysobjvalues imageval column must be parsed a little prior to decryption (luckily exactly the same way as link server passwords). After the parsing credential passwords can be decrypted using the SMK.
Decrypting Credential Passwords with PowerShell – Get-MSSQLCredentialPasswords.psm1
The script must be run locally on the MSSQL server (as DPAPI requires access to the local machine key). The user executing the script must also have sysadmin access to all the database instances (for the DAC connection) and local admin privileges on the Windows server (to access the entropy bytes in registry). In addition, if UAC is enabled, the script must be ran as an administrator. Below is a summary of the process used by the script.
Identify all of the MSSQL instances on the server.
Attempt to create a DAC connection to each instance.
Select the encrypted credential passwords from the “imageval” column of the “master.sys.sysobjvalues” table for each instance.
Select the encrypted Service Master Key (SMK) from the “master.sys.key_encryptions” table of each instance where the “key_id” column is equal to 102. Select the version that has been encrypted as LocalMachine based on the “thumbprint” column.
Extract the entropy value from the registry location HKLM:\SOFTWAREMicrosoftMicrosoft SQL Server[instancename]SecurityEntropy.
Use the information to decrypt the SMK.
The script determines the encryption algorithm (AES or 3DES) used to encrypt the SMK based on SQL Server version and SMK key length.
Use the SMK to decrypt the credential passwords.
If successful, the script displays the cleartext credential passwords. Below is an example of the end result:
I’ve tested the script with MSSQL 2008 and 2012. There might be some bugs, but it appears to work reliably. Please let me know if you notice any errors or if I did not account for certain situations etc.
PTaaS is NetSPI’s delivery model for penetration testing. It enables customers to simplify the scoping of new engagements, view their testing results in real time, orchestrate faster remediation, perform always-on continuous testing, and more - all through the Resolve™ vulnerability management and orchestration platform.
We help organizations defend against adversaries by being the best at simulating real-world, sophisticated adversaries with the products, services, and training we provide. We know how attackers think and operate, allowing us to help our customers better defend against the threats they face daily.
At NetSPI, we believe that there is simply no replacement for human-led manual deep dive testing. Our Resolve platform delivers automation to ensure our people spend time looking for the critical vulnerabilities that tools miss. We provide automated and manual testing of all aspects of an organization’s entire attack surface, including external and internal network, application, cloud, and physical security.
Our proven methodology ensures that the client experience and our findings aren’t only as good as the latest tester assigned to your project. That consistency gives our customers assurance that if vulnerabilities exist, we will find them.
Is your organization prepared for a ransomware attack? Explore our Ransomware Attack Simulation service.