Add support for Managed Identity access

[Jandev]

At the moment the connectionstring holds all connection information to the database.

For more modern (Azure) scenarios it's a great feature to be using the Managed Identity of a Function App in order to connect & retrieve data from the database.

[jayendranarumugam]

Hi @Jandev ,
I've provided my PR #8 for this feature. Please have a look

[Jandev]

Thanks, this is looking like a nice first step.

The biggest issue I have with this is the dependency on the Microsoft.Azure.Services.AppAuthentication package, which makes this binding a bit bigger for only the Managed Identity scenario.

In order to solve this I was thinking of somehow 'injecting' the functionality to retrieve a token, so the client can take the dependency on a specific version of the Microsoft.Azure.Services.AppAuthentication package (or something else for that matter), as long as a valid Access Token is generated.

Something like:

interface ISqlBindingTokenProvider
{
    Task<string> GetAccessToken();
}

If the client (Function App) has a type registered with the ISqlBindingTokenProvider interface, the following can be implemented

using (var connection = new SqlConnection(attribute.ConnectionString))
{
	// Somehow retrieve the ISqlBindingTokenProvider and if not `null`, invoke the method `GetAccessToken`
	// and add the retrieved value to the `connection
	connection.Open();
	using (var command = connection.CreateCommand())

I haven't done the research on how to inject such a thing though.

I'll add this also to the open issue to make the expectation more clear.
All of this 'complexity' is to make sure the package is as small as possible and we don't introduce dependencies to package versions which the client might not want.

[jayendranarumugam]

Hi @Jandev ,

Thinking about on how to reg the ISqlBindingTokenProvider from the client. I got like this (not sure this will be the correct approach) creating a new binding attribute for SqlServerTokenProvider and all it's new logic inside that binding ?

public static IActionResult GetSingleRecord(
			[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = nameof(GetSingleRecord))]
			HttpRequest req,
			[SqlServer(Query = "SELECT TOP 1 Id, Name, Description FROM MyTable")]
			[SqlServerTokenProvider]

[Jandev]

That's an interesting take on implementing it, indeed.

I was checking out a binding from a friend of mine, the JWT Binding and see it's possible to do Dependency Injection in a binding.

If we can manage to do something like this:

// Have a public interface, which the client can register & implement.
public interface ISqlBindingTokenProvider
{
	Task<string> GetToken();
}

// Inject the service provider in the binding
public SqlServerBinding(IServiceProvider services)
{
	// Retrieve the tokenprovider if it can be found, if not no worries, the binding should still proceed
	var tokenProvider = services.GetService<ISqlBindingTokenProvider>();
}

// And use the tokenprovider if it has been retrieved
using (var connection = new SqlConnection(attribute.ConnectionString))
{
	if (!ContainsUserInformation(attribute.ConnectionString))
	{
		if (this.tokenProvider != null)
		{
			connection.AccessToken = await this.tokenProvider.GetToken();
		}
	}

The Function App using the binding should then add a FunctionsStartup class (if they haven't got one already) and register their own implementation.

[assembly: FunctionsStartup(typeof(Startup))]
namespace My.Awesome.FunctionApp
{
	public class Startup : FunctionsStartup
	{
		public override void Configure(IFunctionsHostBuilder builder)
		{
			builder.Services.AddTransient<ISqlBindingTokenProvider, SqlBindingTokenProvider>();
		}

And have their own implementation of the interface

public class SqlBindingTokenProvider : ISqlBindingTokenProvider
{
	public Task<string> GetToken()
	{
		var tokenProvider = new AzureServiceTokenProvider();
		string accessToken = await tokenProvider.GetAccessTokenAsync("https://database.windows.net/");
		return accessToken;
	}
}

This has to be documented somewhere in the readme as people won't find this functionality any other way.

I haven't tested this code and scribbled this in VS Code, but it looks like this should & could work.

[jayendranarumugam]

I tried out the code @Jandev and got struct at how to injecting the service.As the service are controlled by the client/functions. We only have a interface . So i tried like below.

    public class SqlServerBinding : IExtensionConfigProvider
    {
        private readonly ISqlBindingTokenProvider sqlBindingTokenProvider;

        // Inject the service provider in the binding
        public SqlServerBinding(IServiceProvider services)
        {
            // Retrieve the tokenprovider if it can be found, if not no worries, the binding should still proceed
            var sqlBindingTokenProvider = services.GetService(typeof(ISqlBindingTokenProvider));
        }

....

            if (sqlBindingTokenProvider != null)
            {
                sqlBindingTokenProvider.GetAccessToken()// GetAccessToken couldn't able to access. 
            }

where the GetAccessToken is not able to access

[Jandev]

Yes, this looks about right.

I've also created an implementation, you can find it in this branch: https://github.com/Jandev/azurefunctions-sqlbinding/tree/feature/managed-identy-addition

If the client isn't registering the token provider, the code is omitted. If it is registered, an access token will be retrieved.