How to delete server entries in SQL Server Management Studio's "Connect to Server" Screen?

Question

Possible Duplicate:
How to remove “Server name” items from history of SQL Server Management Studio

In the "Connect to Server" screen, SQL Server Management Studio stores all entries you have ever entered for Server Name, login and password. This is very helpful, but from time to time things change, servers addresses change, databases are no longer available.

How can I delete server entries from this screen? Also, when you select a server, past logins are available in the list. Once again, these change. How can I delete user entries?

Solution 1

Looks like this file is a binary serialized version of the Microsoft.SqlServer.Management.UserSettings.SqlStudio class defined in the Microsoft.SqlServer.Management.UserSettings, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 assembly (located at c:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.UserSettings.dll).

With a bit of development skill (Visual Studio or even Powershell) you can deserialize this file into the original class, find the entries you want to remove and re-serialize the file back out.

This should give you the idea (working on a copy of the .bin file)...

var binaryFormatter = new BinaryFormatter();
var inStream = new MemoryStream(File.ReadAllBytes(@"c:\temp\SqlStudio.bin"));
var settings = (SqlStudio) binaryFormatter.Deserialize(inStream);
foreach (var pair in settings.SSMS.ConnectionOptions.ServerTypes)
{
    ServerTypeItem serverTypeItem = pair.Value;
    List<ServerConnectionItem> toRemove = new List<ServerConnectionItem>();
    foreach (ServerConnectionItem server in serverTypeItem.Servers)
    {
        if (server.Instance != "the server you want to remove")
        {
            continue;
        }
        toRemove.Add(server);
    }
    foreach (ServerConnectionItem serverConnectionItem in toRemove)
    {
        serverTypeItem.Servers.RemoveItem(serverConnectionItem);
    }
}

MemoryStream outStream = new MemoryStream();
binaryFormatter.Serialize(outStream, settings);
byte[] outBytes = new byte[outStream.Length];
outStream.Position = 0;
outStream.Read(outBytes, 0, outBytes.Length);
File.WriteAllBytes(@"c:\temp\SqlStudio.bin", outBytes);

After Adrian's question, I tried this again on a Win7 x64 box using Visual Studio 2010. I found the same error so, after digging a bit I found it took a number of steps to resolve.

1. Set the Platform target to 'x86' in the project properties
2. add a reference to Microsoft.SqlServer.Management.SDK.SqlStudio (on my box this was at c:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.SqlServer.Management.Sdk.SqlStudio.dll)
3. add a reference to Microsoft.SqlServer.Management.UserSettings (in the same directory at the previous one)
4. perform custom assembly resolution

The custom assembly resolution took a bit of doing since it wasn't obvious (to me, at least) why the CLR wouldn't just resolve the assembly correctly and why Visual Studio wouldn't allow me to add the reference manually. I'm talking about the SqlWorkbench.Interfaces.dll.

The updated code looks like this:

internal class Program
    {
        static void Main(string[] args)
        {
            AppDomain.CurrentDomain.AssemblyResolve += CurrentDomain_AssemblyResolve;

            var binaryFormatter = new BinaryFormatter();
            var inStream = new MemoryStream(File.ReadAllBytes(@"c:\temp\SqlStudio.bin"));
            var settings = (SqlStudio) binaryFormatter.Deserialize(inStream);
            foreach (var pair in settings.SSMS.ConnectionOptions.ServerTypes)
            {
                ServerTypeItem serverTypeItem = pair.Value;

                List<ServerConnectionItem> toRemove = new List<ServerConnectionItem>();
                foreach (ServerConnectionItem server in serverTypeItem.Servers)
                {
                    if (server.Instance != "the server you want to remove")
                    {
                        continue;
                    }
                    toRemove.Add(server);
                }
                foreach (ServerConnectionItem serverConnectionItem in toRemove)
                {
                    serverTypeItem.Servers.RemoveItem(serverConnectionItem);
                }
            }


            MemoryStream outStream = new MemoryStream();
            binaryFormatter.Serialize(outStream, settings);
            byte[] outBytes = new byte[outStream.Length];
            outStream.Position = 0;
            outStream.Read(outBytes, 0, outBytes.Length);
            File.WriteAllBytes(@"c:\temp\SqlStudio.bin", outBytes);
        }

        private static Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
        {
            Debug.WriteLine(args.Name);
            if (args.Name.StartsWith("SqlWorkbench.Interfaces"))
            {
                return Assembly.LoadFrom(@"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbench.Interfaces.dll");
            }
            return Assembly.Load(args.Name);
        }
    }

Solution 2

Unfortunately, it does not appear to be possible (or at least practical) to only remove certain items.

However, if you want, you can reset the configuration and start from scratch.

Make sure Management Studio is closed, then delete or rename this file:

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

Note that that file contains other user preference settings, so if you've customized your Management Studio configuration, you'll have some work to do restoring them.

Reference: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/94e5c3ca-c76d-48d0-ad96-8348883e8db8/

Good luck!