A few days ago on Twitter I wrote:
Couldn’t connect to new SQL Server install because I forgot to enable TCP/IP. I’m the lead author for a Microsoft Press book about SQL Server administration.
Aside from demonstrating that checklists are helpful, it did raise an interesting point about why this isn’t a configurable option in the SQL Server Setup tool. After all, most people using SQL Server today are not connecting to it locally on the same machine, and the Linux and Docker container versions of SQL Server already have TCP/IP enabled.
Thanks to some helpful responses from John Q. Martin (blog | Twitter) and Dave Dustin (Twitter) on that thread, it turns out (and I really should have known this, being a lead author on a book about SQL Server administration) that you can turn on TCP/IP as long as you install SQL Server from the command line, or by using a configuration file.
The two switches as outlined in the official documentation are:
TCPENABLED=1– enables TCP/IP
NPENABLED=0– disables Named Pipes
Did you just say to yourself that you don’t install SQL Server on Windows using a configuration file? Do you click through every option and set it manually every time? Please don’t do that. Use a configuration file instead.
Once you cross over to the automation side, you’ll be able to do cool things like making sure all of your SQL Server deployments on Windows Server are the same, and now you can even ensure that TCP/IP is enabled by default, like me.
Thanks for the tip, John and Dave.
Share your thoughts in the comments below.
Photo by Denis Cardoso on Unsplash.