Named Pipes Provider: Could not open a connection to SQL Server 2016 [53]

Question

I have been battling with this error for two days and I can't find any solutions I have checked that my services are running, Named pipe and TCP/IP are enabled. I am able to login into SQL Server using Windows and SQL Server auth. This is happening when I send a command text to select from a table, the connection state is open but when I do:

var dbCommand = CreateDbCommand(connection, commandText, null, parameters);
return dbCommand.ExecuteReader();

private static IDbCommand CreateDbCommand(IDbConnection connection, string commandText, CommandType? commandType, IEnumerable<Parameter> parameters)
{
    var command = connection.CreateCommand();
    command.CommandText = commandText;
    command.CommandType = commandType ?? CommandType.Text;

    foreach (var parameter in parameters)
    {
        var sqlParameter = command.CreateParameter();
        sqlParameter.ParameterName = parameter.Key;
        sqlParameter.Value = parameter.Value ?? DBNull.Value;
        command.Parameters.Add(sqlParameter);
    }

    return command;
}

I am getting the error on

return dbCommand.ExecuteReader();

and I have exhausted my patience any help is welcome.

I am using SQL Server 2016 and my website is running under local IIS.

Thanks

PS: this used to work in SQL Server 2012, but is failing since upgrading.

Regards

Check if 1433 is opened in firewall. I assume you're running IIS and SQL Server on different machines. — qxg, Feb 22 '17 at 03:30
to make things clear, I run my local website under the same IIS configuration as I would in production — Jack M, Feb 22 '17 at 08:02

granadaCoder · Answer 1 · 2017-02-23T16:50:07.273

1

IIS:

If you running under IIS, then your web-app or virtual-directory is running under an AppPool, and that AppPool has an IIdenity associated with it. So just because ~you (mycompany\jackm) can get to the db, it doesn't mean that account running the AppPool can. Figure out who is running the AppPool and grant login and permissions to the db to that user.

OR (quick test), change the Identity of the AppPool to be you (mycompany\jackm), and try that.

If you are using a local-user account, make sure you give that user the ability to login and access the database.

Use Master
GO

CREATE LOGIN [myMachineName\myLocalUser] FROM WINDOWS;  
GO  

use MyDatabase

EXEC sp_addrolemember 'db_datareader', 'myMachineName\myLocalUser'

GO

edited Feb 23 '17 at 16:50

answered Feb 21 '17 at 22:22

granadaCoder

26,328
10
113
146

I am connecting using local windows domain account, to be precise the connection is open so it is not permission related. I had an issue earlier with linked server so after I fixed that I was stuck with this error – Jack M Feb 22 '17 at 03:44
I forgot to say that the IIS/Apppool account is enabled as I am able to make a connection I am just wondering if it is anything to do with NTLM or Kerberos authentication – Jack M Feb 23 '17 at 08:03
"local windows domain account"... are you talking about a local-user that is specific to the single machine? or do you mean a domain-account that sits in AD somewhere? – granadaCoder Feb 23 '17 at 13:26
Local user account on my machine, just for clarification, I am using my laptop and running the website under iis (in the same way I would run the website on the server) – Jack M Feb 23 '17 at 16:09
I appended my answer. – granadaCoder Feb 23 '17 at 16:50

Named Pipes Provider: Could not open a connection to SQL Server 2016 [53]

1 Answers1