265

I'm running an IIS 7 Website with an AppPool of Integrated Pipeline Mode. The AppPools does NOT run under NetworkService, etc.. identity (by purpose), but uses its own AppPool Identitiy (IIS AppPool\MyAppPool).

This is a so called service account or virtual account. (a user account, which is not a full account...)

I'd like to give this service account (IIS AppPool\MyAppPool) permissions to connect to my SQL Server 2008 Express (running in Mixed Auth. Mode).

While SQL Server can add any normal user account, the IIS AppPool\MyAppPool virtual account cannot be added to the valid logons (SQL Server says, that the account cannot be found).

Is there any trick, anything I need to enable to make the virtual accounts work? (the w3wp.exe process runs under this identity according to taskmgr, but I cannot use the account in NTFS security either...)

Thanks for your help!

EJoshuaS - Stand with Ukraine
  • 11,977
  • 56
  • 49
  • 78
Max
  • 3,260
  • 2
  • 19
  • 24

10 Answers10

450

The "IIS APPPOOL\AppPoolName" will work, but as mentioned previously, it does not appear to be a valid AD name so when you search for it in the "Select User or Group" dialog box, it won't show up (actually, it will find it, but it will think its an actual system account, and it will try to treat it as such...which won't work, and will give you the error message about it not being found).

How I've gotten it to work is:

  1. In SQL Server Management Studio, look for the Security folder (the security folder at the same level as the Databases, Server Objects, etc. folders...not the security folder within each individual database)
  2. Right click logins and select "New Login"
  3. In the Login name field, type IIS APPPOOL\YourAppPoolName - do not click search
  4. Fill whatever other values you like (i.e., authentication type, default database, etc.)
  5. Click OK

As long as the AppPool name actually exists, the login should now be created.

dp.
  • 8,138
  • 7
  • 33
  • 28
  • 6
    Sorry to resurrect an old post, but does anyone know if doing this has any security implications in a live environment? – dooburt Mar 10 '11 at 00:10
  • 44
    amazingly helpful, yet mind-blowingly (and crappily) Microsoftian. – Evan Nagle Apr 21 '11 at 06:18
  • 19
    Wow. "do not click search" in step 3 seems to make a difference (?) – Jedidja May 24 '11 at 19:35
  • 20
    Won't this fail if the SQL server instance is on another server? For instance - how does the `SQL1` know about the local/virtual `IIS AppPool\MyAppPool` on `WEB1`? – one.beat.consumer Mar 20 '12 at 23:11
  • This doesn't work for me, even though my database server is on the same machine as my web server. I get "Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication". This is with Sql Server 2008R2 and Windows Server 2008R2 – Nathan Mar 27 '13 at 22:46
  • This appears to not work when using (local) in the data source of the connection string. – Nathan Apr 19 '13 at 16:02
  • 1
    @Nathan: Works for me with `(local)` (2008 R2). – Allon Guralnek Jun 23 '13 at 15:50
  • These service accounts are based on the name of your app pool. What happens if you change the name of the app pool? Does the service account get renamed and permissions preserved, or is the account replaced and all permissions left referencing a non-existent account? – Triynko Jul 29 '13 at 05:45
  • 1
    I have been searching for a solution like this for 2 days. Haven't tried it yet, but probably will. My fear is that a Windows security update will kill this function and some morning when I get into work I'll have a million emails complaining about the site being down. Up vote for the solution!!! Down vote on me for risking it!!! – htm11h Nov 19 '13 at 17:22
  • 5
    Is there any solution if your Web Server and SQL Server are on different machines!!?? I can find the login, but I cannot create the new SQL Login. (If I don't do a check names... it still does not work) – Danimal111 Jan 23 '14 at 21:20
  • 3
    @DanB Yes, there is a solution for that: Use a domain account instead. The IIS AppPool accounts are local accounts. You'll need to create a domain account and set your IIS AppPool to log in with the domain account. – reirab Feb 25 '15 at 15:22
  • 3
    Thank you -- I figured it out through trial and error... the real chink in the armor was a little known setting in IIS in the Configuration Editor for the website in Section: system.webServer/security/authentication/windowsAuthentication From: ApplicationHost.config called useAppPoolCredentials (which is set to False by default. Set this to True and live becomes great again!!! HARD TO FIND... – Danimal111 Feb 25 '15 at 19:19
  • @Max and Nathan, -- the SQL server and website do not need to be on the same machine... see note from reirab on 2/25/2015... it does work... I edited the final answer with the new info... hope this helps all!!! (My edits are not yet posted, but I added a new answer with the rest of the pieces to make this work!! Let me know if you have questions as it's awfully confusing at first... good luck! – Danimal111 Feb 25 '15 at 19:35
  • @dp. I don't understand this. Why do I need to create a login when I have already provided login and password in my connection string in web.config. Why it is not using web.config connection string? – Usman Khalid Jul 14 '15 at 14:47
  • @UsmanKhalid This is only for connecting to SQL Server with Windows Authentication. If you have specified a User Name and Password in your connection string then you are using a SQL Server account; i.e. a completely self-contained account, including password, created and maintained within SQL Server versus an Active Directory account that is simply authorized in SQL Server. – Chance Jul 14 '15 at 20:31
  • Can't do this via the GUI anymore (at least not in SSMS 18.1). See Ryan's answer below. This can only be done via script now. – MC9000 Aug 28 '19 at 20:31
  • It worked for me through the SSMS GUI. Note that you may also need to map your app DB user to the login you created. In SSMS, right-click the login and select Properties. On the User Mapping page, select the DB for your app, select the appropriate role(s) below it, and hit OK. – Tawab Wakil Nov 29 '21 at 20:31
81
CREATE LOGIN [IIS APPPOOL\MyAppPool] FROM WINDOWS;
CREATE USER MyAppPoolUser FOR LOGIN [IIS APPPOOL\MyAppPool];
Ryan Prechel
  • 6,592
  • 5
  • 23
  • 21
  • 1
    Nice - whilst the accepted answer is correct - I managed to get myself in a situation where Management Studio wasn't working so this raw TSQL solution was what I needed. – Andrew Patterson May 14 '13 at 01:07
  • 5
    Note to the noobs like me: make sure to execute this query against the database you wish to access with the IIS app pool, the second statement is on database level. You may need to make the user a member of certain database roles and grant him permissions on the schema(s) such as `dbo`. – MarioDS Nov 04 '14 at 11:42
  • Once I discovered it's not possible to add IIS APPPOOL logins via the SSMS GUI, I ran your script. THANKS! It USED to be possible - not in SSMS 18! I suppose this is now a "feature" – MC9000 Aug 28 '19 at 20:29
  • 1
    You may want to add the role `db_owner` to the newly created user `MyAppPoolUser` in order to avoid permissions issues: `ALTER ROLE db_owner ADD MEMBER [MyAppPoolUser]` – rodsarria Aug 30 '20 at 18:45
  • @rodsarria: that is not necessarily a good idea. The IIS App Pool user would then have permissions to create and alter tables. Instead, go for explicit GRANTs on tables, views, functions and stored procedures. You don't want to give an application level user that much access to your DB schema. – Greg Burghardt Oct 22 '21 at 15:51
12

You can solve like this,

  1. Open "Applications Pools",
  2. You should right click that you have choosed application pool. Then choose "Advanced Settings".
  3. Click three point on the Identity tab then you should choose "LocalSystem" from field of "Built-in-account"

If you do this way, you don't need to create a user in database.

Gokhan Ertogan
  • 137
  • 1
  • 4
  • OMG this is working perfectly! Shortest solution for this issue. – Faiz Infy Sep 26 '20 at 17:20
  • This needs to be upvoted more particularly when this issue arises in a dev environment – Daniel Sep 30 '20 at 14:45
  • 3
    While this would work in a development environment, I would never do this in production as the IIS AppPool users have tight restrictions on what they can access by design. Changing to an account that has "AUTHORITY\SYSTEM" and "BUILTIN\Administrators" is giving an external-facing website far too much system access. https://learn.microsoft.com/en-us/troubleshoot/iis/understanding-identities https://learn.microsoft.com/en-us/windows/win32/services/localsystem-account – Tom H Nov 24 '21 at 01:23
6

If you're going across machines, you either need to be using NETWORK SERVICE, LOCAL SYSTEM, a domain account, or a SQL 2008 R2 (if you have it) Managed Service Account (which is my preference if you had such an infrastructure). You can not use an account which is not visible to the Active Directory domain.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
6

As a side note processes that uses virtual accounts (NT Service\MyService and IIS AppPool\MyAppPool) are still running under the "NETWORK SERVICE" account as this post suggests http://www.adopenstatic.com/cs/blogs/ken/archive/2008/01/29/15759.aspx. The only difference is that these processes are members of the "NT Service\MyService" or "IIS AppPool\MyAppPool" groups (as these are actually groups and not users). This is also the reason why the processes authenticate at the network as the machine the same way NETWORK SERVICE account does.

The way to secure access is not to depend upon this accounts not having NETWORK SERVICE privileges but to grant more permissions specifically to "NT Service\MyService" or "IIS AppPool\MyAppPool" and to remove permissions for "Users" if necessary.

If anyone has more accurate or contradictional information please post.

Steven
  • 681
  • 10
  • 6
5

Look at: http://www.iis.net/learn/manage/configuring-security/application-pool-identities

USE master
GO
sp_grantlogin 'IIS APPPOOL\<AppPoolName>'

USE <yourdb>
GO
sp_grantdbaccess 'IIS APPPOOL\<AppPoolName>', '<AppPoolName>'
sp_addrolemember 'aspnet_Membership_FullAccess', '<AppPoolName>'
sp_addrolemember 'aspnet_Roles_FullAccess', '<AppPoolName>'
Ricardo Yanez
  • 906
  • 13
  • 14
2

This may be what you are looking for...

http://technet.microsoft.com/en-us/library/cc730708%28WS.10%29.aspx

I would also advise longer term to consider a limited rights domain user, what you are trying works fine in a silo machine scenario but you are going to have to make changes if you move to another machine for the DB server.

keithwarren7
  • 14,094
  • 8
  • 53
  • 74
1

I figured it out through trial and error... the real chink in the armor was a little known setting in IIS in the Configuration Editor for the website in

Section: system.webServer/security/authentication/windowsAuthentication

From: ApplicationHost.config <locationpath='ServerName/SiteName' />

called useAppPoolCredentials (which is set to False by default. Set this to True and life becomes great again!!! Hope this saves pain for the next guy....

enter image description here

Danimal111
  • 1,976
  • 25
  • 31
  • 1
    I followed the same process, but the problem didn't get solved. You must have done something else too, can you please share? – RSB Apr 23 '15 at 11:56
  • @RSB - did you follow all the other steps? did you set up the appPools, and give them permissions to the files where your website is located? Did you add the server name as a login with permissions to the SQL Server? Did you app the appPool to your SQL Server with permissions? Did you modify IIS to use WindowsAuthentication? What errors are you getting? Does it work in your dev environment and not production? – Danimal111 Apr 23 '15 at 17:12
  • I set up appPool, but how to give them permission to files where website is located? How to add the server name as a login with permissions to the SQL server? I also added appPool to SQL Server with permissions. How to modify IIS to use WindowsAuthentocation.It works in my dev environment when IIS and sql server are on the same machine, but it gives error-System.ComponentModel.Win32Exception: Access Denied error when IIS and Sql server are on different machine. Please suggest. – RSB Apr 24 '15 at 10:50
  • 1
    if you give me your email, I'll send you a document I made on how to do it all.... there's too mucj to add it here... – Danimal111 Apr 24 '15 at 16:25
  • @RSB I sent you an email. Good Luck. – Danimal111 Apr 28 '15 at 20:38
  • Thank you so much, would be a great help. – RSB Apr 29 '15 at 06:02
0

In my case the problem was that I started to create an MVC Alloy sample project from scratch in using Visual Studio/Episerver extension and it worked fine when executed using local Visual studio iis express. However by default it points the sql database to LocalDB and when I deployed the site to local IIS it started giving errors some of the initial errors I resolved by: 1.adding the local site url binding to C:/Windows/System32/drivers/etc/hosts 2. Then by editing the application.config found the file location by right clicking on IIS express in botton right corner of the screen when running site using Visual studio and added binding there for local iis url. 3. Finally I was stuck with "unable to access database errors" for which I created a blank new DB in Sql express and changed connection string in web config to point to my new DB and then in package manager console (using Visual Studio) executed Episerver DB commands like - 1. initialize-epidatabase 2. update-epidatabase 3. Convert-EPiDatabaseToUtc

arokanika
  • 41
  • 6
0

For the ApplicationPoolIdentity, add a login/user in MSSQL as IIS_IUSRS which is corresponding to the default pool.

hastrb
  • 410
  • 4
  • 12