Where are credentials for SQL Management Studio saved?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.

Solution 4:

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.

Solution 5:

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'

Tags:

Sql

Recent Posts