Issues with SQL Server state store and MSI auth

[ItalyPaleAle]

Originally posted by @giovanidecusati in #1103 (comment)

We are trying to use "state.sqlserver" component configured to use User-Managed-Identity to connect to Azure SQL Server Database instance. Is that supported?

I'm facing this issue when trying to connect to SQL:
[INIT_COMPONENT_FAILURE]: initialization error occurred for <componentName>-statestore (state.sqlserver/v1): failed to create db database: mssql: login error: Login failed for user '<MyUserManagedIdentityClientId>'." app_id=<appid> instance=<revisionInstance> scope=dapr.runtime type=log ver=1.10.4-msft-2

SqlConnectionString:
Server=<MySqlServerInstance>.database.windows.net;Authentication=**Active Directory Managed Identity**;Encrypt=True;User Id=<MyUserManagedIdentityClientId>;Database=<MyDatabaseName>

We are deploying via Bicep Tempalte:
resource resource_daprSecretStore 'Microsoft.App/managedEnvironments/daprComponents@2022-06-01-preview' = { name: statestoreName parent: resource_environment properties: { componentType: 'state.sqlserver' version: 'v1' metadata: [ { name: 'connectionString' secretRef: keyVaultConenctionStringSecretName } { name: 'tableName' value: tableName } { name: 'schema' value: schemaName } ] scopes: [ daprAppName ] secretStoreComponent: secretStoreName } }

Appreciate any help, thanks!

[berndverst]

SQL Server does not yet support authentication via Azure Active Directory as there is no Microsoft Go SDK for this service in the Azure SDK for Go (GitHub.com/azure/azure-sdk-for-go/) As a result, authentication via Azure identities will not be possible.

Also you seem to have a Typo - is this the correct variable name? "keyVaultConenctionStringSecretName" for loading the connection string from your KeyVault instance?

We can keep this issue open as a feature request for the time being.
We can only implement what is possible in the Azure SDK for Go. We can relay this request to the SDK team. From what I understand this is currently not on their roadmap, and may not be prioritized.

[ItalyPaleAle]

It looks like it's something we may be able to support: https://github.com/denisenkom/go-mssqldb#azure-active-directory-authentication