So, you installed Microsoft SQL Server Express and just wanted to enjoy free MS SQL on a Windows machine (well, what can you do - unfortunately a lot of software only knows how to work with MS products), when you discovered that the server isn't visible from the network.
That is, while sitting on the Windows server, SQL is accessible, but it simply refuses to work over the network.
The catch here is... well, maybe it's marketing, or maybe it's the crooked hands of the programmers who built the Express installer. The point is that:
a) By default, TCP/IP network support is disabled in SQL Express.
b) By default, the SQL Server Browser service is disabled in SQL Express.
But let's go ahead and fix this misunderstanding.
1) Open "SQL Server Configuration Manager" (via the "Start" menu and the corresponding item in the programs list).
2) Select "SQL Server Services" and double-click on "SQL Server Browser".
3) On the "Service" tab, set the "Start mode" option to "Automatic".
4) Save and start the "SQL Server Browser" service. This service is responsible for automatically redirecting SQL program connectors to the correct dynamic port of SQL Server Express.
5) Now go to "SQL Server Network Configuration" and select "Protocols for SQLEXPRESS" (the name may vary depending on what you named the instance).
6) Double-click to open "TCP/IP".
7) Set "Enabled" to "Yes" and "Listen All" to "Yes".
8) Go back to "SQL Server Services" and restart the "SQLEXPRESS" service (again, the name may be different depending on what you named the named instance).
That's it, the network works and SQL Server Express is accessible from outside via TCP/IP.
PS. Pay attention! The TCP/IP settings of the SQL server list all the IP addresses explicitly. But if you change the addresses on the Windows server, they won't automatically change in the SQL Server settings, and you might spend a long time wondering why the server suddenly stopped working over the network - it's because the SQL Server settings still contain the old addresses.
Comments