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.

Answer 3

I was experiencing a similar problem. Eventually we worked out that it wasn't a problem with SQL Server at all; it was a problem on the API server, where we were spinning up hundreds or even thousands of threads, each one making its own connection to the database. The API server couldn't handle the load and started throwing "Access Denied" exceptions without even really trying to connect.

Solution was to throttle the number of threads. I used the pattern described in this answer.