Search code examples
sql-servervb.netvisual-studioconnection-stringnamed-pipes

Cannot connect to SQL Server using Named Pipes through .NET application

First of all, I am trying to make this work because the "initial connection" of my Windows Forms application (run from Windows 10) to my SQL Server 2012 VM is taking too long (once connected all good). I read that by forcing/using Named Pipes instead of TCP/IP, there should be a considerable improvement on the above.

That being said, I have already read "a lot" and yes, I have found similar posts with the same regular error:

The server was not found or was not accessible... Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

and I cannot figure out how to connect to SQL with NP protocol. I have tried to force it directly through the app connection string (Server=np:MyServer or np:\MyServer\pipe\sql\query or \MyServer\pipe\MSSQL$MSSQLSERVER\sql\query or ;Network Library=dbnmpntw) without success.

Notes:

  1. There is NO firewall enabled at the Windows 10 client nor at the Windows 2012 server.
  2. There is only one instance (default MSSQLServer) at the SQL server.
  3. Shared memory, Named Pipes, TCP/IP protocols are already enabled for the SQL instance in SQL Configuration Manager.
  4. Named Pipes is using its defaults (no alias defined).
  5. Of course I have already restarted the SQL Server service and even rebooted the server.

At this point for testing/replicating the problem, and if anybody there would like to assist this guy about to shoot himself, I am using Visual Studio - View - Server Explorer - Data Connections - Add Connection - Data provider .NET Framework Data Provider for SQL Server, test proper/successful connection to your SQL server (using TCP/IP) and then go to Advanced and adjust "Network Library" to Named Pipes (see below). Test Connection button again and if you figure out how to get a successful result, PLEASE let me know how! (the connection string in the new connection properties should do the trick)

Visual Studio - Test new SQL Connection with Named Pipes

Visual Studio - Test new SQL Connection with Named Pipes

Thanks in advance for your time.

Solution

  • Although I haven't found a way to connect to the SQL server using Named Pipes protocol, I think I took the wrong path focusing on making that work. As I mentioned initially, my main goal was to improve the "initial connection" to the database, and I have already accomplished that. So, if this helps anybody there, it seemed to be a network related problem. Not sure if at the Hyper-V virtual switch level, but in any case:

    1. Adjust your hosts file at the client workstation and add a direct entry to your server (i.e. 172.22.152.7 MyServer).
    2. Be sure to set a fixed IP on your server.

    With the above, the initial connection went from 3-5 seconds to almost immediately (0.5 s).

    Additional notes:

    1. Not a must but since I don't use/need IPv6, I disabled it at the virtual switch and at the server's NIC).
    2. I assume putting the server's IP address directly in the connection string would have made it too. However, eventually, you may need to change IP so, I think it is better/easier to adjust this at the hosts file level.

    Thanks for your attention,

    M.