Managed Identity Authentication causing issue in Azure SQL Database stored procedure

Question

To disable local auth for Azure SQL, I made changes to my code to use system-assigned Managed Identity of the Azure app service for connection instead of the usual username/password in the connection string.

But now, one of the stored procedures is throwing an error

String or binary data would be truncated

I tried two ways to connect to SQL Server through the Azure app service:

1. Using the new SqlClient NuGet package: Microsoft.Data.SqlClient
2. Using older package (System.Data.SqlClient) and fetching an access token for authentication.

Initially, I thought the issue might be because of the newer NuGet package, but even after modifying the existing code to stick with System.Data.SqlClient and adding extra steps for the token, the issue persists.

Strangely, the problem seems to be tied to Managed Identity authentication. Current service is using connection string with username/password and System.Data.SqlClient nuget in Production environment and is running seamlessly.

I double-checked the stored procedures, but no luck finding issues with values going over limits into the SQL Server tables.

I am not understanding the connection between using ManagedIdentity and failure of stored procedure with a completely different error.

• Version of the solution: .NET 4.7.2
• Version of SQL Server database - v12.0.2000.8

Has anyone else ran into this? Any ideas on how to tackle it would be appreciated.

Answer 1

Problem: The issue corresponds directly to the error message, indicating that we were attempting to insert a string value exceeding the column's capacity. Specifically, we utilized the "SUSER_SNAME()" function to retrieve the currently logged-in user's username and attempted to store this value in a "CreatedBy" column, which is of type varchar. However, the "CreatedBy" column has different character limits across different tables.

Originally, when authentication was managed via username/password, the retrieved values, typically around 15 characters, were comfortably within the column's limits. The problem arose upon transitioning to ManagedIdentity, where the resulting value from "SUSER_SNAME()" took the form "AppId@TenantID", extending to approximately 73 characters, thereby exceeding the column's capacity.

Identifying the root cause of this issue was challenging and time-consuming, as the affected table was not directly referenced in the executed stored procedure. Instead, it was nested within multiple layers of triggers.

Resolution: We've increased the "CreatedBy" column size and standardized this limit across all tables.