SQL Server 2017 Connections working in ASP.NET C# Web projects, but not in Windows projects, in Visual Studio 2019

Question

Using the basic SqlConnection code below, I have discovered that, at my workstation, I am unable to connect to our Sql Server 2017 deployment via ASP.NET C# code in any given type of Windows-based project, such as Winforms (.NET Framework or Core) or Console apps. However, this code will run without incident in any Web-based project, such as Web Forms or MVC, either using .NET Framework or Core.

    protected void testConnection()
    {
        string con = "Server=MySqlServer;database=MyDatabase;Integrated Security=true";

        using (SqlConnection cnn = new SqlConnection(con))
        {
            cnn.Open();

            cnn.Close();
        }
    }

In a web project, this code runs. In a Windows project, I get this error:

System.Data.SqlClient.SqlException: '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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'

In investigating this problem, I have tried the following:

• Explicitly declaring the domain on which my SQL server is found (eg. MySqlServer.domain.com). No good.
• Explicitly declaring the SQL Server port number, according to the example shown on connectionstrings.com (eg. MySqlServer, 1433). We do use the standard port number, for the record. No good.
• Changing the protection level of the test method (protected/private/public). No good.
• Connecting to the SQL Server in PowerShell. This connection worked.
• Pinging in the SQL Server in a command prompt. The server responded in 1ms consistently.
• I have asked other users on this network to test this code in a Winforms project on their workstations. No one is able to reproduce my issue.
• Consulted my sysadmin. He is so far not able to find any reason why I should be having this issue.

The only difference I have noticed in all of this is that, when I declare the port number, the Inner Exception returned with the error reads "Win32Exception: A non-recoverable error occurred during a database lookup". Otherwise, it simply reads "Access is Denied."

All of this tells me that the issue is obviously something peculiar to my workstation, but I have no idea what, apart from some firewall or other local security setting that the sysadmin neglected to check. Has anyone encountered this problem?

My VS 2019 installation is only one revision out of date as of this writing, 16.8.3 as opposed to 16.8.4. I am able to connect to and run TSQL code on any database I care to via SSMS. We connect using Windows Authentication. According to the results of "select @@version", our version of SQL Server is 2017, 14.0.3356.20 (x64).

Any insight is appreciated.

Answer 1

We fixed it by forcing Named Pipes to be enabled in SQL Server, as opposed to using the default setting.