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 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

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 According to “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 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:

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.

Leave a Reply

61 Comments on "Decrypting MSSQL Database Link Server Passwords"

newest oldest
Notify of

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?




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??

Jose Contreras

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!


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.


Superb. Thanks very much.

Rubens Junior

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

Congrats for the Article.

Chrissy LeMaire

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

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


This script works for me (MSSQL 2012).



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!


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

Try this (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 »
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.

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

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

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.


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

Server Management

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

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.


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

Thank you!



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?



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

Hi Antti,

Great piece of work but the images are not showing.


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

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 »