First thing you need to clear if you are using windows authentication and you are not mentioning any username password in your connection string then:
What happens when you run your code through localhost: when you run your wcf test client from localhost, it will be able to communicate to database as local debug mode application is calling database by your account’s service. So it has access to database because devenv.exe is running under your user account.
But when you deploy your web service in IIS. Now understand this service runs under IIS not under your account. So you need to assign access rights to IIS service to access the sql server for windows authentication. Here your web service would not be able to communicate to the SQL server because of access rights issue and Login Failed for user_______ (here your user will come)
So if you are using windows authentication to connect your database, you just have to change the IIS Application pool settings. You need to change IIS Application pool’s identity to local System.
Below are the Steps for windows authentication WCF:
1) Open IIS (windows+R (run) then type inetmgr, then click ok)
2) double click your PC name under Connections
3) Click Application Pools
4) Select your app pool (DefaultAppPool)
5) Then under actions on the right click Advanced Settings:
6) Go to Process Model section and
7) click on Identity.
Now select LocalSystem.
Now open your sql server management studio:
open run-> then type ssms ->then press ok
In ssms, login using your windows authentication account.
Open security tab expand logins tab then you will be able to view your account.
Now open properties of your account
go to userMapping then select the database you want to connect
then check the role membership services you want to use for the selected database. click ok.
(For network services i.e. intranet users you need to configure above settings for NT AUTHORITYSYSTEM user too)
add Trusted_Connection=True; property in your connection string. Save it & deploy the web service. Restart app pool.
you will be able to connect the database now.
- Remove From My Forums
-
Question
-
User709978785 posted
Any Ideas on how to fix this issue?
Visual Studio 2005, Windows 7 64bit, SQL Server 2005 Express
Cannot open database «sql2» requested by the login. The login failed.
Login failed for user ‘IIS APPPOOLDefaultAppPool’.
Answers
-
User709978785 posted
Turns out the issue was with IIS 7.5 and the default Identity setting, it is set to ApplicationPoolIdentity and Not NetworkService.
Who knew?
-
Marked as answer by
Anonymous
Thursday, October 7, 2021 12:00 AM
-
Marked as answer by
All replies
-
User709978785 posted
Turns out the issue was with IIS 7.5 and the default Identity setting, it is set to ApplicationPoolIdentity and Not NetworkService.
Who knew?
-
Marked as answer by
Anonymous
Thursday, October 7, 2021 12:00 AM
-
Marked as answer by
-
User805414602 posted
Hi Jackxxx & dotnetruler,
Thanks for your posts…helped me to solve the issue after few hours of head scratching and a good night sleep.
But one thing…Jackxxx, it would have been helpful to know where you changed the settigns for «ApplicationPoolIdentity» for a newbie like me :-). I did some search and found I can change them as shon in the image below (and hope I did it the right way,
coz my ASP.net application started working!)[Edit: noticed the image does not show up properly in R.H.S], So just select Application Pool -> a pool entry -> Advanced Settings… (from Edit Application Pool section) -> Process Model (from Advanced Setting dialog box)
[Thanks http://www.sharemyimg.com/ for allowing to upload image files and to share them !]
-
User103321928 posted
Please tell me where you found this setting to change it. I’ve been fighting with this error for way too long now and I do not know IIS 7.5 or ASP.Net too well yet and I cannot find where to change this.
Please feel free to e-mail me direct at
tim_robinson@live.comI sure will appreciate it as I need to get this solved today before my customer has a fit.
-
User709978785 posted
Open IIS, double click your PC name under Connections, Click Application Pools, Select your app pool (DefaultAppPool), Then under actions on the right click Advanced Settings, Go to Process Model section and click on Identity. Now select
NetworkService.To open IIS, click your start button and enter IIS into the search field.
hope this helps.
-
User103321928 posted
Hey Jackxxx
Sorry for not reading the entire posts…
I changed the DefaultAppPool identity to a couple different things and it kept failing
So just for fun I figured I’d change it to my username/password by specifying the account and finally it began working….
But could that be a security risk/issue? Is there something in the Windows security somewhere I might need to change so that Network Service will work instead of having to use my Administrator account?
Thanks so much for your help.
-
User1848458564 posted
Your user credentials work probably because you are an Administrator on that machine. The problem is that the connection will fail again if you change your password later.
-
User-308628221 posted
@dotnetruler Your solution rocked.
I am surprised why that wasn’t marked as answer as well! It explains in detail what to do.
Also wondering, when changing the Identity of the app pool to network service, I noticed Application Pool Identity as being recommended. Why change to Network Service? Why not just add the default app pool user in Sql server logins and give it access to
the db?Any thoughts!
-
User-1827764150 posted
ckenk, if you set it up to run as Network Service, you’re not leveraging the new security features of IIS. You should leave that setting as-is (i.e. ApplicationPoolIdentity).
What you want to do is go to your SQL server, and add an SQL login for «IIS APPPOOLDefaultAppPool».
The reason MS stopped using NETWORK SERVICE or LOCAL SYSTEM is that there can be security issues between processes sharing those accounts.
Alternately, you can create a restricted domain account, and use it as your Application Pool process identity, and grant it permissions to your database.
-
User1848458564 posted
Unfortunately I never could find a user with the name «APPPOOLDefaultAppPool» on my Win 2008 system so I always have to switch to «Network User». Any ideas how to give permissions to DefaultAppPool?
-
User-1827764150 posted
Because it’s not a real windows account, you can’t user the «Search» button. Just enter the account name ‘IIS APPPOOLDefaultAppPool’, set your security settings, and click OK.
[IMG]http://i871.photobucket.com/albums/ab272/mike300zxt/SQLLogin-1.jpg[/IMG]
-
User1848458564 posted
Great! Thanks for the quick reply. I’ll try that for SQL.
I also figured DefaultAppPool impersonates IIS_IUSRS. So for my web application that needed write access to the application folder, I just needed to give Write access to IIS_IUSRS.
-
User1848458564 posted
Thanks jobran456 for reply, but this thread suggests not to use NetworkService
in IIS 7+ due to security considerations. Instead it is recommended to use
ApplicationPoolIdentity as default Identity setting. This is by default in IIS 7 and later but you need also check for the followings (summarized from this thread):- Set Read permission for
IIS_IUSRS on the the web application’s local directory. IIS_IUSRS is used when Anonymous Authentication is enabled. - Create a new SQL Login (user) as «IIS APPPOOLDefaultAppPool» and give it
db_owner role membership.
- Set Read permission for
-
User1028487728 posted
Excellent solutions from many people thanks for your support like this …. cheers
B. Rathinakumar
-
User-895120540 posted
To: mike300zxt
Thank you, your solution worked for me!
What you want to do is go to your SQL server, and add an SQL login for «IIS APPPOOLDefaultAppPool».
The reason MS stopped using NETWORK SERVICE or LOCAL SYSTEM is that there can be security issues between processes sharing those accounts.
Alternately, you can create a restricted domain account, and use it as your Application Pool process identity, and grant it permissions to your database.
-
User-1034524349 posted
Thanks mike300zxt that worked for me too, I had to then cick Server Roles and tick sysadmin also, I hope that doesnt open up any can of worms.
Cheers,
-
User482576241 posted
I prefer matching the IIS defaults to the SQL Server settings. Open your SQL Server Configuration Manager by opening «Run…» and running «sqlservermanager10.msc.» Check the «Log On As» (by default «LocalSystem») of your SQL Server (it should be «running»).
Now open your IIS Manager by running «,» in the left panel click «Application Pools,» then in the right panel click «Set Application Pool Defaults…» In the «Process Model» section of the dialog which pops up, select the same login method as your SQL Server
that we checked earlier. I’m using the default setting, so on my local machine for development I use «LocalSystem» for both.Alternatively you can configure a new «Login» under the «Security» folder on SQL Server as others have mentioned. The easiest way to do this is using SQL Server Management Studio.
I prefer my approach though because then I don’t have to change my connection strings and authentication method for SQL Server.
Cheers
- Remove From My Forums
-
Question
-
User-830563764 posted
I have a SQL Server Database coded as the SQLDataSource control in my VS2010 ASP.NET website.
Exactly what does the error message mean? What do I need to do in IIS Web server manager to
solve the problem?TIA,
Jeffrey
Answers
-
User-821857111 posted
The Windows account that your web site runs under is «IIS APPPOOLDefaultAppPool». Your database connection string uses Windows authentication (Trusted_Connection=true or Integrated Security=true), so your application tries to connect to the database using
the Windows user account that it runs under. However, you have not configured the account as a login for the database. So you don’t actually need to do anything in IIS, but you do need to add IIS AppPoolDefaultAppPool as a login to SQL server. You can do
that via SQL SErver Management Studio.-
Marked as answer by
Thursday, October 7, 2021 12:00 AM
-
Marked as answer by
With reference to following link:
http://www.asp.net/mvc/overview/deployment/visual-studio-web-deployment/deploying-to-iis[^]
Create a grant script for the new databases
When the application runs in IIS on your development computer, the application accesses the database by using the default application pool’s credentials. However, by default, the application pool identity does not have permission to open the databases. So you have to run a script to grant that permission. In this section you create the script that you’ll run later to make sure that the application can open the databases when it runs in IIS.
Run the following script:
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'IIS APPPOOLDefaultAppPool') BEGIN CREATE LOGIN [IIS APPPOOLDefaultAppPool] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] END GO CREATE USER [WebDatabaseUser] FOR LOGIN [IIS APPPOOLDefaultAppPool] GO EXEC sp_addrolemember 'db_owner', 'WebDatabaseUser' GO
I am new to both ASP.NET and SQL Server.
I have IIS8 installed on my Windows 8 PC, together with SQL Server Express 2012. Given my ignorance, I have just set everything up using default values.
I have a very small web site set up in its own folder and set up in IIS 8 as a virtual folder under the default web site. I have also set up a very simple database called ‘StockControl’ via SSMS, which contains one table called ‘Colours’ which has two columns.
One page in the website is a simple ASP.NET form which runs on localhost without any problems.
On the second page in the website I added a GridView ASP.NET control, letting Expression Web 4 generate the connection string and place it in a web.config file. I tested the SQL query and the connection as I went along and both came back as successful. So
far, so good.However, when I try to run the page in the browser I get the error message:
Server Error in ‘/’ Application.
Cannot open user default database. Login failed.
Login failed for user ‘IIS APPPOOLDefaultAppPool’.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user ‘IIS APPPOOLDefaultAppPool’.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot open user default database. Login failed. Login failed for user 'IIS APPPOOLDefaultAppPool'.]
I have spent two days trying to find out what I have done wrong and even uninstalled and re-installed SQL Server just in case i’d totally messed up and corrupted the database.
I would really appreciate some help please.