Access denied when connecting Web API (Azure App Service) to Azure SQL Database

Question

I have a .Net 6 Web API hosted on Azure App Service. I also have an Azure SQL Database (Pricing Tier: General Purpose - Serverless: Gen5, 2 vCores). Both App Service and SQL Database are on the same subscription, same resource group, and same region (South Africa North).

I can connect and perform CRUD operations on the Azure SQL Database from SQL Server Management Studio. Also, the Web API is able to connect and perform CRUD operations on the database, when I'm debugging it locally in Visual Studio.

However, when I publish the Web API to Azure App Service, the app throws the following exception when it tries to connect and write to the Azure SQL Database:

An error occurred using the connection to database 'eballcoz_mssql' on server '156.38.224.15'.

An exception occurred in the database while saving changes for context type 'eBall.Web.API.Data.EBallContext'.
Microsoft.Data.SqlClient.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.ComponentModel.Win32Exception (5): Access is denied.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:5,State:0,Class:20

I get that exception when the app tries to write a record to a table in the database, using DbContext (EF Core). This is the OnConfiguring() method in my DbContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {             
        optionsBuilder.UseSqlServer("Server=tcp:xxxxx.database.windows.net,1433;Initial Catalog=xxxxx-sqlserver;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
    }
}

I copied this connection string from the "Connection Strings" section of the SQL Databases panel in Azure Portal:

The funny thing is that I also have NLog set up in the API to log errors to this same database, and it's able to do this just fine (it calls a stored proc on the database to log the error). This is my target condiguration in nlog.config:

<target name="db"
            xsi:type="Database"
            connectionString="Server=tcp:eball.database.windows.net,1433;Initial Catalog=xxxxx-sqlserver;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
            commandType="StoredProcedure"
            commandText="[InsertLog]"
        >
      <parameter name="machineName"    layout="${machinename}" />
      <parameter name="logged"         layout="${date}" />
      <parameter name="logLevel"       layout="${level}" />
      <parameter name="message"        layout="${message}" />
      <parameter name="logger"         layout="${logger}" />
      <parameter name="properties"     layout="${all-event-properties:separator=|}" />
      <parameter name="callsite"       layout="${callsite}" />
      <parameter name="exception"      layout="${exception:tostring}" />
      <parameter name="callsiteLineNumber"      layout="${callsite-linenumber}" />
      <parameter name="stackTrace"      layout="${stacktrace}" />
    </target>
  </targets>

I do have the "Allow Azure services and resources to access this server" ticked in the Networking tab of Azure Portal:

As you can see, I even tried to add the App Service's IP addresses to the firewall (rules "Azure App Service 1-4"), to no avail. The last two rules are to allow SQL Server Management Studio, running on my local PC (at home and at work), to connect.

Now, the fact that the app is able to connect just fine when running locally on my PC tells me that the connection strings are probably fine. So there must be something else not allowing the app to connect to the database when hosted on Azure App Service. Like I mentioned before, the weird thing is that the app is able to log exceptions to the database via NLog (which calls a Stored Proc). However, when trying to connect to the database using a dbContext (EF Core), access is denied.

I'm a bit stumped...

This is the connection string that I am using (and which works with SQL Server Management Studio, and my web app when run locally on VS):

Server=tcp:xxxxx.database.windows.net,1433;Initial Catalog=xxxxx-sqlserver;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Any ideas?

Answer 1

What's throwing me off is that you said your NLog works perfectly fine and locally it works as well. The only thing I can see is in your screen shot shows eball database on eball-sqlserver. They error message you're receiving is against eballcoz_mssql; that leads me to believe your DbContext is looking for a database in the server that isn't there.

It's a long shot, since it works locally, but I would double check your entity model just be sure you're not inadvertently connecting to a different database. I would also in appsettings.json change Microsoft to Debug so you can get entity framework spit out the SQL connection your DbContext is using.

Sidenote: Add AZURE_SQL_CONNECTION as a SQL connection string app setting and retrieve using config.GetConnectionString("AZURE_SQL_CONNECTION") when using options.UseSql(...) in Program.cs (or Startup.cs)