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.

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.
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

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:

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.

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). ut 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 leverage the application name in filtering XE or WhoIsActive’s program_name column (instead of another boring Microsoft SQL Server Management Studio - Query).

Application Name=It's a me, Mario;

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.