What? Did I read this right? Did he say SQL2000? Well, my friends, you're absolutely right. I jumped into a wormhole back to 2001, did some work and jumped back to the present time. Unfortunately, it's nothing as glamorous as that. Instead, I recently moved to a new project where we're migrating an old/legacy application to a new MVC/Angular implementation. The only thing that's not moving is the SQL server.
And this brings me to the reason why I'm writing this blog post. On my dev box I had SQL2014 running without any problems for months. 2 different instances, a full SQL server and an Express one. All working fine. As part of moving to the new project, I had to install SQL2000 on my local environment in order to work with the solution.
I installed SQL2000 and SP4 (both installation files are nearly impossible to find and most related links on the MSDN are broken) and then tried to connect using my SQL Server Management Studio (SSMS). However, no matter what I tried, I kept on getting the following error message:
I checked all the usual stuff:
- TCP/IP enabled
- connections enabled
- service running
- service account correct
- Windows authentication enabled
etc
No matter what I tried, nothing. And then I had an epiphany! You'll not find this anywhere on the Google/Bing or at least I couldn't.
TL;DR
The problem was the default port used by TCP/IP to connect to SQL 2000. By default, SQL listens on port 1433. However, if you already have newer versions of SQL running, that port is reserved. Even if you shut down the other instances, SQL2000 insists on not connecting, throwing the same error. Also, if you check the SQL 2000 Server logs, you'll see that it's more than happy to "bind" to port 1433 and tell you that it's listening for connections. It's not gonna happen!!
The only option is to change the default connection port to something else. I changed mine to 14333. To do this, you need to go to Program Files -> Microsoft SQL -> SQL Server Network Utility and change the settings as per the picture below:
Please note that in the picture above I have the wrong port number configured. It should be 14333 and not 1433
And then, one more step. You need to configure the same in the Client Network Utility. Go to Program Files -> Microsoft SQL -> SQL Server Client Network Utility and configure it according to the image below:
Then, go back to your SSMS and change the Server Name to <instance>,<port number>. In my case, this was:
Pay special attention to the comma (,) used between the SQL Instance and the Port number. Colons (:) wont work in this case
This solved the problem and I was able to connect to SQL Server 2000. It was an interesting blast from the past that had me scratching my head for a couple of hours trying to figure out the actual problem. I hope this post helps you if you ever come across this problem.