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

Answer 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();