Error in Azure when a Web App tries to insert into a SQL Server database for the first time after tables created

Question

This is a question that involves Azure with SQL Server and a Web App.

Everything works fine locally. The problem exists only in Azure.

I have set up a SQL Server with a database, and published a web app. They are using the same region.

Everything has default settings, except for allowing my local ip to connect to the database server.

I can connect to Azure SQL Server from SQL Server Management Studio and I see my database there.

The first thing I do in the web app is trying to register a new user. The tech involved is ASP.NET MVC5, ASP.NET Identity, and Entity Framework with migration scripts. Normal MS stuff.

I have set the ApplicationDbContext(DefaultConnection) in the Publish/Settings/Databases dialog and checked "Execute Code First Migrations".

The migration script is running fine. All tables have been created and are showing up in SQL Server Management Studio.

But when the web app is trying to add a new user to the User table, I get the error shown below.

So.. The web app obviously has a connection and access rights to the database since all tables have been created. Any idea what I am missing?

Thanks for any help and input!

Update:

In the web.config I found a new ConnectionString: DefaultConnection_DatabasePublish with the correct configuration.

Somehow the original DefaultConnection was changed to Data Source=localhost;Initial Catalog={catalog};Integrated Security=True after it was published.

Changing the DefaultConnection to the same as DefaultConnection_DatabasePublish resolved the issue.

I have not had this behavior before when publishing. I have not checked "Execute Code First Migrations" before. Can someone confirm if the behavior is related to that?

Server Error in '/' Application.

The system cannot find the file specified

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ComponentModel.Win32Exception: The system cannot find the file specified

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Win32Exception (0x80004005): The system cannot find the file specified]

[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.SqlInternalConnectionTds.. ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) +821
System.Data.SqlClient.SqlConnectionFactory.CreateConnection (DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) +332
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection (DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) +38
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +699
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) +89
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +426
System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen() +343
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
System.Runtime.CompilerServices.TaskAwaiter. HandleNonSuccessAndDebuggerNotification(Task task) +58
System.Data.Entity.SqlServer.<b__3>d__6.MoveNext() +226
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
System.Runtime.CompilerServices.TaskAwaiter. HandleNonSuccessAndDebuggerNotification(Task task) +58
System.Data.Entity.SqlServer.d__9`1.MoveNext() +354
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +99
System.Runtime.CompilerServices.TaskAwaiter. HandleNonSuccessAndDebuggerNotification(Task task) +58
System.Data.Entity.Core.EntityClient.d__8.MoveNext() +594

Answer 1

According to your description and error message, I guess you don't set the right connection string in web.config.

So the web app couldn't access the right sql database.

I suggest you could try to use kudu console to check the web app have set the right connection string.

1.Open the kudu console.

2.Use debug console

3.Locate the site\wwwroot folder and find web.config file.

Click the modify button.

4.Then you could find the connection string.

Replace the right connection string with the old one.

The connection string format is as below:

Server=tcp:{sqlservername}.database.windows.net,1433;Initial Catalog={databasename};Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Or you could directly set the connection string in azure web app's Application settings.