Connection strings and app settings not working properly on Azure App Service

[davidjrh]

Description of bug

When setting the SiteSqlServer connection string through the App Service configuration settings, there are some areas where this setting is ignored in DNN and the connection string stored on the web.config file is used. This is because DNN Platform is currently using WebConfigurationManager class instead of System.Configuration.ConfigurationManager. This second class allows to override AppSettings and ConnectionStrings from the settings specified on the App Service by reading the values on the environment variables first.

IMPORTANT: the idea is to store the connection string and other secrets on Azure Key Vault by allowing the App Service to read the settings using a managed identity. All this effort to securely store the credentials on AKV goes to the trashcan if the connection string still needs to be specified in the web.config file.

Steps to reproduce

List the precise steps to reproduce the bug:

1. Deploy a DNN Platform website on Azure App Service (and SQL Database)
2. Set the connection string on the Azure App Service settings
3. Change the connection string on the web.config to a non existant server (i.e. Data Source=localhost;Initial Catalog=mydatabase;User ID=myuser;Password=mypass) or even leave it blank
4. Load the site and check the Log4net errors under Portals_default\Logs

Current behavior

Currently thousands of errors are logged like this one if you target to a non existant SQL Server instance:

2020-10-20 16:53:22.893+00:00 [RD501AC513D57E][D:3][T:11][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.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 (0x80004005): The system cannot find the file specified
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at PetaPoco.Database.OpenSharedConnection()
   at DotNetNuke.Data.PetaPoco.PetaPocoExt.ExecuteReader(Database database, String sql, Object[] args)
   at DotNetNuke.Data.PetaPoco.PetaPocoHelper.ExecuteReader(String connectionString, CommandType type, Int32 timeoutSec, String sql, Object[] args)
   at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteReader(String connectionString, CommandType commandType, Int32 commandTimeout, String procedureName, Object[] commandParameters)
   at DotNetNuke.Data.SqlDataProvider.ExecuteReader(String procedureName, Object[] commandParameters)
   at DotNetNuke.Data.DataProvider.GetServers()
   at DotNetNuke.Entities.Host.ServerController.GetServersCallBack(CacheItemArgs cacheItemArgs)
   at DotNetNuke.Common.Utilities.DataCache.GetCachedDataFromRuntimeCache(CacheItemArgs cacheItemArgs, CacheItemExpiredCallback cacheItemExpired)
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:2,State:0,Class:20

Or like this one if you just clear the connection string value:

2020-10-20 20:34:54.093+00:00 [RD2818786F3748][D:2][T:6][ERROR] DotNetNuke.Services.Exceptions.Exceptions - System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at DotNetNuke.Data.DataProvider.get_ConnectionString()
   at DotNetNuke.Data.SqlDataProvider.ExecuteReader(String procedureName, Object[] commandParameters)
   at DotNetNuke.Data.DataProvider.GetLogTypeInfo()
   at DotNetNuke.Services.Log.EventLog.DBLoggingProvider.<>c.<GetLogTypeInfo>b__19_0(CacheItemArgs c)
   at DotNetNuke.Common.Utilities.DataCache.GetCachedDataFromRuntimeCache(CacheItemArgs cacheItemArgs, CacheItemExpiredCallback cacheItemExpired)

Expected behavior

No errors should appear on the logs, the connection string set on the App Service configuration should override the settings on the web.config file.

Error information

Use of WebConfigurationManager instead of System.Configuration.ConfigurationManager on the DotNetNuke.Library. I will provide a pull request to fix just the app settings and connection strings issue.

Affected version

• 10.00.00 alpha build
• 09.08.00 release candidate
• 09.07.02 latest supported release

[bdukes]

The remarks section for WebConfigurationManager states

Using WebConfigurationManager is the preferred way to work with configuration files related to Web applications. For client applications, use the ConfigurationManager class.

Does anyone know any specifics of what is different between the two, and when WebConfigurationManager might do something more desirable in a web environment?

[davidjrh]

@bdukes Here is a blog post describing the issue http://www.schwammysays.net/configurationmanager-vs-webconfigurationmanager-on-azure/

I've added a PR with the changes affecting only the AppSettings and ConnectionStrings, tested on a 9.7.2 website running on Azure App Service and a second local one under IIS, works OK for both environments.

[bdukes]

According to some random StackOverflow post, the main difference is that ASP.NET config can be overridden by path. For example, if you have a web.config in a subfolder, it could be used to override settings or connection strings for requests to that folder. However, since DNN handles every request via Default.aspx in the root, this overriding isn't used very much, unless an extension is adding extra aspx pages. Especially for retrieving DNN's connection string, I think this makes sense.

[mitchelsellers]

If we lose overriding in a sub-folder it could be a huge breaking change as it is a default behavior.

I'm not sure what the actual difference is with this though

[davidjrh]

I've tested the overriding the web.config on sub-folders and works as expected (if you override the web.config in a subfolder, even overrides the settings specified on the Azure App Settings). Also tested a MVC module that uses the "DesktopModules\MVC\web.config" and works OK.