AzurePack: Unable to create database when using SQL High Availability group (AlwaysOn)–Part 2

  • Article

 

The symptoms seen in this scenario also are the same as we had seen in Part 1, but the underlying issue is different.

AzurePack: Unable to create database when using SQL High Availability group (AlwaysOn)- Part 1

We have setup WAP environment and configured a SQL resource provider. WAPSQLCL is the SQL listener that we have added to the AlwaysOn group.
When a tenant is created a new database, it fails with following message logged in tenant portal event logs:

Log Name: Microsoft-WindowsAzurePack-MgmtSvc-TenantSite/Operational
Source: Microsoft-WindowsAzurePack-MgmtSvc-TenantSite
Date: xxxx
Event ID: 12
Task Category: (65522)
Level: Error
Keywords: None
User: IIS APPPOOL\MgmtSvc-TenantSite
Computer: xxxx
Description:
Error:JsonException: Unable to create the database 'test'. Please try again later. If the problem persists, please contact support.

Assessment:

In event logs we could see the detailed error:

Database 'test' could not be created on always on listener 'WAPSQLCL' since it restoration failed from network file share '\\Fileshare\Test' on secondary replica: WAPDB02 with following exception: System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'SqlResourceProviderLogin'.

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

In SQL server error logs we could see the error been logged as:

Login failed for user “sqlResourceProviderLogin”. Reason: password did not match that for the login provided.

Solution:

To sync the password of sqlResourceProviderLogin on both the SQL server nodes, select "Repair connection" option for SQL listener on Admin portal.