Connect to SQL - Using the User Managed Identity

[clientbala]

Currently, I am not able to connect to the SQL database using the user-managed identity in the Azure functions (with the identity set as user managed). Would it be possible to take some additional SinkOption parameters (clientid) so that it can authenticate using the user managed client id.

[ckadluba]

Hi @clientbala! Thanks for getting in touch.

That's right, Azure user managed identities are currently not supported by the sink. You have to use system managed identities. The change you suggest would be an interesting and probably very simple enhancement. Maybe I'll look into this when I have time to do it, but if you have some time and opportunity to implement and test this, a pull request would be very welcome.

If you only need a quick solution for managed identities, I recommend you to create a system managed identity for your Azure Functions app as described here. https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=dotnet

[clientbala]

Thanks @ckadluba ...it would be helpful, if you can assign this to me and I will submit a PR once I complete the change.

[ckadluba]

@clientbala okay, you have it. 🙂

[mastronardif]

Hi Folks, Is this Feature available, I read somewhere in this project it was bein worked on around Dec 2019. It would be nice if you can add "Tenant/Use access Token" to the Options section. It appears the ODBC Msql connection string has an "Access token" in the connection string but I have yet to get that to work. Also from what I have researched I can not fine that you can add this to the connectin string. I think the below would work.

private static string GetAccessToken(string tenantId) { var azureServiceTokenProvider = new AzureServiceTokenProvider(); return azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/", tenantId).GetAwaiter().GetResult(); } var con = new SqlConnection(connectionString); connection.AccessToken = GetAccessToken(TENANT_ID); connection.Open();

Thanks for listening I like the product.

[ckadluba]

@mastronardif MSSqlSink currently only works with Azure system managed id. User managed id is not yet supported as stated by @clientbala in this issue. I currently have no time to implement nor have I researched what would be necessary to implement it. If adding the tennant id is a useful feature too, we are always open for a PR that adds some valuable functionality.

[ckadluba]

@clientbala are you still up for this task? Do you need any help with it? If you have no time or cannot do it for any other reason, it's no problem. But in this case just let me know, so we can remove the assignment and set it "up for grabs".

[ckadluba]

After some research, I believe that using Azure User Managed Identitiy should already be possible with the current sink implementation. I had not yet time to test it but in fact it should be possible by putting the following in the connection string (in addition to enabling the managed identities in the sink options of course).

RunAs=App;AppId={ClientId of user-assigned identity}

Details can be found here:

https://docs.microsoft.com/en-us/dotnet/api/overview/azure/service-to-service-authentication#connection-string-support

[Daniel-Svensson]

If you update the Microsoft.data.sqlclient nuget to at least 3.0 you can use the drivers built in support for MSI including user managed identities.

https://github.com/dotnet/SqlClient/blob/main/release-notes/3.0/3.0.0.md#active-directory-default-authentication-support

[ckadluba]

Fixed in PR #418 / release 5.8.0.