Azure functions + Azure SQL : session provider error 19 - physical connection is not usable

Question

This starting to occcur today... I was searchiong for a solution, but I just can find it using in different scenarions like outside the azure functions or using a different client library. Anyway...

This is my client:

using System;
using endpoints3t.Models;
using System.Data.SqlClient;

namespace endpoints3t.DataAccess
{
    internal class MssqlClient
    {
        public MsSqlServer Client;
        private string thisEnv = Environment.GetEnvironmentVariable("ThisEnvironment");
        public MssqlClient()
        {
            var str = Environment.GetEnvironmentVariable($"MsSql_{thisEnv}_ConnectionString");
            SqlConnection c = new SqlConnection(str);
            Client = new MsSqlServer()
            {
                Client = c
            };
        }
    }
}

And this is a sample of a simple call:

public async Task<List<Something>> GetSomeData()
        {
            if (msSQL.Client.Client.State == System.Data.ConnectionState.Closed)
            {
                msSQL.Client.Client.Open();
            }
            using (SqlCommand cmd = new SqlCommand("ExecMyStoredProcedure", msSQL.Client.Client))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                var reader = await cmd.ExecuteReaderAsync();
                while (reader.Read())
                {
                    var item = new Something()
                    {
                        Id = Guid.Parse(reader["Id"].ToString())
                    };
                    result.Add(item);
                }
            }
            return result;
        }

According to documentation, System.Data.SqlClient controls the open/close of my connection and maybe for this reason I can't find much documentation about how to handle this situation. Any help ?

score 1 · Answer 1 · answered Oct 14 '22 at 13:08

If you want to connect any services outside the azure function, you have to inject that your function.

Workaround follows

Here I have created azure function and created connection class to Open connection and inserting to SQL Database.

Note: When you try to connect your Azure SQL server you have access to connect. Make sure your IP has added in a firewall.

enter image description here

Trigger Function

public class Function1
    {
        private readonly connectdb getconnection;
        public Function1(connectdb getconnection)
        {
            this.getconnection = getconnection ?? throw new ArgumentNullException(nameof(getconnection));
        }
        [FunctionName("Function1")]
        public async Task Run([TimerTrigger("0 */1 * * * *")]TimerInfo myTimer, ILogger log)
        {
            // Get the connection string from app settings and use it to create a connection.
            var str = Environment.GetEnvironmentVariable("sqldb_connection");
            var resconn = await getconnection.Getconn(str);
            log.LogInformation("$\"{resconn} rows were updated\"" + resconn);
        }
    }

Connection.cs

public async Task<int> Getconn(string myconn)
        {
            using (SqlConnection conn = new SqlConnection(myconn))
            {
                conn.Open();
                var text = " Insert into <table name>(col2,col2,col3) Values('AAA','BBB','CCC')";

                using (SqlCommand cmd = new SqlCommand(text, conn))
                {
                    // Execute the command and log the # rows affected.
                    var rows = await cmd.ExecuteNonQueryAsync();
                    return rows;
                }
            }
        }

Result