SQL Server 2012 & .NET suser_sname() returns different values dependent on accessed from Local IIS or Remote IIS

Question

I'm having an issue with returning the desired username into SQL Server as a default value for a table. All was working well while I was working on my local machine and the remote SQL Server. When I deployed my project this week, I found that my SQL Server 2012 triggers were returning the server name of the IIS server (IIS01) instead of returning the desired value, the username of the user running the web application.

The following table is written to by UPDATE and INSERT triggers for another table. The table design is this:

CREATE TABLE [dbo].[table]
(
    [id] [int] NOT NULL,
    [username] [varchar](50) NULL,
    [dt_changed] [smalldatetime] NULL,
    [actiontype] [char](1) NULL
)
GO

ALTER TABLE [dbo].[table] 
    ADD CONSTRAINT [DF_table_username] DEFAULT suser_sname()) FOR [username]
GO

ALTER TABLE [dbo].[table] 
    ADD CONSTRAINT [DF_table_dt_changed] DEFAULT (getdate()) FOR [dt_changed]
GO

When I write to the triggered table via .NET running on IIS Express Visual Studio, it inserts the correct username into the table,

 id  username        dt_changed          actiontype
 534 domain\drewl    2017-11-07 13:39:00 I

When I write to the triggered table from .NET running on IIS on a remote IIS01 server, it inserts an incorrect username into the table. The username inserted is the servername of the domain.

 id  username        dt_changed          actiontype
 533 domain\IIS01$   2017-11-07 11:16:00 I

In .NET, I am using a couple different ways of getting the username of the user running the script,

System.Web.HttpContext.Current.User.Identity.Name
<asp:LoginName runat="server" />

Both of these return the correct user on both the local IIS Express and the remote IIS01 IIS server.

In order to rule out that I was not using the wrong SQL command for the username, I wrote this bit of C# to show me the values for each.

 string ResourceLoginName = Convert.ToString(System.Web.HttpContext.Current.User.Identity.Name);
 string UsernameOnly = ResourceLoginName.Split('\\')[1];
 detectedusername.InnerHtml = UsernameOnly;
 SqlConnection con = new SqlConnection();
 con.ConnectionString = ConfigurationManager.ConnectionStrings["RMConnectionString"].ConnectionString;
 con.Open();
 SqlCommand cmd = new SqlCommand("SELECT 'ORIGINAL_LOGIN()' AS UserFunction, ORIGINAL_LOGIN() AS UserValue UNION SELECT 'SUSER_NAME(SUSER_ID())' AS UserFunction, SUSER_NAME(SUSER_ID()) AS UserValue UNION SELECT 'SUSER_SNAME(SUSER_SID())' AS UserFunction,SUSER_SNAME(SUSER_SID()) AS UserValue UNION SELECT 'SYSTEM_USER' AS UserFunction,SYSTEM_USER AS UserValue UNION SELECT 'CURRENT_USER' AS UserFunction,CURRENT_USER AS UserValue UNION SELECT 'USER_NAME(USER_ID())' AS UserFunction,USER_NAME(USER_ID()) AS UserValue UNION SELECT 'SESSION_USER' AS UserFunction,SESSION_USER AS UserValue UNION SELECT 'USER' AS UserFunction,USER AS UserValue", con);
 cmd.CommandType = CommandType.Text;
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataTable dt = new DataTable();
 da.Fill(dt);
 Users.DataSource = dt;
 Users.DataBind();

As above, the IIS Express returns the correct usernames,

.NET
drewl
SQL
Function                    Value
CURRENT_USER                dbo
ORIGINAL_LOGIN()            MTROGERS\drewl
SESSION_USER                dbo
SUSER_NAME(SUSER_ID())      MTROGERS\drewl
SUSER_SNAME(SUSER_SID())    MTROGERS\drewl
SYSTEM_USER                 MTROGERS\drewl
USER                        dbo
USER_NAME(USER_ID())        dbo

While the remote IIS01 IIS server returns,

.NET
drewl
SQL
Function                    Value
CURRENT_USER                domain\IIS01$
ORIGINAL_LOGIN()            domain\IIS01$
SESSION_USER                domain\IIS01$
SUSER_NAME(SUSER_ID())      domain\IIS01$
SUSER_SNAME(SUSER_SID())    domain\IIS01$
SYSTEM_USER                 domain\IIS01$
USER                        domain\IIS01$
USER_NAME(USER_ID())        domain\IIS01$

What am I missing here and how can I get it to work in the remote (PROD) IIS like it does in IIS Express?

Can you post the user name associated with the app pool you have configured for the web app on your local iis instance? — Ross Bush, Nov 07 '17 at 21:02
This may be relevant: https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/ — Alex, Nov 07 '17 at 21:57
@RossBush The local IIS instance is IIS Express. I found the AppPool for the IIS Express project I am using to be,
`` @Alex I tried the link provided, and redid the steps there, but DOMAIN\ServerName$ is still be passed as the username. — Drew Laing, Nov 08 '17 at 12:35

Drew Laing · Answer 1 · 2017-11-08T13:44:24.040

After some more head scratching and research, I found this Stack Overflow question\answer,

I changed my WebConfig to use impersonation,

<identity impersonate="true" />

Set the AppPool to Classic mode (from Integrated), then followed Mike Pollitt's answer, to use Basic Authentication instead of Windows Authentication.

In IIS, only Basic Authentication logs users on with a security token that flows across the network to a remote SQL server. By default, other IIS security modes used in conjunction with the identity configuration element settings will not result in a token that can authenticate to a remote SQL Server.In IIS, only Basic Authentication logs users on with a security token that flows across the network to a remote SQL server. By default, other IIS security modes used in conjunction with the identity configuration element settings will not result in a token that can authenticate to a remote SQL Server.

Once I changed this and restarted the website in IIS, it now returns the correct username.

SQL Server 2012 & .NET suser_sname() returns different values dependent on accessed from Local IIS or Remote IIS

1 Answers1