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.
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:
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.
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.
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.
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.
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
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);
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)
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:
To export the Windows Credential Store, see this Answer