Azure function connect to on-premises sql access denied

Question

I've got an issue where I cannot connect to an on-premises SQL database through an Azure function app once it is published to Azure. However when I run it locally in Visual Studio Code, it works perfectly.

The purpose of the Function is to resize images that are stored in Azure blob storage to a thumbnail and store it in a table field <varbinary(max)>.

Here is the error message I get:

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.

I'm connecting to the SQL database via SQL Login, which works perfectly. Connection String is in 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"
  }
}

The code (in C#) which I have simplified a bit to the core of the issue:

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

Can someone help me with this? I have no clue why it is working locally but not on Azure.

First, please fix that huge security flaw you have. You clearly know about parametrisation so why are you parametrising @output but injecting name? — Larnu

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2020-12-15T16:36:59

The server was not found or was not accessible.

This is a network connectivity issue, not a permissions issue. The Azure function cannot resolve your SQL Server's hostname into an IP address, and would not be able to connect to that IP address if it could.

Your function would need to access to an on-prem conected VNet or use a Hybrid Connection to reach your on-prem SQL Server.

see

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

and

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

Azure function connect to on-premises sql access denied

1 Answers