14

When we logged into SQL Management Studio(using Server Name, Login and Password) with checked "Remember Password". I need to know, where it save in PC.

I need to format my PC. And when we install SQL Management Studio, then I will lose my all credentials which I saved. That's why I need to get that files for backup where it save.

9 Answers 9

4

It would be helpful to know what version of SQL Server and what OS you're running SSMS on. That being said, for SQL Server 2008, it's stored in the SqlStudio.bin file found:

%appdata%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

It's my understanding that there are a lot of other settings stored in here and that simply moving that file somewhere, may or may not work for you.

1
  • Tried to copy this file to another machine with newer SSMS version installed. The list of recent servers was populated an well as logins, but not passwords (these are encrypted using Windows account). Apr 28, 2017 at 18:46
9

The Answer is a simple version based on previous answers in this post with some necessary fixes.

Assuming you are running SQL 2008 of later

Go to Microsoft SQL Server Management Studio, right click on any of the server that you have already connected, click "Register" and select the server, password should be populated already if you have this server saved password. Then click "Save" Now go to Main Menu -> View -> Registered Servers, you will see the server you just registered, now right click on it and Click Tasks -> Export, specify a file name and uncheck "Do not include user name and passwords in export file", the exported server will have an extension like: ".regsrvr" now by using the following script you will see the connection string decrypted:

param(
    [Parameter(Mandatory=$true)]
    [string] $FileName
)

Add-Type -AssemblyName System.Security
$ErrorActionPreference = 'Stop'

function Unprotect-String([string] $base64String)
{
    return [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64String), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

$document = [xml] (Get-Content $FileName)
$nsm = New-Object 'System.Xml.XmlNamespaceManager' ($document.NameTable)
$nsm.AddNamespace('rs', 'http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08')

$attr = $document.DocumentElement.GetAttribute('plainText')
if ($attr -ne '' -and $Operation -ieq 'Decrypt')
{    
    throw "The file does not contain encrypted passwords."  
}

$servers = $document.SelectNodes("//rs:RegisteredServer", $nsm)

foreach ($server in $servers)
{
    $connString = $server.ConnectionStringWithEncryptedPassword.InnerText
    echo ""
    echo "Encrypted Connection String:"
    echo $connString
    echo ""
    if ($connString -inotmatch 'password="?([^";]+)"?') {continue}
    $password = $Matches[1]

    $password = Unprotect-String $password  
    echo ""
    echo "Decrypted Connection String:"
    $connString = $connString -ireplace 'password="?([^";]+)"?', "password=`"$password`""
    echo $connString
    echo ""
}

How to use the script:

  • Save the content of the script as DecryptConnString.ps1
  • Open powershell
  • Type: ./DecryptConnString.ps1 -FileName 'prod.regsrvr'
8

First you need to register the servers in the SSMS. Either right click a server in Object Explorer and select Register or right click Local Server Groups, select New Server Registration and select the server name. The server password will be filled if they were remembered before. Then export the servers as per @mrdenny answer.

Now comes the tricky part. You need to re-encrypt the passwords under the user profile on the target machine. I have prepared a PowerShell script which can do that.

param(
    [Parameter(Mandatory=$true)]
    [string] $FileName,
    [Parameter(Mandatory=$true)][ValidateSet('Decrypt', 'Encrypt')]
    [string] $Operation
)

$ErrorActionPreference = 'Stop'

function Protect-String([string] $clearText)
{
    return [System.Convert]::ToBase64String([System.Security.Cryptography.ProtectedData]::Protect([System.Text.Encoding]::Unicode.GetBytes($clearText), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

function Unprotect-String([string] $base64String)
{
    return [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64String), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

$document = [xml] (Get-Content $FileName)
$nsm = New-Object 'System.Xml.XmlNamespaceManager' ($document.NameTable)
$nsm.AddNamespace('rs', 'http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08')

$attr = $document.DocumentElement.GetAttribute('plainText')
if ($attr -eq '' -and $Operation -ieq 'Encrypt')
{
    throw "The file does not contain plaintext passwords."
}
if ($attr -ne '' -and $Operation -ieq 'Decrypt')
{
    throw "The file does not contain encrypted passwords."
}

$servers = $document.SelectNodes("//rs:RegisteredServer", $nsm)
foreach ($server in $servers)
{
    $connString = $server.ConnectionStringWithEncryptedPassword.InnerText
    if ($connString -inotmatch 'password="([^"]+)"') {continue}
    $password = $Matches[1]


    if ($Operation -ieq 'Decrypt')
    {
        $password = Unprotect-String $password   
    }
    if ($Operation -ieq 'Encrypt')
    {
        $password = Protect-String $password
    }
    $connString = $connString -ireplace 'password="([^"]+)"', "password=`"$password`""
    $server.ConnectionStringWithEncryptedPassword.InnerText = $connString
}

if ($Operation -ieq 'Decrypt')
{
    $document.DocumentElement.SetAttribute('plainText', 'true')
} 
else 
{
    $document.DocumentElement.RemoveAttribute('plainText')
}
$document.Save($FileName)

On the source machine run .\Move-SqlRegisteredServers.ps1 -FileName 'Your.regsrvr' -Operation Decrypt. This will replace the encrypted passwords with plain text.

On the target machine run .\Move-SqlRegisteredServers.ps1 -FileName 'Your.regsrvr' -Operation Encrypt. This will encrypt the passwords again using the new key.

Now you can import the Your.regsrvr file into SSMS and have your servers together with saved credentials.

3
  • 2
    I needed to add this following the param(...) block to make the namespace System.Security.Cryptography.ProtectedData available: [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null
    – agrath
    Oct 30, 2017 at 2:03
  • For SSMS 2017 (17.9) this does not work. But I was able to use your method Unprotect-String to decode my password.
    – Alireza
    Oct 23, 2018 at 1:20
  • recent SSMS uses a bit different format of the connection string regex, something like this 'password=[^;]+?;', "password=$password;". the rest worked like a charm, thanks a lot! Aug 19, 2019 at 11:10
6

Assuming you are running SQL 2008 or higher, in the Registered Servers window right click on the folder under "DAtabase Engine" and select Tasks then Export. Specify a file and uncheck the "Do not include user names and passwords in the export file" checkbox. Click OK. Safe this file. When you rebuild your machine import the file and you'll have everything that was saved in that list.

1
  • The exported file can only be imported under the same user profile. Tried to import on another machine and got an error because the passwords are encrypted. Apr 28, 2017 at 18:48
3

It's certainly not saved in plain text. If you don't know the password, you should just reset it on the server. Since this is a site for Systems Administrators and you definitely read the faq, I assume you're the administrator of the server and can do this no problem.

2

For SSMS v18 you can find it here, and this time it's human readable and editable:

%appdata%\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml

The passwords are not stored in the file

1
  • 1
    I found <Password/> in the xml but nothing after it. The next lines are:<Instance>myPC\MY_MSSQLSERVER</Instance> <UserName>sa</UserName> <ServerType>lots of numbers and lettters</ServerType> <AuthenticationMethod>1</AuthenticationMethod> Apr 21, 2021 at 2:36
1

Small tweak of Dark Daskin's answer to make it compatible with SSMS 2017 (and new patch of SSMS2014):

param(
    [Parameter(Mandatory=$true)]
    [string] $FileName,
    [Parameter(Mandatory=$true)][ValidateSet('Decrypt', 'Encrypt')]
    [string] $Operation
)

[System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null

$ErrorActionPreference = 'Stop'

function Protect-String([string] $clearText)
{
    return [System.Convert]::ToBase64String([System.Security.Cryptography.ProtectedData]::Protect([System.Text.Encoding]::Unicode.GetBytes($clearText), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

function Unprotect-String([string] $base64String)
{
    return [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64String), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

$document = [xml] (Get-Content $FileName)
$nsm = New-Object 'System.Xml.XmlNamespaceManager' ($document.NameTable)
$nsm.AddNamespace('rs', 'http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08')

$attr = $document.DocumentElement.GetAttribute('plainText')
if ($attr -eq '' -and $Operation -ieq 'Encrypt')
{
    throw "The file does not contain plaintext passwords."
}
if ($attr -ne '' -and $Operation -ieq 'Decrypt')
{
    throw "The file does not contain encrypted passwords."
}

$servers = $document.SelectNodes("//rs:RegisteredServer", $nsm)
foreach ($server in $servers)
{
    $connString = $server.ConnectionStringWithEncryptedPassword.InnerText
    if ($connString -inotmatch 'password="?([^";<]+)"?') {continue}
    $password = $Matches[1]


    if ($Operation -ieq 'Decrypt')
    {
        $password = Unprotect-String $password   
    }
    if ($Operation -ieq 'Encrypt')
    {
        $password = Protect-String $password
    }
    $connString = $connString -ireplace 'password="?([^";<]+)"?', "password=`"$password`""
    $server.ConnectionStringWithEncryptedPassword.InnerText = $connString
}

if ($Operation -ieq 'Decrypt')
{
    $document.DocumentElement.SetAttribute('plainText', 'true')
} 
else 
{
    $document.DocumentElement.RemoveAttribute('plainText')
}
$document.Save($FileName)
1

This is working on SSMS 18, I added additional parameter for output file and I was able to move my credentials to severl machines. You need to allow PowerShell execution save this as file with name psREGSR.ps1

Run this on the source PC: .\psREGSRV.ps1 -SourceFileName 'path to Original file on the sourece PC .regsrvr' -OutFileName 'path to Decrypted_File.regsrvr' -Operation Decrypt

Copy the Decrypted_File.regsrvr to the Target PC and run this .\psREGSRV.ps1 -SourceFileName 'path to Decrypted_File.regsrvr' -OutFileName 'path to Encrypted_File.regsrvr' -Operation Encrypt

The outfile for the second command is the one you import in the target pc

PSCode

param(
    [Parameter(Mandatory=$true)]
    [string] $SourceFileName,
    [Parameter(Mandatory=$true)]
    [string] $OutFileName,
    [Parameter(Mandatory=$true)][ValidateSet('Decrypt', 'Encrypt')]
    [string] $Operation
)

[System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null

$ErrorActionPreference = 'Stop'

function Protect-String([string] $clearText)
{
    return [System.Convert]::ToBase64String([System.Security.Cryptography.ProtectedData]::Protect([System.Text.Encoding]::Unicode.GetBytes($clearText), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

function Unprotect-String([string] $base64String)
{
    return [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64String), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
}

$document = [xml] (Get-Content $SourceFileName)
$nsm = New-Object 'System.Xml.XmlNamespaceManager' ($document.NameTable)
$nsm.AddNamespace('rs', 'http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08')

$attr = $document.DocumentElement.GetAttribute('plainText')
if ($attr -eq '' -and $Operation -ieq 'Encrypt')
{
    throw "The file does not contain plaintext passwords."
}
if ($attr -ne '' -and $Operation -ieq 'Decrypt')
{
    throw "The file does not contain encrypted passwords."
}

$servers = $document.SelectNodes("//rs:RegisteredServer", $nsm)
foreach ($server in $servers)
{
    $connString = $server.ConnectionStringWithEncryptedPassword.InnerText
    if ($connString -inotmatch 'password="?([^";<]+)"?') {continue}
    $password = $Matches[1]


    if ($Operation -ieq 'Decrypt')
    {
        $password = Unprotect-String $password
    }
    if ($Operation -ieq 'Encrypt')
    {
        $password = Protect-String $password
    }
    $connString = $connString -ireplace 'password="?([^";<]+)"?', "password=`"$password`""
    $server.ConnectionStringWithEncryptedPassword.InnerText = $connString   
}

if ($Operation -ieq 'Decrypt')
{
    $document.DocumentElement.SetAttribute('plainText', 'true') 
} 
else 
{
    $document.DocumentElement.RemoveAttribute('plainText')
}

$document.Save($OutFileName);
1

For SQL Management Studio v18, the configuration (including the "remember me list") is stored in:

%appdata%\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml

The passwords is stored in Credintial Manager (Control Panel\User Accounts\Credential Manager) under Generic Accounts like this:

enter image description here

To export the Windows Credential Store, see this Answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .