NetSPI Blog

Decrypting MSSQL Database Link Server Passwords

Antti Rantasaari
March 5th, 2014

Extracting cleartext credentials from critical systems is always fun. While MSSQL server hashes local SQL credentials in the database, linked server credentials are stored encrypted. And if MSSQL can decrypt them, so can you using the PowerShell script released along with this blog. 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, this is just another reminder that unnecessary database links, database links with excessive privileges, and the use of SQL server authentication rather than integrated authentication can result in unnecessary risk. This blog should be interesting to database hackers and admins interested in learning more.

Linked Servers

Microsoft SQL Server allows users to create links to external data sources, typically to other MSSQL servers. When these links are created, they can be configured to use the current security context or static SQL server credentials. If SQL server credentials are used, the user account and password are saved to the database encrypted and thus they are stored in a reversible format. A one-way hash cannot be used, because the SQL server has to be able to access the cleartext credentials to authenticate to other servers. So, if the credentials are encrypted and not hashed, there must be a way for the SQL server to decrypt them prior to use. The remainder of this blog will focus on how that happens.

Linked Server Password Storage

MSSQL stores link server information, including the encrypted password, in master.sys.syslnklgns table. Specifically, the encrypted password is stored in the “pwdhash” column (even though it’s not a hash). Below is an example:

The master.sys.syslnklgns table cannot be accessed using a normal SQL connection, but rather a Dedicated Administrative Connection (DAC) is needed (more information about DAC at http://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx). Sysadmin privileges are needed to start a DAC connection, but as local administrator privileges are needed anyways, that shouldn’t be a problem. If local administrators don’t have sysadmin privileges you’ll just have to impersonate the MSSQL server account or local SYSTEM account. More details on this can be found on Scott’s blog at https://www.netspi.com/blog/entryid/133/sql-server-local-authorization-bypass.

MSSQL Encryption

Time to introduce some MSSQL encryption basics. To move ahead, access to the Service Master Key (SMK) is required (more information about SMK at http://technet.microsoft.com/en-us/library/ms189060.aspx). According to microsoft.com “The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key.” SMK is stored in master.sys.key_encryptions table and it can be identified by the key_id 102. SMK is encrypted using Windows Data Protection API (DPAPI) and there are two versions of it in the database; one encrypted as LocalMachine and the other in the context of CurrentUser (meaning the SQL Server service account here). We’ll choose the former to extract the key as LocalMachine encryption uses the Machinekey for encryption and it can be decrypted without impersonating the service account. Below is an example of what that looks like:

Additional entropy is added to strengthen the encryption but the entropy bytes can be found in the registry at HKLM:SOFTWAREMicrosoftMicrosoft SQL Server[instancename]SecurityEntropy. Once again, local administrator privileges are needed to access the registry key. The entropy is stored in the registry for each MSSQL instance as shown below:

After that (and removing some padding / metadata from the encrypted value) we can decrypt the SMK using DPAPI.

Decrypting Linked Server Passwords

Based on the length of the SMK (or the MSSQL version) we can determine the encryption algorithm: MSSQL 2012 uses AES, earlier versions use 3DES. In additional, the pwdhash value has to be parsed a bit to find the encrypted password. The first answer referring Pro T-SQL Programmer’s guide at http://stackoverflow.com/questions/2822592/how-to-get-compatibility-between-c-sharp-and-sql2k8-aes-encryption got me on the right track; even though the byte format didn’t seem to match exactly like detailed on the page, it wasn’t too hard to find the right bytes to encrypt. So now, using the SMK, it is possible to extract all of the link credentials (when SQL Server account is used, not Windows authentication) in cleartext.

Decrypting Linked Server Passwords with PowerShell – Get-MSSQLLinkPasswords.psm1

To automate the decryption of linked server credentials I wrote a PowerShell script called “Get-MSSQLLinkPasswords.psm1”. It can be download from GitHub here:
https://github.com/NetSPI/Powershell-Modules/blob/master/Get-MSSQLLinkPasswords.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.

  1. Identify all of the MSSQL instances on the server.
  2. Attempt to create a DAC connection to each instance.
  3. Select the encrypted linked server credentials from the “pwdhash” column of the “mas-ter.sys.syslnklgns” table for each instance.
  4. 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.
  5. Extract the entropy value from the registry location HKLM:SOFTWAREMicrosoftMicrosoft SQL Server[instancename]SecurityEntropy.
  6. Use the information to decrypt the SMK.
  7. The script determines the encryption algorithm (AES or 3DES) used to encrypt the SMK based on SQL Server version and SMK key length.
  8. Use the SMK to decrypt the linked server credentials.
  9. If successful, the script displays the cleartext linked server credentials. Below is an example of the end result:

I’ve tested the script with MSSQL 2005, 2008, 2012, 2008 Express, and 2012 Express. 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.

61
Leave a Reply

avatar
39 Comment threads
22 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
30 Comment authors
GeigerReginaldo SilvaWilliam SmithPaul Hewsonmung Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
newest oldest
Notify of
Ce@5ER
Guest
Ce@5ER

Very nice!

I’ll test the script and let you know of further details.

Thank you for this great initiative

blueboy
Guest
blueboy

Great script Antii, it works fantastic on stand alone servers!

However, it doesn’t work on a cluster. Are you able to get it to work on a cluster?

Thanks,

Pete

Jonni_Star
Guest
Jonni_Star

I have gotten this to work in a SQL 2008 R2 cluster.

I had to put the cluster name into the connection string and needs to be run on the active node.

You could automate this by getiting the cluster name in the registry inside the instance there is Cluster\ClusterName.

chanmm
Guest

Great job. Thank you

Kodak
Guest
Kodak

Excellent – thank you!

um110030
Guest
um110030

Great stuff here but when I run the script from a PS prompt it opens a DOS box and keeps looping over and over, dos window closes, another opens, over and over again, crl+c kill sit, I can’t see the output because the window is only open for a second….

Any ideas??

Jose Contreras
Guest

just rename the extension from psm1 to ps1

works like a charm !

um110030
Guest
um110030

Hi there again,

I still can’t get this to work!!! I confess I’m an Oracle DBA (Linux) normally but surely this can’t be so difficult!

I copy the script from the link.
I save it in notepad as Get-MSSQLLinkPasswords.psm1.
I open powershell ISE (as admin)
cd to the directory with the script
I run it by typing .\Get-MSSQLLinkPasswords.psm1

A new powershell window opens, flashes and closes, this continues until I ctl+c.

Can you please tell me what I am doing wrong?

Many thanks!

Valentyn
Guest
Valentyn

It works great. Do you know where SQL Server stores hash for Credentials (Security->Credentials).
Or maybe you know how to get password for Credentials as for Linked Servers.
Thanks in advance.

Rob
Guest
Rob

Superb. Thanks very much.

Rubens Junior
Guest
Rubens Junior

I had the same problem as “um110030”, but it worked from Powershell ISE.

Congrats for the Article.

Chrissy LeMaire
Guest

Hello Antti,
This is an amazing script! I would like to use portions of it within my own SQL Migration script, and give you full credit using

1. Your name
2. Your website & URL to the original script
3. The license information

BSD 3-Clause, however, requires that I place their entire copyright within the script. Would it be possible to just link the license instead?

Chrissy LeMaire
Guest

Hello again,
Thanks again for this code. I used it within two of my SQL Server migration scripts, and credited your work.

https://github.com/ctrlbold/sqlmigration

rafaelsrocha
Guest
rafaelsrocha

This script works for me (MSSQL 2012).

Thanks!!!

Oksana
Guest
Oksana

Could someone please post code for clustered servers please, We have few linked servers on SQL cluster with lost passwords, I couldn’t make it work on cluster boxes. Really appreciate your help. Script works perfect on standalone boxes. Thank you!

KeerthyP
Guest

thank you very much for the script. works perfectly.

BTW if anyone got PS error: script.psm1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Try this http://stackoverflow.com/questions/4037939/powershell-says-execution-of-scripts-is-disabled-on-this-system (need admin rights)

ddavis66
Guest
ddavis66

Hello Antti, I am trying to get your script to run,, I am executing this in PowerShell ISE, I have done what you suggested above. um10030, copy / paste the script into the script window in powershell_ise. Run the script (a little green play button at the top). Then type Get-MSSQLLinkPasswords in the PS prompt below the script window. The output I get: Get-MSSQLLinkPasswords : Error creating DAC connection: Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the… Read more »

ddavis66
Guest
ddavis66

Thanks for the info.. I have the correct Instance Name now and no longer getting the “Connection string is not valid” Still getting the following error message (see below) : I can still get a connection from an elevated DOS Prompt when running C:> sqlcmd -S “servername”\”instance name” 1> I can also connect by C:> sqlcmd -S “servername” , “tcp port” 1> Get-MSSQLLinkPasswords : Error creating DAC connection: Exception calling “Open” with “0” argument(s): “A network-related or instance-specific error occurred while establishing a connection to SQL Se rver. The server was not found or was not accessible. Verify that the… Read more »

ddavis66
Guest
ddavis66

Update: Finally getting the connection to work.. now seeing the following: Exception calling “ExecuteReader” with “0” argument(s): “Invalid object name ‘master.sys.syslnklgns’.” At line:102 char:30 + $Data=$Cmd.ExecuteReader <<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException Exception calling "Load" with "1" argument(s): "Value cannot be null. Parameter name: dataReader" At line:104 char:14 + $Dt.Load <<<< ($Data) + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException Thanks,

venkat
Guest
venkat

Thanks a lot ! This is a life saver !

mw
Guest
mw

Works great with one tiny exception… If your default datbaase isnt master it throws a wobbly, so teaking the connect string to include ;Initial Catalog=master solves the problem.

Thanks for great work on this

Firasath
Guest
Firasath

I downloaded the ps script, opened powershell in admin mode and navigated to the folder where script is downloaded. When i execute . ./Get-MSSQLLinkPasswords.psm1 , the script opens in noted.Why am i not seeing anything. Is there anything else i need to do other than downloading the file.

Joe
Guest
Joe

This is great, Thank you so much for this. I didn’t realize anyone else would of run into this issue. For everyone posting comments, read the comments above. PowerShell ISE is the easiest way to do it.

Start
Search – powershell
Right Click Power Shell ISE
Run As Administrator
Copy and paste the script to the top scripting window
Click the green Play button
On the bottom prompt type Get-MSSQLLinkPasswords
Enter

For any amateurs like I was with Power Shell, This is the easiest way to do it. Thanks again for an amazing post !!!

Steve Armistead
Guest
Steve Armistead

This is excellent work, and is very much appreciated. I thought at first that I’d encountered an anomaly when errors would be thrown but then results were returned. I found that a shut down instance on the server was causing the script to hiccup; uninstalling the otherwise unused instance resolved the problem.

Thanks again – this script saves me much time when migrating to a new batch of servers.

marty
Guest
marty

Hi,
SELECT * FROM sys.key_encryptions returns an empty resultset on our mssql 2005 instance.
Does this only work on a specific version?

Server Management
Guest

Nice Work…!

It’s works for me.

Thank you very much for the grate script. 🙂

Martin
Guest
Martin

thanks a million.
migrating from SQL 2005 to SQL 2008.

Martin
Guest
Martin

tried on another sql server and got this error message…

Exception calling “Unprotect” with “3” argument(s): “Value cannot be null.
Parameter name: encryptedData”
At C:\_powershell\Get-MSSQLLinkPasswords.ps1:83 char:76
+ $ServiceKey = [System.Security.Cryptography.ProtectedData]::Unprotect <<<< ($SmkBytes, $Entropy, 'LocalMachine')
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

Get-MSSQLLinkPasswords : Unknown key size
At line:1 char:23
+ Get-MSSQLLinkPasswords <<<<
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-MSSQLLinkPasswords

any ideas?

Geiger
Guest
Geiger

I received this error. Replying above.

Martin
Guest
Martin

inital catalog=master did it…

Bobby
Guest
Bobby

Great solution, and well written explanation of how the solution was derived. Much better than just having a script to run and not really understanding what it does and how it works.

Used this a few times now when working in businesses where colleagues have left without any documentation or secure password storage for Linked Servers.

Deepak
Guest
Deepak

running the function, am getting the below error:

PS C:\> Get-MSSQLLinkPasswords
Get-MSSQLLinkPasswords : Error creating DAC connection: Exception calling “Open” with “0” argument(s): “A connection was
successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error:
0 – The specified network name is no longer available.)”.Exception.Message
At line:1 char:1
+ Get-MSSQLLinkPasswords
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-MSSQLLinkPasswords

Mark Monnin
Guest
Mark Monnin

I had a few problems because of the server admin account not being a SQL Server admin account, so I wasn’t able to do the DAC connection programmatically. So I just copied the data from the select queries into some other temporary tables (select into) and had the script get the data from there.

BIDS IDE
Guest
BIDS IDE

Some of the password generated has just “########”, does it mean anything? It happened both for sql accounts and also domain accounts.

Thank you!

Calimero
Guest
Calimero

Hi!

I tried to run the script as the AD Administrator (which has been added to the login via SQL Management Studio) but it simply doesn’t display anything within powershell. It executes and jumps to next line and displays the PS prompt. I imported the module, set the exec policy to unrestricted, rebooted the machine – no effect.

A connection with “sqlcmd -S SERVERNAME\SQLINSTANCE” is possible.

System: Server 2012R2 with MS SQL 2012. Any hints?

Cheers

Calimero

mung
Guest
mung

Hi!! Antti Rantasaari: My windows server 2003R2 run SQL 2008R2 sofware. i had connected DAC by SSMS from windows xp run sql2008 client to server: file–>new–> Database engine quer(servername: admin:Serverx\serverx, login:sa). execute query : select * from sys.key_encryptions –> it’s showed ok . Went i past your script on power shell_ise and click button play and type bottom promt PS C:\Documents and Settings\Administrator>>>Get-MSSQLLinkPasswords and then showed to get error : Get-MSSQLLinkPasswords : Error creating DAC connection: Exception calling “Open” with “0” argument(s): “A connection was successfully established with the server, but then an error occurred during the login process. (provider:… Read more »

Paul Hewson
Guest
Paul Hewson

Hi Antti,

Great piece of work but the images are not showing.

Thanks,
Paul

William Smith
Guest
William Smith

To those who had trouble running this, I hit several of the problems discussed here. Here is what I did, and the results worked perfect for me. 1. You need to be at least a local administrator on the box. (you do not have to be a domain administrator) 2. Using SSIS, check your membership. I was already a member of ‘sysadmin’ role, but I also needed to be a member of ‘dbowner’ in the master database. 3. Open Windows PowerShell ISE. (this is an IDE tool, not just command line) 4. Paste the code from git into the new… Read more »

Reginaldo Silva
Guest
Reginaldo Silva

That’s Awesome, tks.

Geiger
Guest
Geiger

I too was getting the error below and figured out why… $ServiceKey = [System.Security.Cryptography.ProtectedData]::Unprotect <<<< ($SmkBytes, $Entropy… Exception calling "Unprotect" with "3" argument(s): At first I thought there was an issue with my version of the Unprotect function. However, it was simpler than that. The issue was that I had the wrong key in $SmkBytes $SmkBytes is set to the result of this query… SELECT substring(crypt_property,9,len(crypt_property)-8) FROM sys.key_encryptions WHERE key_id=102 and (thumbprint=0x03 or thumbprint=0x0300000001) However… when I ran that query outside of Powershell as DAC I could see that there were multiple records. The script was taking the first one… Read more »