如何通过代码将Azure WebJob/AppService连接到指定Azure数据库

场景说明 #

基于Azure WebJobs SDK创建WebJob，用于自动创建SQLite数据库并从指定Azure数据库同步数据。所有Azure数据库结构一致，使用Microsoft.Datasync.Client和Microsoft.Datasync.Client.SQLiteStore实现同步。需求是根据队列触发消息中的数据库名称，动态连接对应的Azure数据库完成同步，但当前执行Pull操作时出现SSL/TLS协议错误，同时动态连接逻辑需要完善。

现有代码 #

队列触发代码 #

public static void ProcessQueue([QueueTrigger("queue_trigger")] string message, ILogger logger)
{
    try
    {
        if (message.StartsWith("[DB_CREATION]_", StringComparison.OrdinalIgnoreCase))
        {
            message = message.Replace("[DB_CREATION]_", "");
            ProcessSqliteDbCreation(message);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception in connection establishment: " + ex.Message);
    }
}

SQLite创建及同步代码 #

private static void ProcessSqliteDbCreation(string azureDBName)
{
    //Create local dir
    DirectoryInfo di = Directory.CreateDirectory(_DefaultLocalDBPath);

    //get projectdbnameon mobile using projectdbname
    var sqlLiteDBName = GetProjectDbNameOnMobile(azureDBName);

    // Set path of database file
    string DbFile = $"{_DefaultLocalDBPath}{sqlLiteDBName}_{Guid.NewGuid()}.db"; 

    // Create database file
    File.WriteAllBytes(DbFile, new byte[0]);

    string offlineConnectionString = new UriBuilder { Scheme = "file", Path = DbFile, Query = "?mode=rwc" }.Uri.ToString();
    var store = new OfflineSQLiteStore(offlineConnectionString); //"file:" is required

    // Define structure of all tables of database
    DefineTables(store); 

    var options = new DatasyncClientOptions
    {
        HttpPipeline = new DelegatingHandler[] { new DbNameHandler(azureDBName) },
        OfflineStore = store,
        SerializerSettings = new Microsoft.Datasync.Client.Serialization.DatasyncSerializerSettings()
        {
            CamelCasePropertyNames = false,
            DateTimeZoneHandling = DateTimeZoneHandling.Local
        },
    };
    var client = new DatasyncClient(aimWebUtilityUrl, options);

    client.InitializeOfflineStoreAsync();

    // Method for pulling data from Azure DB to new SQLite DB
    KeepPulling(client); //SEE METHOD BELOW

    // Upload created database file on blob storage
    if (File.Exists(DbFile)) CreateAndUploadDbZip(sqlLiteDBName, DbFile);
}

public static void KeepPulling(DatasyncClient client)
{
    ParallelOptions po = new ParallelOptions();
    po.MaxDegreeOfParallelism = 5;
    Parallel.Invoke(po,
        async () => await PullOneAsync<Table1>(client), //SEE METHOD BELOW
        async () => await PullOneAsync<Table2>(client)
        );
}

// Pull data from server one by one 
static async Task PullOneAsync<T>(DatasyncClient client)
{
    ReadOnlyCollection<TableOperationError> syncErrors = null;

    try
    {
        var table = client.GetOfflineTable<T>();
        table.PullItemsAsync(table.CreateQuery()).Wait(); //ERROR OCCURS HERE
    }
    catch (PushFailedException exc)
    {
        if (exc.PushResult != null) syncErrors = (ReadOnlyCollection<TableOperationError>)exc.PushResult.Errors;
    }

    // Simple error/conflict handling
    if (syncErrors != null)
    {
        // ... 现有错误处理逻辑
    }
}

报错信息 #

System.AggregateException HResult=0x80131500 Message=One or more errors occurred. (The SSL connection could not be established, see inner exception.)

Inner Exception 1: HttpRequestException: The SSL connection could not be established, see inner exception.

Inner Exception 2: AuthenticationException: Authentication failed because the remote party sent a TLS alert: 'ProtocolVersion'.

Inner Exception 3: Win32Exception: The message received was unexpected or badly formatted.

解决方案 #

1. 修复TLS协议版本问题 #

错误根源是运行环境默认TLS版本过低，不被Azure服务支持，需强制使用TLS 1.2或更高版本。在ProcessSqliteDbCreation方法开头添加：

// 强制启用TLS 1.2和1.3
System.Net.ServicePointManager.SecurityProtocol = 
    System.Net.SecurityProtocolType.Tls12 | System.Net.SecurityProtocolType.Tls13;

2. 完善动态数据库连接逻辑 #

实现DbNameHandler将数据库名称传递给后端服务（根据后端识别方式调整）：

public class DbNameHandler : DelegatingHandler
{
    private readonly string _databaseName;

    public DbNameHandler(string databaseName)
    {
        _databaseName = databaseName ?? throw new ArgumentNullException(nameof(databaseName));
    }

    protected override async Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
    {
        // 方案1：通过请求头传递数据库名称（需后端配合读取）
        request.Headers.Add("X-Target-Database", _databaseName);

        // 方案2：通过URL参数传递（如果后端以此识别）
        // var uriBuilder = new UriBuilder(request.RequestUri);
        // uriBuilder.Query = $"db={Uri.EscapeDataString(_databaseName)}&{uriBuilder.Query.TrimStart('?')}";
        // request.RequestUri = uriBuilder.Uri;

        return await base.SendAsync(request, cancellationToken);
    }
}

3. 修正异步代码死锁问题 #

现有代码使用Task.Wait()和Parallel.Invoke结合异步方法会导致死锁，需全部改为异步调用：

修改ProcessSqliteDbCreation为异步方法

private static async Task ProcessSqliteDbCreation(string azureDBName)
{
    // 强制TLS版本
    System.Net.ServicePointManager.SecurityProtocol = 
        System.Net.SecurityProtocolType.Tls12 | System.Net.SecurityProtocolType.Tls13;

    DirectoryInfo di = Directory.CreateDirectory(_DefaultLocalDBPath);
    var sqlLiteDBName = GetProjectDbNameOnMobile(azureDBName);
    string DbFile = $"{_DefaultLocalDBPath}{sqlLiteDBName}_{Guid.NewGuid()}.db"; 
    File.WriteAllBytes(DbFile, new byte[0]);

    string offlineConnectionString = new UriBuilder { Scheme = "file", Path = DbFile, Query = "?mode=rwc" }.Uri.ToString();
    var store = new OfflineSQLiteStore(offlineConnectionString);
    DefineTables(store); 

    var options = new DatasyncClientOptions
    {
        HttpPipeline = new DelegatingHandler[] { new DbNameHandler(azureDBName) },
        OfflineStore = store,
        SerializerSettings = new Microsoft.Datasync.Client.Serialization.DatasyncSerializerSettings()
        {
            CamelCasePropertyNames = false,
            DateTimeZoneHandling = DateTimeZoneHandling.Local
        },
    };
    var client = new DatasyncClient(aimWebUtilityUrl, options);

    // 异步初始化离线存储
    await client.InitializeOfflineStoreAsync();

    // 异步执行数据拉取
    await KeepPullingAsync(client);

    if (File.Exists(DbFile)) CreateAndUploadDbZip(sqlLiteDBName, DbFile);
}

修改KeepPulling为异步方法，用Task.WhenAll替代Parallel.Invoke

public static async Task KeepPullingAsync(DatasyncClient client)
{
    // 并行拉取多表数据
    await Task.WhenAll(
        PullOneAsync<Table1>(client),
        PullOneAsync<Table2>(client)
    );
}

修改PullOneAsync去掉Wait()，改用await

static async Task PullOneAsync<T>(DatasyncClient client)
{
    ReadOnlyCollection<TableOperationError> syncErrors = null;

    try
    {
        var table = client.GetOfflineTable<T>();
        // 异步拉取数据，避免死锁
        await table.PullItemsAsync(table.CreateQuery());
    }
    catch (PushFailedException exc)
    {
        if (exc.PushResult != null) 
            syncErrors = exc.PushResult.Errors as ReadOnlyCollection<TableOperationError>;
    }

    if (syncErrors != null)
    {
        // 错误/冲突处理示例
        foreach (var error in syncErrors)
        {
            Console.WriteLine($"Sync error for {error.Item.GetType().Name}: {error.Message}");
            await error.CancelAndDiscardItemAsync();
        }
    }
}

更新队列触发方法为异步

public static async Task ProcessQueue([QueueTrigger("queue_trigger")] string message, ILogger logger)
{
    try
    {
        if (message.StartsWith("[DB_CREATION]_", StringComparison.OrdinalIgnoreCase))
        {
            message = message.Replace("[DB_CREATION]_", "");
            await ProcessSqliteDbCreation(message);
        }
    }
    catch (Exception ex)
    {
        logger.LogError(ex, "Exception during database creation and sync");
    }
}

4. 后端兼容性验证 #

确保Azure数据库服务支持TLS 1.2+，并且后端能正确识别DbNameHandler传递的数据库名称参数，完成对应数据库的路由。

内容的提问来源于stack exchange，提问作者E. A. Bagby