Where are credentials for SQL Management Studio saved?

sql

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:

C:\Documents and Settings\<userName>\Application Data\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.

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.

Where are credentials for SQL Management Studio saved?

Related

Recent Posts