Misleading SSMS Connection

Foreword

The other day I managed to confuse myself. I was looking up some information from an Extended Events (XE) session, but my eyes were playing a trick on me. The database ids were off by one, and I couldn't find some query hashes in the Query Store, even when they were supposed to be there. So my first thought was that I must be connected to a different server with a drift. But the information in the SSMS tab, status bar and even colour coding (courtesy of Redgate's SQL Prompt) - all pointed to the correct server.

When I ran SELECT @@SERVERNAME, I found out I was right.

SSMS showing the same tab and status bar information for both windows, but two different server names: Cerebro and bamboozled

How did it happen?

To demonstrate this, I will need two different SQL Servers. I already have a local instance, and I'll use a Docker container for the other one. I keep one around for exactly this kind of sandbox - more on why I run SQL Server in Docker.
The SQL Server version doesn't matter; if you locally have a different image, feel free to use that one.

docker run `
-e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=Password5' `
-e 'MSSQL_PID=Developer' `
--hostname bamboozled `
-p 14338:1433 `
-d `
--name tempsql `
mcr.microsoft.com/mssql/server:2019-latest

Warning Password5 is a throwaway password for a local sandbox container only. Don't use it on anything real.

Take note of the port number 14338 and the hostname.
To connect there, I'll open a New Query window and fill in all the necessary information like so:

SSMS New Query connection dialog filled in for the Docker instance: server localhost,14338, SA login

And running the previous query, we can see that everything matches the information from the docker run command and the colour coding of the tab and status bar is yellow (that's how I denote a sandbox environment).

So far, no surprises there.

SELECT @@SERVERNAME returns "bamboozled", the Docker container hostname, with a yellow sandbox tab

But the connection window has an Options button, revealing additional tabs. For example, on the Connection Properties tab, you can specify a default database for a connection or a shorter timeout (especially useful for the Docker containers). But I'm interested in the last tab, called Additional Connection Parameters.

I usually use this tab to specify an Application Name parameter. Then I can use the application name to filter XE or Adam Machanic 's sp_WhoIsActive program_name column (instead of another boring Microsoft SQL Server Management Studio - Query).

Application Name=It's a me, Mario;

Application name showing "It's a me, Mario" in the Additional Connection Parameters tab

But at the very bottom of this tab, there is a note that gives away the solution. Emphasis mine:

Note Connection string parameters override graphical selections on other panels

Meaning, if I put a whole connection string there, like:

Server=localhost,14338;Database=master;User ID=sa;Password=Password5;Trusted_connection=False;

It will connect to the Docker container, no matter what I've selected on the first tab.

What really happened

I was aware of this behaviour, so how did I confuse myself? When you have a focus in an existing tab and click on a New Query - it copies the whole deal, including the Additional Connection Parameters, so when you click on the Change Connection and only change the server in the graphical menu, it still gets overridden.

So make sure you keep these special connections contained, so you don't accidentally copy them.

What is it good for?

Absolutely nothing! Maybe it's a cautionary tale or a harmless prank you can play on your colleague when April Fool comes around. Just make sure you're not connecting to Production!

As always, thanks for reading.

Thank you for reading