App Portal Connection String For Azure

[joan_mckinley]

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";

Error is:

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)

Thanks,

Joan

[CharlesW · Accepted solution]

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. 

[ChrisG]

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.

[joan_mckinley]

Hi Chris, 

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?

Thanks,

Joan

[CharlesW · Accepted solution]

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. 

[jdempsey]

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.

[jdempsey · Accepted solution]

I just set this up in Azure over the weekend.  I created a VM using one of the pre-built SQL BYOL images and am connecting App Broker to it with the following connection string:

Data Source=(local);Initial Catalog=AppBroker;Integrated Security=True;

In my case, I just put App Broker and SQL on the same VM, but this would have also worked on separate VMs by substituting the SQL server name in place of "(local)" in the connection string.

[joan_mckinley]

Thanks jdempsey!  We ended up switching our SQL Server to IaaS instead of PaaS.  It worked after that.