0

I'm trying to run a SELECT query from my sql server through an Azure Function App so I can later work on the data pulled from it. I'm fairly new to Azure so I might be missing something. My Runtime version is ~3. It's a C# HTTP Trigger Function.

My code is based entirely on this blog entry https://randypaulo.com/2018/12/19/querying-azure-sql-database-using-azure-functions-2-0-to-return-json-data/

This is the part where it breaks, at conn.Open();

try 
        {
            var str = Environment.GetEnvironmentVariable("sqldb_connection");
            
            using(SqlConnection conn =new SqlConnection(str))
                {
                    using(SqlCommand cmd =new SqlCommand())
                        {
                            SqlDataReader dataReader;
                            cmd.CommandText = "SELECT TOP 10 ID FROM CATALOG";
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;
                            conn.Open(); 
                            dataReader = cmd.ExecuteReader();
                            var r = Serialize(dataReader);
                            json = JsonConvert.SerializeObject(r, Formatting.Indented);
                        }

                    }
        }

The function compiles successfully with around 10+ warnings that look like this.

2021-02-01T21:54:58.159 [Warning] warning CS1701: Assuming assembly reference 'System.Data.Common, Version=4.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' used by 'System.Data.SqlClient' matches identity 'System.Data.Common, Version=4.2.2.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' of 'System.Data.Common', you may need to supply runtime policy

However, when it comes to actually running the query, at the conn.Open(); line. I get a really long error message. But it says "Unable to load DLL 'sni.dll' or one of its dependencies:"

2021-02-01T21:57:29.802 [Information] The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.
2021-02-01T21:57:29.803 [Information] System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SNILoadHandle' threw an exception.---> System.DllNotFoundException: Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)at System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)at System.Data.SqlClient.SNILoadHandle..ctor()at System.Data.SqlClient.SNILoadHandle..cctor()--- End of inner exception stack trace

I'm running on some limitations so I'm gonna list them out:

  • Can't use Visual Studio/Visual Studio Code.
  • Can only develop on portal
  • Can't switch to runtime ~2
  • Can't create new resources

Here is what I've tried so far

  • Using Microsoft.Data.SqlClient instead > I can't install packages. (Package Reference successfully installs it, but it shows as missing when compiling)
  • Created a function.proj file and listed Package References > It runs and restores packages, but they don't seem to be used by my run.csx.
  • Removed and added packages with dotnet add package System.Data.SqlClient

What works:

  • The Connection String > I successfully ran a query using sqlcmd in the Console.

My questions:

  • Is there a way to copy the sni.dll from the \shared\Microsoft.AspNetCore.App\2.2.14 folder to the \shared\Microsoft.AspNetCore.App\3.1.11 folder? (no success through the console, forbidden)

  • How can I reference/install custom or specific libraries through the Azure portal? (like #r "Newtonsoft.Json" or #load "file.csx")

  • Do I need to mention the function.proj file I created in my run.csx file for it to work? How?

Al P
  • 1
  • 2
  • Hello and welcome. Could you reproduce a piece of your own code and put it in the question. since it is based on the code from link but it is not the same hence we can't analyze problem correctly. At least write the SQL part that you are trying to run. – AntiqTech Feb 01 '21 at 23:02
  • 1
    I edited it in! Hope it helpsl I didn't modify it a lot. I also tried opening the communications like cmd.Connection.Open() to the same result. – Al P Feb 01 '21 at 23:13
  • Please write your system specs. Windows 10? 8?7? You need to check net core 3 installation maybe reinstall? Problem might be about prerequisites for .net core 3 – AntiqTech Feb 01 '21 at 23:26
  • Also check this discussion : https://github.com/Azure/app-service-announcements-discussions/issues/9 There might be something useful for you – AntiqTech Feb 01 '21 at 23:29
  • It's running entirely on the azure server, in a Windows environment. None of this is running in my system and I can only use the Azure Portal. I checked that solution out earlier but I can't figure out what they meant by adding the package explicitly. – Al P Feb 02 '21 at 00:05
  • Can you build a project in VS on windows and deploy it yo Azure portal ? is that an option for you? – AntiqTech Feb 02 '21 at 00:09
  • I've found a stackoverflow entry with the same exception: https://stackoverflow.com/questions/60518486/azure-functions-unable-to-load-dll-sni-dll-or-one-of-its-dependencies-the-sp check it out, maybe it will be helpful. – AntiqTech Feb 02 '21 at 00:10
  • 1
    Thank you a lot for your help! I ended up switching the application setting "FUNCTION_EXTENSION_VERSION" to 2 instead of 3. Which changed the function runtime settings to ~2. And now the function is working correctly! – Al P Feb 02 '21 at 16:42
  • I'm glad you've found a solution. Since there is no answer posted, please add this solution at the end of your question with bold style so the people reading this question can also see that there is solution for this. – AntiqTech Feb 02 '21 at 17:11

1 Answers1

0

I manually switched the application setting "FUNCTION_EXTENSION_VERSION" to 2 instead of 3. Which changed the function Runtime version settings to ~2.

And now the function is working correctly!

Al P
  • 1
  • 2