In previous article, we have discuss tutorial about How to Insert Missing SQL Server Identity Column Values Using Set Identity_Insert Command. In this article, it is about SQL server again and some users will experience this error. The error details are given as:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe) (Microsoft SQL Server, Error: 233)
You can see the screenshot of this error that I had received while making a database connection.
Error 233 – Root Cause
As error suggests the provider name as Shared Memory Provider. We know Shared Memory is a protocol used in SQL Server along with TCP/IP and Named pipe. We decided to check the settings and values for all protocols in SQL Server Configuration Manager.
Just launch your SQL Server Configuration Manager and expand SQL Server Network Protocols from left side panel. Here, you can see an option “Protocols for MSSQLSERVER”. MSSQLSERVER is the name of SQL Server Instance as default instance has been installed there. If you have named instance installed on your machine then you might get “Protocols for <INSTANCENAME>.
Now click on this option “Protocols for MSSQLSERVER”, you can see all three protocols used in SQL Server in right side pane as shown in below image.
Here we can see Shared Memory protocol is set as Enabled but Named Pipes is set as Disabled that should be enabled to make successful database connection. This was the main reason for getting this error. Keep reading this article to fix this issue in next section.
I have also written another aspect for Error 233 in below article. You might get this error if your SQL Server Instance has exceeded the total number of user connections set in server configurations. Have a look at this article as well.
Error 233 – Solution
You get error 233 because SQL Server client cannot connect to the server. This error could occur because the server is not configured to accept remote connections. To fix this issue, we will use the SQL Server Configuration Manager tool to allow SQL Server to accept remote connections. Here, in our case one of the protocol Named Pipe was disabled that’s why we were getting error. We will go ahead and fix this issue by enable the Named Pipe protocol. You might get this issue due to any protocol being disabled.
Make sure to enable Shared Memory, TCP/IP and Named Pipe protocols. Launch SQL Server Configuration Manager. Expand SQL Server Network Configuration from left side pane and click on Protocols for MSSQLSERVER. MSSQLSERVER is SQL Server Instance name. Now you will see all protocols in to right side pane. Right click on given protocols that is disabled and select Enable button to enable that protocols.
Named Pipe protocol was disabled in my case so I have launched Properties window of this protocol and selected Enabled option as Yes from dropdown as shown in below screenshot. Now click on Apply and Ok button to apply this change on SQL Server. It will ask you to restart the SQL Server service to apply these changes in to effect. Restart SQL Server services from SQL Server Configuration manager. Once services will come online, try to connect to your SQL Server instance, this time you will not face error 233 and you will be able to make successful database connection.
If you are still facing same issue then you can also check “Allow Remote Connections” settings for this SQL Server instance. This setting must be enabled to connect to databases remotely. Connect to SQL Server Instance in SSMS locally on database server. Right click on SQL Server instance name in SSMS and choose Properties. You will get Server properties window. Click on Connections from left side pane and tick the check box on Allow remote connections to this server option from right side pane.
Conclusion
If you find this tutorial helpful, you can always share this tutorial to others. Thank you and Happy Coding!
Yury Sobolev is Full Stack Software Developer by passion and profession working on Microsoft ASP.NET Core. Also he has hands-on experience on working with Angular, Backbone, React, ASP.NET Core Web API, Restful Web Services, WCF, SQL Server.
