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.
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.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
|cookielawinfo-checbox-analytics||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".|
|cookielawinfo-checbox-functional||11 months||The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".|
|cookielawinfo-checbox-others||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.|
|cookielawinfo-checkbox-necessary||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".|
|cookielawinfo-checkbox-performance||11 months||This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".|
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.