AppPoolIdentity local SQL Server LoginRSS

7 replies

Last post Apr 19, 2018 08:00 PM by Falk0n

  • AppPoolIdentity local SQL Server Login

    Apr 17, 2018 04:36 AM|Falk0n|LINK

    I'm having a bit of trouble with getting an ASP.NET application to login to SQL Server using Windows Authentication.  IIS is passing machine credentials to the local installation of SQL Server (same VM) instead of the Application Pool Identity.  This is a site on IIS 10, connecting to SQL Server 2016, and running on Server 2016.  Prior to taking on this project, I have very little experience in IIS.  I've done Windows and Linux sysadmin work, but I have not worked with IIS. 

    I understand what I'm trying to accomplish here, but my lack of IIS experience means there's probably something I'm missing.  I can see in SQL Server Logs on Windows logs tons of authentication failures with it trying to pass machine credentials (domain\machinename$) instead of the Application Pool Identity.  I would understand if this were a remote SQL instance, but it's on the same machine. 

    I've followed what was laid out here: https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/

    I've also read several posts from here, Stack Exchange, and Server Fault.  Task manager shows w3wp is running under the Application Pool Identity, not Network Service. I have set various settings in the IIS site configuration and application configurations. 

    There is backstory to this.  I'm working with a network management application that I did not develop.  This is being set up as a standalone configuration with no domain and SQL Server installed locally.  The default configuration for this software out of the box violates several of our security policies.  Out of the box, this software creates a full user account added to the local Administrators group, sets the password never to expire, adds the account to about every account right it can, uses that account with admin rights to run the web application, creates a SQL Server user with the sysadmin role and dbo control over its databases, sets that password never to expire, and puts the unencrypted connection string in a few config files.  I have this now running under an IIS virtual account with only the permissions and rights it needs.  My next step is to get this site to use Windows Authentication instead of username/password.  I've created a login for the IIS virtual account and mapped it to the relevant databases.  I've set up the OBDC system DSNs to use Windows authentication and hunted through web.config and app.config files.  Using WIndows authentication is part of the security policy.  I noticed today that this application is not using connection strings in web.config files in the way I've seen it documented.  From what I have seen, it seems to store the database connection strings in a registry key but also relies on the ODBC configuration.  I have ventured a try at changing the connection strings there to have "Integrated Security=SSPI" or "Integrated Security=yes", but it still passes the machine credentials.  I believe the account that the software creates in the Administrators group can login via Windows Authentication.  I haven't gotten very much help from their support on this at this point.  I'd be grateful for someone with more experience that has some insight.

  • Re: AppPoolIdentity local SQL Server Login

    Apr 17, 2018 09:26 AM|Yuk Ding|LINK

    Hi Falk0n,

    So Could you access the sql server windows authentication with application pool identity directly without any error? There is a configuration called authenticated userOverride in configuration editor ->system.webServer/serverRuntime. You could try to modify the serverruntime from useAuthenticatedUser to UseWorkerProcessUser.

    https://weblogs.asp.net/owscott/iis-using-windows-authentication-with-minimal-permissions-granted-to-disk

    Best Regards,

    Yuk Ding

    Yuk Ding

    MSDN Community Support
    Please remember to "Mark as Answer" the responses that resolved your issue.
  • Re: AppPoolIdentity local SQL Server Login

    Apr 17, 2018 01:20 PM|Falk0n|LINK

    I have not been able to get IIS to pass the application pool identity at all.  Both the SQL Server logs and Windows application logs indicate that only the machine credentials are being passed.  I tried setting the ServerRuntime value for the application.  I did not see any change.  The logs are still full of failed logins using the machine credentials.  The application does load though, but I'm sure there are many broken areas.

    To better understand how this works, I want to explain exactly what I'm trying to do.  The authentication of the user to the application is handled with forms authentication.  I simply want the backend authentication to the database to use the IIS APPPOOL\AppPoolIdentity credentials.  If I disable Anonymous Authentication completely, the application just gives a 401 error and never lets you get to the login screen.  So, the application handles user credentials on the front end and stores them in its database while the IIS worker thread user should be handling the actual database connections.  Both would run under the same account.

    Strangely enough, there are also a whole bunch of log entries in SQL Server that say domain\machinename$ was able to log in successfully.  That just confuses me more, because I have not created a login for the machine credentials, and I don't WANT a login for machine credentials.  I'm trying to isolate the backend access to just the the application pool identity and the databases it's allowed to access. 

  • Re: AppPoolIdentity local SQL Server Login

    Apr 17, 2018 01:22 PM|lextm|LINK

    Falk0n

    I've followed what was laid out here: https://blogs.msdn.microsoft.com/ericparvin/2015/04/14/how-to-add-the-applicationpoolidentity-to-a-sql-server-login/

    What is your connection string? Remote or local is not defined by whether you installed SQL Server locally, but how you connect to it (aka the connection string).

    Lex Li
    https://lextudio.com
    ---------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
  • Re: AppPoolIdentity local SQL Server Login

    Apr 17, 2018 04:12 PM|Falk0n|LINK

    From the database settings registry key for the application

    Data Source=(local);Initial Catalog=WhatsUp;Integrated Security=True;Persist Security Info=False

    The web.config files don't really use connection strings.  It pulls them from the database settings in the registry.  Provider is System.Data.SqlClient.  I need to learn to use the trace in SQL Server or similar.  I'm getting tons of log entries.  Some of them say login succeeded for Domain\ComputerName$ while others say login failed for Domain\ComputerName$

    Regardless, no matter what identity I set to run the application pool, I never see anything but machine credentials passed to SQL Server.

  • Re: AppPoolIdentity local SQL Server Login

    Apr 17, 2018 04:20 PM|Falk0n|LINK

    Is there something inherently wrong with my idea that this can use Forms authentication in the front end but still use Windows Authentication in the backend?

  • Re: AppPoolIdentity local SQL Server Login

    Apr 18, 2018 10:39 AM|Yuk Ding|LINK

    Hi Falk0n,

    I only find the the form authentication with windowscredential without kerberos ticket. So maybe you still require the windows authentication.

    In addition, have you tired to set system.webServer/security/authentication/windowsAuthentication/userAppPoolCredentials to true at the same time?

    Best Regards,

    Yuk Ding

    Yuk Ding

    MSDN Community Support
    Please remember to "Mark as Answer" the responses that resolved your issue.
  • Re: AppPoolIdentity local SQL Server Login

    Apr 19, 2018 08:00 PM|Falk0n|LINK

    I have tried this setting.  The Windows Event log and SQL Server logs still say that the machine credentials are being passed instead of the IIS virtual account.  This seems to work if I use the account that the software created as the App Pool Identity.  This is a full user account that is in the Administrators group and not very security conscious.  The IIS virtual account was given access to the needed files and registry keys and granted the user right to log on as a batch job and a service.  I'm not quite sure why this works with a full user account but not the virtual account.  It could be a system permission or user right that allows it to pass the user credentials to a loopback bound (local) SQL instance?