c# - Azure函数连接到本地sql访问被拒绝

Question

我遇到一个问题，即在将本地 SQL 数据库发布到 Azure 后，我无法通过 Azure 函数应用连接到该数据库。但是，当我在 Visual Studio Code 中本地运行它时，它运行得很好。

该函数的目的是将存储在 Azure Blob 存储中的图像调整为缩略图大小，并将其存储在表字段 中。

这是我收到的错误消息:

2020-12-15T15:33:52.058 [Information] 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)

2020-12-15T15:33:52.093 [Error] Executed 'BlobTrigger_MQS_Resize' (Failed, Id=346672c6-820c-43f6-b950-d5059e44697e, Duration=19570ms)
Access is denied.

我正在通过 SQL Login 连接到 SQL 数据库，效果非常好。连接字符串位于 local.settings.json 中:

{
  "IsEncrypted": false,
  "Values": {
    ...,
    "Connection_SQL_Database_MQS": "Data Source=MyServer;Initial Catalog=MyDB;User ID=MyUser;Password=MyPassword;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  }
}

我已经简化了一些问题核心的代码(C#):

public static class BlobTrigger_MQS_Resize
    {
        [FunctionName("BlobTrigger_MQS_Resize")]
        public static async Task Run([BlobTrigger("mqs-media/{name}", Connection = "hqdevstorage_STORAGE")]Stream input, string name, ILogger log)
        {
            log.LogInformation($"C# Blob trigger function Processed blob\n Name:{name} \n Size: {input.Length} Bytes");

            try
            {
                var connectionString = Environment.GetEnvironmentVariable("Connection_SQL_Database_MQS", EnvironmentVariableTarget.Process);

                using (var output = new MemoryStream())
                using (Image<Rgba32> image = Image.Load(input))
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    // Code that resizes images to 'output', this works also when published
                    image.Resize(...);           
                    image.Save(output, encoder);

                    //PROBLEM IS HERE: Write resized image to SQL Database
                    conn.Open();
                    var query = "UPDATE dbo.MyTable" 
                    + " SET Blob = @output"  
                    + " WHERE File_Name = '" + name + "'";

                    using(SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@output", output);
                        var rows = await cmd.ExecuteNonQueryAsync();
                        log.LogInformation($"{rows} rows were updated");
                    }
                }
                
            }
            catch (Exception ex)
            {
                log.LogInformation(ex.Message);
                throw;
            }
             
        }
    }

有人可以帮我解决这个问题吗？我不知道为什么它可以在本地运行，但不能在 Azure 上运行。

Answer 1

The server was not found or was not accessible.

这是网络连接问题，而不是权限问题。 Azure 函数无法将 SQL Server 的主机名解析为 IP 地址，并且即使可以连接也无法连接到该 IP 地址。

您的函数需要访问本地连接的 VNet 或使用混合连接来访问本地 SQL Server。

参见

https://learn.microsoft.com/en-us/azure/app-service/app-service-hybrid-connections

和

https://learn.microsoft.com/en-us/azure/azure-functions/functions-create-vnet