"sni.dll" not found while using System.Data.SqlClient using Azure C#

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?

c#
.net
azure
system.data.sqlclient
asked on Stack Overflow Feb 1, 2021 by Al P • edited Feb 1, 2021 by Al P

1 Answer

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!

answered on Stack Overflow Feb 2, 2021 by Al P

User contributions licensed under CC BY-SA 3.0