Problem:
After installing a new named instance of SQL Server 2016 onto a development machine, I was unable to connect via a remote machine, but could connect locally. The named instance, MSSQLSERVER2016, was the 2nd instance to be installed on the server – the original was the default instance of SQL 2012 Standard.
I got the error message:
“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server)”
I was still able to connect successfully to the original 2012 default instance.
Solution:
The first thing I checked, as suggested in the error message, was that remote connections were enabled in the server properties of the named instance.
The named instance was correctly set to allow remote connections. So started to search for information regarding SQL Server network protocols and came across this MS document on SQL Server Network Protocol Configuration – https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/default-sql-server-network-protocol-configuration .
Apparently, different editions of SQL Server set network protocols differently for security purposes, as you can see from the table below taken from the documentation.
In this case, I was in fact using Developer Edition for the MSSQLSERVER2016 named instance, so TCP/IP connections – which would be used for remote connections – was disabled, while Shared Memory – which would be used locally – was enabled. This differs from the installation defaults of Enterprise and Standard editions.
So, to fix the connection problem, I opened up SQL Server Configuration Manager on the server, went to SQL Server Network Configuration, and sure enough, for the MSSQLSERVER2016 named instance TCP/IP was set to disabled.
After changing the TCP/IP Protocol Status to Enabled, all I had to do was restart the SQL Server Service for the named instance, and then I could connect successfully from a remote machine.