Azure Functions runtime 2 - SQL Connections using SqlClient

Question

Unable to use the .Net SqlClient with the runtime 2. Getting the following error about missing references:

2018-04-09T13:48:51.172 [Information] Compilation failed.

2018-04-09T13:48:58.071 [Information] Script for function 'SqlExecutorActivity' changed. Reloading.

2018-04-09T13:48:58.363 [Error] run.csx(29,5): error CS0246: The type or namespace name 'SqlConnectionStringBuilder' could not be found (are you missing a using directive or an assembly reference?)

2018-04-09T13:48:58.376 [Error] run.csx(29,68): error CS1069: The type name 'SqlConnectionStringBuilder' could not be found in the namespace 'System.Data.SqlClient'. This type has been forwarded to assembly 'System.Data.SqlClient, Version=4.2.0.2, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' Consider adding a reference to that assembly.

2018-04-09T13:48:58.387 [Error] run.csx(71,28): error CS0246: The type or namespace name 'AzureServiceTokenProvider' could not be found (are you missing a using directive or an assembly reference?)

2018-04-09T13:48:58.396 [Error] run.csx(78,27): error CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)

2018-04-09T13:48:58.417 [Error] run.csx(83,30): error CS0246: The type or namespace name 'SqlCommand' could not be found (are you missing a using directive or an assembly reference?)

#r "System.Data"
using System.Threading;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Collections.Generic;
public static async Task<string> Run(string requestData, TraceWriter log)
{
    dynamic data = JsonConvert.DeserializeObject<MaintQueueData>(requestData);
    log.Info($"Doing work in Server {data.Server}.");
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
    builder["Server"] = data.Server;
    builder["Initial Catalog"] = data.Database;
    builder["Connect Timeout"] = 30;
    builder["Trusted_Connection"] = true;
    builder["Integrated Security"] = false;

    log.Info($"{builder.ConnectionString} ... HAHA no passwords br0ski!");

    var token = (String)null;
    var result = (String)null;

    try
    {
        token = await ADAuthentication.GetSqlTokenAsync();
    }catch(Exception e)
    {
        string error = $"Failed to authenticate with AAD: {e.Message}";
        log.Error($"{error}");
        return error;
    }

    try
    {
        result = await ExecuteInSql(builder.ConnectionString, token, data.Query);
    }catch(Exception e)
    {
        string error = $"Failed to execute SQL: {e.Message}";
        log.Error($"{error}");
        return error;
    }

    log.Info($"Query Complete: {data.Query}, Results: {result}");
    return $"Query Complete: {data.Query}, Results: {result}";
}
public static class ADAuthentication
{
    const String SqlResource = "https://database.windows.net/";

    public static Task<String> GetSqlTokenAsync()
    {
        var provider = new AzureServiceTokenProvider();
        return provider.GetAccessTokenAsync(SqlResource);
    }
}

private static async Task<String> ExecuteInSql(String connectionString, String token, String Query)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.AccessToken = token;
        await conn.OpenAsync();
        String text = Query; 
        using (var cmd = new SqlCommand(text, conn))
        {
            var result = await cmd.ExecuteScalarAsync();
            return result as String;
        }
    }
}

Joey Cai · Accepted Answer · 2018-04-10 09:54:52Z

1

I have tested your code in httptrigger and got the same problem with you.

However, I noticed that when the runtime is "beta", it load using Microsoft.AspNetCore.Mvc;package.

If your project platform is .netcore, it is not supported connect to Azure SQL server via AAD Authentication.

So, I suggest you could try to use runtime 1 of azure function to achieve what you want.

Azure Functions runtime 2.0 is in preview, and currently not all features of Azure Functions are supported.

Also, you could use Active Directory password authentication to connect.

To connect to a database using integrated authentication and an Azure AD identity, the Authentication keyword in the database connection string must be set to Active Directory Integrated. The following C# code sample uses ADO .NET.

string ConnectionString =
@"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
SqlConnection conn = new SqlConnection(ConnectionString);
conn.Open();

edited Apr 10, 2018 at 9:54

answered Apr 10, 2018 at 9:49

Joey Cai

20.2k1 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Piroinno Over a year ago

Pls clarify if you got the SQL connection bit to work with runtime2?

Joey Cai Over a year ago

Yes, when I test your code in httptrigger with runtime2 get the same error with you. But I found that when I tested it in blobtrigger, it worked fine.

Piroinno Over a year ago

Thanks. Downgraded to .net461 and published from visual studio and it's now working.

Collectives™ on Stack Overflow

Azure Functions runtime 2 - SQL Connections using SqlClient

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related