AppFabric Caching with Azure SQL as configuration storage

Question

Can AppFabric Caching Service be settled up with Azure SQL database as storage for cache cluster configuration?

It's definitely supports SQL Server for this puproses as it stated here: https://msdn.microsoft.com/en-us/library/ee790826.aspx, but not a word about Azure SQL support. Azure SQL it's HA technology, so looks like "organically" way to store configuration that should be high available to keep cache cluster functioning with no troubles.

UPDATE. The AppFabric configuration UI does not provide possibility to use SQL credentials to access - only "Intergrated Security" is available option here and it's not an option with Azure SQL Service, but the connection string could be manually edited via DistributedCacheService.exe.config.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
...
  <dataCacheConfig cacheHostName="AppFabricCachingService">
    ...
    <clusterConfig provider="System.Data.SqlClient" connectionString="Server=tcp:azuredbnamehere.database.windows.net,1433;Database=AppFabricConfigHolderTest;User ID=user@azuredbnamehere;Password=password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" />
  </dataCacheConfig>
...
</configuration>

UPDATE 2. To have a point to start from I’ve configured AppFabric to use my local SQL Server and succeeded – I was able to create new caches, check cluster status, manage cache hosts with no issues.

Then I’ve deployed resulting AppFabric DB into the Azure SQL and succeeded after removing Windows user principal from security objects, because Azure SQL does not support it.

Then I’ve manually modified connection string in AppFabric host configuration file (DistributedCacheService.exe.config) to point to the Azure SQL database instead of me local one.

And here I stuck - the Administrative PowerShell SnapIn still tried to connect to my local DB instance. Looks like the information about cluster configuration storage is duplicated and not only DistributedCacheService.exe.config knows about it.

UPDATE 3. I've decomplied utility that does SQL Server registration for AppFabric - DistributedCache.SqlConfiguration.exe. And I've realised what is going on the "registration" stage:

using (PowerShell powerShellHost = PowerShellHelper.GetPowerShellHost())
{
    powerShellHost.AddCommand("Set-CacheConnectionString");
    powerShellHost.AddParameter("Provider", "System.Data.SqlClient");
    powerShellHost.AddParameter("ConnectionString", connectionString);
    PowerShellHelper.TraceCommands(powerShellHost);
    powerShellHost.Invoke();
    PowerShellHelper.TraceErrorsAndWarnings(powerShellHost);
}

And here we are!

PS > Get-Help Set-CacheConnectionString

NAME Set-CacheConnectionString

SYNOPSIS Persists the connection string for temporary usage. The string can then be retrieved with Get-CacheConnectionString. Note that this command does not change the connection string used by the cache cluster configuration store.

As it turned out the cluster configuration storage information is really duplicated. Where it's stored I've realised alter decompiling PowerShell module that performs Set-CacheConnectionString.

using (RegistryKey registryKey = Registry.CurrentUser.CreateSubKey("SOFTWARE\\Microsoft\\AppFabric\\V1.0\\Temp"))

UPDATE 4. The most interesting thing that AppFabric Cache Administrative PowerShell SnapIn uses different registry path (decompilation revealed): HKLM\SOFTWARE\Microsoft\AppFabric\V1.0\Configuration. After modifying ConnectionString property value there I've finally got Use-CacheCluster command working.

The cluster also started and looks operable now.

Answer 1

We can "cheat" and setup AppFabric cache cluster using the approach described in the updates, but, sadly, as it turned out the code to ensure that we do use Integrated Security is sewn into the PowerShell Administrative SnapIn:

// Microsoft.ApplicationServer.Caching.Configuration.ConfigurationBase
...
if (provider.Equals("System.Data.SqlClient"))
{
    ConfigurationBase.ValidateSqlConnectionString(connectionString);
}
...
internal static void ValidateSqlConnectionString(string connectionString)
{
    SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
    if (!sqlConnectionStringBuilder.IntegratedSecurity || !string.IsNullOrEmpty(sqlConnectionStringBuilder.UserID) || !string.IsNullOrEmpty(sqlConnectionStringBuilder.Password))
    {
        int errorCode = 17032;
        string sqlAuthenticationNotSupported = Resources.SqlAuthenticationNotSupported;
        throw new DataCacheException("DistributedCache.ConfigurationCommands", errorCode, sqlAuthenticationNotSupported);
    }
}

And if you try to Register-CacheHost (Add-Host, and some other commands) you'll face following error:

Register-CacheHost : ErrorCode:SubStatus:Only Windows authentication is supportedwith SQL Server provider. Specify a valid connection string for Windows authentication without any User ID or Password.
At line:1 char:1
+ Register-CacheHost -Provider "System.Data.SqlClient" -ConnectionString "Server=t ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Register-CacheHost], DataCacheException
    + FullyQualifiedErrorId : SqlAuthenticationNotSupported,Microsoft.ApplicationServer.Caching.Configuration.Commands.RegisterCacheHostCommand

So, the only real-world option to use Azure SQL as cache cluster configuration holder is to write custom configuration provider from scratch: https://msdn.microsoft.com/en-us/library/ff718169.aspx or try to use the implemented SqlServer provider (Microsoft.ApplicationServer.Caching.SqlServerCustomProvider) under different name to fool PowerShell module (not tested).

UPDATE. I've tested the approach with fooling AppFabric and using the built-in SQL Server provider under different name. It works like a charm.