I attempted to validate the installation of AwareIM v9 on Azure SQL Database in Hyperscale mode, but we found that it’s not feasible to proceed with this configuration. The primary reason is that Azure SQL Database does not allow the use of a single login with full privileges to manage the creation or administration of multiple databases. Instead, a specific user is required for each database, which limits our configuration options.
We conducted an additional test by trying the installation on SQL Managed Instance and then manually restoring a backup to a pre-created database in Azure SQL Database. However, upon launching AwareIM, we encountered the following error:
USE statement is not supported to switch between databases. Use a new connection to connect to a different database.
This error arises because, in Azure SQL Database, the USE command is not supported for switching between databases within the same connection. Instead, it requires a new, specifically configured connection for each required database.
AwareIM, due to its limitations, requires the ability to switch between the "main" and "test" databases within the same connection, which is not feasible in Azure SQL Database. This restriction prevents proper deployment, as AwareIM does not allow defining specific connection strings for each database, as illustrated in the attached image.
Additionally, we confirmed that although the BASServer.props file is configured with connection strings generated by AwareIM for each database, it appears that the product internally continues to use the USE command to select databases. This causes the error because this feature is incompatible with Azure SQL Database, where switching databases within the same connection is not permitted.
Here is a link that describes the error in detail: https://support.revvitysignals.com/hc/en-us/articles/4408236343188-Error-USE-statement-is-not-supported-to-switch-between-databases-when-setting-up-an-Information-Services-data-source-to-Azure-SQL-Database.
Has anyone else attempted this configuration and encountered the same issue? I would appreciate any guidance or alternative solutions that have been implemented.
Best regards.
