Getting access is denied on opening SQL connections

Question

We're pulling down data from SQL in a high throughput scenario here, through one app. Possibly up to 100+ SQL requests at any one time. Some may be long running, which may be why they mount up.

Intermittently (but not constantly), we're getting the following exception when calling SqlConnection.Open();:

System.Exception: Error getting document from database --->
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) ---> System.ComponentModel.Win32Exception: Access is denied
--- End of inner exception stack trace ---

at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at BridgeService.SqlDal.GetDataFromSql(Int32 entityId)
--- End of inner exception stack trace ---

The code causing this exception looks like this:

public DataSet GetDataFromSql(int entityId)
{
    DataSet tempDataSet = null;

    using (var cnn = new SqlConnection(GetConnectionString()))
    {
        cnn.Open(); // this line throws the exception

        try
        {
            // stuff gets done on cnn
        }
        catch (Exception e)
        {
            throw e;
        }
    }

    return tempDataSet;
}

I'm imagining this is something to do with the connection pool in some way. Is there a way we can check for an available connection in the pool before we open and use it? Or is this caused by something completely different?

If you need any more info, feel free to ask.

Answer 1

I had the same problem once too.Solved by enabling NamePipes in SQL server configuration manager and turning off windows firewall or allowing sqlserver port 1433 in windows firewall Hope it'll work for you too

Answer 2

That's outdated but if someone needs a solution,

Open Sql Configuration > Sql Server Network Configuration > Protocols for MSSQLServer Then enable Named Pipes and TCP/IP settings.

Restart MSSql Server via windows services.