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?

are you reading the connection string from your appsettings.json if yes Have you added the same connection string in the app settings in portal which is under configuration if you have not added then that might be the problem — Mohit Ganorkar
– Mohit Ganorkar, Commented Aug 28, 2022 at 11:10
Thanks for the comment MohitGanorkar. Yes, I copied the connection string from the "Connection Strings" section in Azure Portal, and pasted it into both appsettings.json and the OnConfiguring() method in my DbContext. When I run the web app in Visual Studio on my PC, it works fine, and is able to connect and write to the database. But when I publish the exact same web app, unmodified, to Azure App Service, it throws an Access Is Denied exception. — Fabricio Rodriguez
– Fabricio Rodriguez, Commented Aug 28, 2022 at 11:17
Perhaps you have another connection string in the mix. The samples you've shown specifically request to use the tcp: protocol, yet the error message is coming from the Named Pipes Provider. — AlwaysLearning
– AlwaysLearning, Commented Aug 28, 2022 at 12:35
Pls check your firewall settings, and you also can create a new rule to allow all ip for test. — buymeacoffee
– buymeacoffee, Commented Aug 29, 2022 at 13:58
Thanks for the comment Jason Pan. I did your suggestion, but still no luck... Trying a few other things out. Will let you know how it goes... — Fabricio Rodriguez
– Fabricio Rodriguez, Commented Aug 30, 2022 at 13:45

Ryan Hill · Accepted Answer · 2022-09-08 20:01:03Z

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)

answered Sep 8, 2022 at 20:01

Ryan Hill

1,9402 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fabricio Rodriguez Over a year ago

Ryan, you're a genius! :) yes, my dbContext was working on localhost, but not on Azure App Service, because on Azure I had a connection string saved as an environmental variable (under Configuration) which was overwriting the appsettings.json variable. Thank you!!!!!!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related