CODEWITHSUNDEEP
sql-serverasp.net-mvcasp.net-mvc-4azureazure-sql-database
Gerard
Gerard

Reputation: 2709

Azure and SQL Server: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Using Azure with SQL Server. In 30% of the cases where the SimpleRoleProvider is implicitly called we get an error: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server. The error occurs frequently but is not reproducible. It looks like a timeout..

[Authorize(Roles = "Administrator")]
public class AdminController : Controller
{
   ...
}

The stack trace looks like:

[Win32Exception (0x80004005): Access is denied]

[SqlException (0x80131904): 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.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5296071 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +558 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +5308555 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover) +145 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) +889 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) +307 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions) +434 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +225 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +37 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnectionOptions userOptions) +558 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnectionOptions userOptions) +67 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1052 System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +78 System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +167 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +143 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +83 System.Data.SqlClient.SqlConnection.Open() +96 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +88 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +239 System.Web.Security.SqlRoleProvider.GetRolesForUser(String username) +762 WebMatrix.WebData.SimpleRoleProvider.GetRolesForUser(String username) +54 System.Web.Security.RolePrincipal.IsInRole(String role) +9461104

We don't have this problem on our own server. Is this a configuration issue? We are using the free trail version of Azure yet.

--- ADDITION ---

The bad thing is that the MVC-framework "handles" the request, in the example given (Role Management). The use of the framework is to add an abstraction layer to handle "domain request" conveniently. So what to do in this case?

Thanks in advance for any help!

Upvotes: 2

Views: 6853

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294297

I assume by Azure and SQL Server you mean the SAAS Azure SQL Database (the names change faster than I can keep track), not the IAAS SQL Server on Windows Azure. First thing ou need to determine wether your application is being throttled, see Connection Constraints and give this article a good, thorrough read: Causes of Connection Termination. If your application is being throttled then you need to revisit your design and address the specific reason why you're being throttled.

Upvotes: 2

amhed
amhed

Reputation: 3659

It's expected on SQL Azure. It should happen on about 0.5% of the time, not 30%.

Here's a previous question that addressed this topic

Connection failures in SQL Azure are common. This is because your application will create a connection pool but while your side thinks these connections are over, Azure could terminate them at their end and you will never know about it.

You should implement a retry logic on your code in case one of these timeouts occur. Microsoft already has a library for this: the Transient Fault Handling Application Block

Upvotes: 1

Related Questions