Decrypting MSSQL Database Link Server Passwords
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.
- Identify all of the MSSQL instances on the server.
- Attempt to create a DAC connection to each instance.
- Select the encrypted linked server credentials from the “pwdhash” column of the “mas-ter.sys.syslnklgns” 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 linked server credentials.
- 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.
Very nice!
I’ll test the script and let you know of further details.
Thank you for this great initiative
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
I haven’t tried it on a cluster as I don’t have access to one at the moment. To be honest, I didn’t even think about that when I wrote the script.
But I’ll update the script later if I’ll get it to work on a cluster.
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.
Great job. Thank you
Excellent – thank you!
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??
um110030, I really don’t know why the script would open DOS prompts; I don’t think it calls any DOS commands. Did you run the script as administrator (if UAC is enabled), have sysadmin privileges to the DB, admin privileges on the server, and have Powershell version v2 or higher (it probably doesn’t work on PSv1) ?
Antti Rantasaari – Thanks for the quick reply. – UAC is set to never notify (but I tried with RAA anyway, same result) – I’m a member of the administrators group at server level – I’m a member of the sysadmin group at instance level, with pubic set per database. – Powershell is version 3 Perhaps it was to do with how I saved the script, copied it into notepad, saved as Get-MSSQLLinkPasswords.psm1. Initially the script kept opening in notepad when running it but I changed the association so it opened with the PS binary located in this path C:\Windows\System32\WindowsPowerShell\v1.0… Read more »
Maybe try copy/pasting the whole script to PowerShell or powershell_ise. Then call the Get-MSSQLLinkPasswords function. Double clicking the script wouldn’t do anything as it’s just a function definition; the script on Github doesn’t actually call the function.
just rename the extension from psm1 to ps1
works like a charm !
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!
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.
Alternatively, save the script somewhere and run the following command in PowerShell:
import-module Get-MSSQLLinkPasswords.psm1
To import the module you’ll have to have the execution policy set to unrestricted; you can do it for the current PowerShell session by running the following command:
Set-ExecutionPolicy unrestricted -Scope process
Then you can call the function Get-MSSQLLinkPasswords.
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.
Hi Valentyn,
Normal SQL passwords are hashed, not encrypted. You can’t decrypt the passwords the same way link passwords can be decrypted.
Thanks for a quick response. But I wasn’t talking about SQL Users. In this screenshot I show which credentials I meant. http://owely.com/22I7GJ
Sorry, I misunderstood. I don’t know how the Credentials are stored. I’ll take a look at it when I have some time.
Hi Valentyn,
I was able to figure out how to extract cleartext passwords for Credentials. I’ll post a blog and an extraction script shortly.
Thanks for the suggestion!
Superb. Thanks very much.
I had the same problem as “um110030”, but it worked from Powershell ISE.
Congrats for the Article.
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?
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
This script works for me (MSSQL 2012).
Thanks!!!
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!
My apologies for the very late reply. Can you try what Jonni_Star suggested above? I don’t have a SQL cluster set up for testing so I can’t really update the code.
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)
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 »
The error states “Connection string is not valid”. You could write out the connection string in the script to see if the script constructs the connection string erroneously.
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 »
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,
Thanks a lot ! This is a life saver !
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
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.
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 !!!
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.
Hi,
SELECT * FROM sys.key_encryptions returns an empty resultset on our mssql 2005 instance.
Does this only work on a specific version?
Did you try running “select * from sys.key_encryptions” manually on master database? It looks like there is a little error in the script, line 74 runs that select query against the default database and not necessarily master db. You can fix it by changing the query to “..from master.sys.key_encryptions..”
Let me know if that helps.
I haven’t tried the script yet, but OMG, yep, that was my mistake… Didn’t query master but the currently selected DB. THX!
Nice Work…!
It’s works for me.
Thank you very much for the grate script. 🙂
thanks a million.
migrating from SQL 2005 to SQL 2008.
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?
I received this error. Replying above.
inital catalog=master did it…
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.
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
Try creating a DAC connection via SMSS or SQLCMD to make sure that you can open a DAC connection.
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.
Some of the password generated has just “########”, does it mean anything? It happened both for sql accounts and also domain accounts.
Thank you!
I’m not sure, I haven’t seen that before. Let us know if you can figure it out.
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
Pretty hard to say what the problem is. You could start by running the SQL queries from the script to see if they return any data.
Thx for your reply.
Running the SQL query “SELECT substring(crypt_property,9,len(crypt_property)..[…]” displays the master key.
When I try to run the SQL cmd
SELECT sysservers.srvname,syslnklgns.name,substring(syslnklgns.pwdhash,5,$ivlen) iv,substring(syslnklgns.pwdhash,$($ivlen+5),
len(syslnklgns.pwdhash)-$($ivlen+4)) pass FROM master.sys.syslnklgns inner join master.sys.sysservers on syslnklgns.srvid=sysservers.srvid WHERE len(pwdhash)>0
it results in an error (Invalid $ivlen pseudo column)
$ivlen is a Powershell variable so it won’t work by itself. Try replacing it with 16.
Already thought so… but it didn’t work either.
Tried it on 4 different servers, each with SQL 2012, 2012R2 and mixed user accounts for SQL (sa, user1, user2, SERVER\Administrator, etc.). The result is always the same. 🙁
The PowerShell module does not decrypt SQL account passwords, it decrypts credentials that are used to access linked servers. If the SQL query does not return any data, there are no links with saved credentials.
Oooooops… 😀
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 »
Hi Antti,
Great piece of work but the images are not showing.
Thanks,
Paul
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 »
That’s Awesome, tks.
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 »