Getting SQL Express to Listen So You Can Connect Remotely
Posted by Russell Wright on April 22, 2014
Dang! We had a situation where a new tool we were implementing in our back office, BillQuick Web Services 2014, would not use SQL Server since we purchased the "Pro" version and not the "Enterprise" version. Unfortunately, it appears the trial version we installed has all the enterprise version capabilities and, after entering our license information, we were locked down to the pro version and it wouldn’t connect to the SQL Server anymore.
So, that meant I needed to install SQL Express to support the tool. Okay, no biggie…except for the fact that SQL Express does not enabled Named Pipes or TCP/IP protocols, by default. This took me a bit to figure out.
This is not a new problem, as posted by Peter van Ooijen in 2006. Some things never change…
The easy way to fix this is to use SQL Server Configuration Manager.
Navigate to the Protocols section for your SQL Express instance and switch it on!
And if that’s not enough, you need to change the TCP/IP properties to listen on the correct port. I set the port to 1077 (one the SQL Server is NOT listening on). It appears if you set it in the the IPAll section, the same port will be used for each IP address and you only have to enter it one time.
Oh, and then recycle your SQL server instance!
The connection string to use looks like this.