SQL Server 2014 – Named Pipes Clarifications

availability-groupssql server 2014

Currently,SQL Services are using TCP IP protocol. Named pipes protocol is disabled. I am facing issues while running my application status as below :

An error has occurred while establishing a connection to the server.
(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

My Question is ,Will there be any issue if I enable Named Pipes now because I restarted the Named Pipes and restarted SQL Service and issue seems to be resolved. But I am confused why we are getting Named Pipes issue when TCP IP is enabled. Does SQL Server need both TCP IP and Named Pipes to be enabled ?

Thank you

Best Answer

It's because,

When a client tries to make a connection, it uses the protocols order by priority defined by SQL Server configuration manager. A low value means a high priority. If you configured TCP/IP as first priority and SQL Server is not able to resolve Name to IP it raise the error you posted. But when you disable TCP/IP, then because TCP/IP with first priority is disabled client uses Named Pipe (with second priority) to connect to SQL Server. And get a successful connection.

Here, you can set the priority of protocols by moving it UP or DOWN.

Thanks

Related Solutions

SQL Server 2014 – How to Create a Server Instance with Just ComputerName

You cannot change the Instance Name after it has been installed. But you can uninstall that SQL Server instance and reinstall as a DEFAULT instance. Ideally you should make a backup of the server, then restore the backups to the new server.

You can technically detach databases and attach again after the reinstall, but that includes the risk of losing your detached databases. See Aaron Bertrand's post:

https://blogs.sentryone.com/aaronbertrand/bad-habits-file-backups/

When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio button and type in the default instance name of MSSQLSERVER.

All default instances use the MSSQLSERVER internally, but they just do not make it visible to you.

Sql-server – Classic ASP Connection to SQL Server 2014 without TLS 1.0 using ADODB.Connection

You might possibly be trying to use 32 bit legacy drivers.

Try running C:\Windows\SysWOW64\odbcad32.exe to create a DSN that connects to your SQL Server database using the 32 bit SQL Server driver. First test the connectivity using the GUI.

Then try specifying your DSN in your connection string like so:

DSN=myDsn;Uid=myUsername;Pwd=;

The 64-bit version is located in: C:\Windows\System32\odbcad32.exe

P.S. I've not tried this but the provider name for the 32-bit SQL Server is actually "SQL Server". It'd be nice if all you had to do was replace SQLNCLI11 with SQL Server. Here's my file DSN I created with the 32 bit version--notice the driver...

[ODBC]
DRIVER=SQL Server
UID=sa
WSID=P13-0000
APP=Microsoft® Windows® Operating System
SERVER=mcdba1

Best Answer

Related Solutions

SQL Server 2014 – How to Create a Server Instance with Just ComputerName

Sql-server – Classic ASP Connection to SQL Server 2014 without TLS 1.0 using ADODB.Connection

Related Question