none
Cannot connect to DB: provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

    Question

  • This error is a nightmare.

    I deployed a website to azure using publish profile, it worked great until I republished or did some other action.

    Every second action I take in my website, and this error pops in without letting me reproduce it.

    It works OK, until some time passes by, and the error is back again.

    What can lead to this error? I don't know even what's the source of the error.

    I connected using the same connection string via SSMS and works great.

    Here is my web.config conn. strings area:

    <connectionStrings>
        <add name="Context" connectionString="Data Source=tcp:osq20ak50cc.database.windows.net,1433;Initial Catalog=l_db;User ID=shimmy@osq20ak50cc;Password=ddd" providerName="System.Data.SqlClient" />
        <add name="Context_DatabasePublish" connectionString="Data Source=tcp:osq20ak50cc.database.windows.net,1433;Initial Catalog=l_db;User ID=shimmy@osq20ak50cc;Password=ddd" providerName="System.Data.SqlClient" />
        <!--add name="Storage" connectionString="DefaultEndpointsProtocol=https;AccountName=lblob;AccountKey=QJkcru54YWn/BIQpGGTXpoEWPYlX1bSCiA17yZnTKVe2RnTVP3QL+1V0ohzZduHINtdVdN2cA3AWlGswXhEhBbDQ==" /-->
    </connectionStrings>
    

    Here is the error details:

    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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

    Stack trace:

    [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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5295167 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover) +5307115 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) +920 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) +5309659 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) +38 System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +5311874 System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) +143 System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) +83 System.Data.SqlClient.SqlConnection.Open() +96 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +76 [HttpException (0x80004005): Unable to connect to SQL Server database.] System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +131 System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install) +89 System.Web.Management.SqlServices.Install(String database, String dbFileName, String connectionString) +27 System.Web.DataAccess.SqlConnectionHelper.CreateMdfFile(String fullFileName, String dataDir, String connectionString) +386

    Last time it messed up was when I uncommented the third connection string, now even I already recommented it, server is down.

    Why does it have to happen, my customer is frustrated and so am I. Is it a known problem in Azure?

    This has caused me so much grief! Is it unique to Azure as a gift to new customers??


    Shimmy


    Tuesday, December 04, 2012 1:30 PM

Answers

  • The answer was that I relied on the connections strings of the Windows Azure server that are supposed to be injected to the app configuration, however it's not there in the Application_Startup method, adding the connection string to web.config manually did the trick.


    Shimmy

    Wednesday, December 12, 2012 4:29 AM

All replies

  • Hi

    try removing the tcp and the port value from the connection string.

    you might have a firewall issue between Role Instance to SQL Azure Load balancer

    use the following connection string :

    <add name="Context" connectionString="Data Source=osq20ak50cc.database.windows.net;Initial Catalog=l_db;User ID=shimmy@osq20ak50cc;Password=ddd" providerName="System.Data.SqlClient" />

    Thanks

    Thursday, December 06, 2012 2:40 PM
  • The answer was that I relied on the connections strings of the Windows Azure server that are supposed to be injected to the app configuration, however it's not there in the Application_Startup method, adding the connection string to web.config manually did the trick.


    Shimmy

    Wednesday, December 12, 2012 4:29 AM
  • Can you detail exactly what you added to overcome this? I get this error after a few minutes idle consistently.
    Thursday, December 20, 2012 4:44 AM