Entity Framework saves connection string user ID to database instead of assigned value

Question

I am using ASP.NET Core 6 with Entity Framework, and SQL Server is the database server. I am getting the Active Directory username of the person logged into the web app, and I am trying to save that username to the database so that we can keep track of who makes changes to records.

However, instead of saving the username (like JMyers), it is saving the user ID that is configured in the SQL connection string.

Here is one of the functions where I save the username to the database:

public void UpdateEmployeeStatus(int employeeNumber, string lastName)
{
    string userName = GetADUsername(); //this function gets and formats the username.  The output would be something like JMyers

    PortalUser profileToUpdate = new PortalUser();

    // Get the profile that will be updated
    profileToUpdate = portalDb.PortalUsers
                              .First(u => u.EmployeeNumber == employeeNumber
                                          && u.LastName == lastName);

    if (profileToUpdate != null)
    {
        profileToUpdate.Active = false;
        profileToUpdate.AudtDate = DateTime.Now;
        profileToUpdate.AudtTime = DateTime.Now;
        profileToUpdate.AudtUser = userName;

        portalDb.SaveChanges();
    }
}

Don't worry about the odd Date and Time fields storing the same value. It's something weird that my employer wants.

All other fields save properly like the date, time, and Active flag. When I run the debugger, the value of the userName variable is JMyers. Also, the value of profileToUpdate.AudtUser is also JMyers. However, the database somehow stores un_BlahBlah in the AudtUser field. That is the user ID in the connection string (I changed it in this post for security reasons).

Here is the connection string, with values, changed for security purposes:

"Server=Stark;Initial Catalog=Jarvis;Persist Security Info=False;User ID=un_BlahBlah;Password=MickeyMouse;MultipleActiveResultSets=true;Integrated Security=true;Encrypt=True;Connection Timeout=90;"

The web app is also running in Azure as an App Service but the same behavior takes place both locally when I debug in Visual Studio, and when it's live in Azure.

CaptainGenesisX · Answer

I dig some more digging & discovered there was a "Scalar-Valued Function" setup that took the value I pass it (JMyers) and changes it to JMyers instead of the un_BlahBlah that was in the connection string.

If it helps anyone in the future, this is the function that SQL was using:

ALTER FUNCTION [dbo].[ufn_LogUserName] 
(
    @UserName nvarchar(25)
)
RETURNS nvarchar(25)
AS
BEGIN
    DECLARE @Current_System_User_Name NVARCHAR(255) = dbo.ufn_sys_getsystemuser()

    DECLARE @RetVal nvarchar(25)
    -- Declare the return variable here

    IF upper(@Current_System_User_Name) = 'WEB_API' OR upper(@Current_System_User_Name) like 'un_%' 
        BEGIN
        IF @UserName IS NOT NULL
            set @RetVal = @UserName
        else
            set @RetVal = @Current_System_User_Name
        END
    ELSE
            set @RetVal =  @Current_System_User_Name

    RETURN @RetVal

END

I appreciate everyone's feedback and suggestions. It helped me dig through stored procedures, functions, and triggers to see what was happening.

Entity Framework saves connection string user ID to database instead of assigned value

1 Answers