App Portal Connection String For Azure
We migrated our App Portal environment to Azure. We are only able to get it to connect to the database by using a plain text user ID and password in the connection strings in the web.config files. We can't leave it with a plain text password. We want to use Active Directory MSI for authentication.
We updated the files in:
- <AppPortal Install Location>\App Portal\Web\web.config
- <AppPortal Install Location>\APM\Web.config
- <AppPortal Install Location>l\Service\ESDService.exe.config
- <AppPortal Install Location>\Utilities\MyApps\AppPortal.App.MyAppsExecutables.exe.config
With this connection string but it gives us an error when we try to start the ESD Service:
<SQLServerName>;Initial Catalog=AppPortalDEV;Authentication="Active Directory MSI";
App Portal could not load its required settings. Error is : Invalid value for key 'authentication'. at System.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value) at System.Data.SqlClient.SqlConnectionString.ConvertValueToAuthenticationType() at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) at System.Data.SqlClient.SqlConnection..ctor(String connectionString, SqlCredential credential) at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at System.Data.SqlClient.SqlDataAdapter..ctor(String selectCommandText, String selectConnectionString) at SelfService.ESDConfig.getConfigValue(String strName) at ESDService.ESDService.GetConfig(Boolean bIsFurstRun)
I have no idea where or how you might check this, but I wonder whether this is because the Microsoft.Data.SqlClient package being used on your server is too old to support the "Active Directory MSI" authentication option.
According to Using Azure Active Directory authentication with SqlClient, support for this option was added in the 2.1.0 release of Microsoft.Data.SqlClient. I believe that this version was released in November 2020.
Thanks for the reply. By default the config files are referencing system.data.sqlclient. How do we change to Microsoft.data.sqlclient within the App Portal application?
It does not look like this will be possible at this time. Throughout the App Broker code, there are hundreds of references are made to System.Data.SqlClient. There are no references to Microsoft.Data.SqlClient. Updating the code to use Microsoft.Data.SqlClient would likely be a significant undertaking. About all I can suggest would be to submit this as an Idea.
I was thinking it wouldn't really be possible to make this work with an Azure SQL instance without product changes. For other customers I know that are running App Broker in "the cloud", they are running a standard SQL Server instance on a VM running in Azure or AWS. This shouldn't require anything special from a connection string perspective.