Last post Feb 11, 2009 04:11 PM by AkAlan
Feb 11, 2009 11:48 AM|AkAlan|LINK
I have been developing a web app against a sql server that resides on the same machine as the IIS server and up to now have had no problems. I have recently set up a connection to a sql server (QA) which is in the same domain but on a different machine and
I get a no access error when I try to navigate to a page that pulls data from QA. I only have a problem when I run from the published web site, not when I run it from my local host. I think there might be an issue with the IIS configuration or maybe a missing
asp account but I'm not sure and the guy who was supposed to take care of that part of the development has bailed and I am left trying to get this to work. Here is the configuration info I think might be relevant:
asp web.config - I am using WIndows Authentication, ActiveDirectory for role management, have set impersonate to true and am currenly logging on as the administrator.
On the IIS I do not have Enable Anonymous Access checked, Integrated Windows Authentication is checked. ASP.Net version is 2.0.5027 I don't see a Network Service account as having any permissions to the application (I'm thinking there is an issue with that).
I believe I read that if I had impersonate to true in the web.config, when the web app tried to access data from a sql server it would use the authenticated users credentials so I don't see where the problem could be.
I am continuing to research this issue but could use some guidance. If I get the solution first I will post immediately. Thanks for any help.
Feb 11, 2009 12:20 PM|Matt-dot-net|LINK
if your connection string is using "Integrated Security = SSPI" then the database connection is using the credentials of your application which is the Application Pool's Identity. Default App Pool identiy is NETWORK SERVICE (username: machinename$).
Feb 11, 2009 12:32 PM|AkAlan|LINK
I gave the NT AUTHORITY\NETWORK SERVICE account access to the database, even made it db_owner. Still didn't work. Do I need to restart the web service or wait for my change to propogate?
"Integrated Security=True" and "Integrated Security=SSPI" ?
I have it set to true
Feb 11, 2009 12:37 PM|Matt-dot-net|LINK
I don't think there is any difference.
You gave your SQL machine's NETWORK SERVICE account access to your database. You have to give your IIS Web Server machine's NETWORK SERVICE account access. If the web server's hostname is WEB1 the user name you need to give access to is DOMAIN\WEB1$
This is not recommended because you may at some point use a web farm in which case you have to grant permission to each machine in your farm. Go with option 2 that I gave you above, but the "$" trick will work for you now.
Also, this is assuming that your web server and database server machines are a part of the same domain.
Feb 11, 2009 01:24 PM|AkAlan|LINK
Thanks Matt, I am pretty new to all this so bear with me. I just set Custom Errors to Off and now see a specific error that might help solve this. I now get this error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
This makes no sence to me but I will continue to google and search for a solution. Thanks for all your help so far.
Feb 11, 2009 01:38 PM|Matt-dot-net|LINK
Turn off the impersonation.
Feb 11, 2009 02:01 PM|AkAlan|LINK
OK I did. Now I'm getting this:
Login Failed for ARS\ ARCTEC-DEV-SVR$
ARS is my domain and ARCTEC-DEV-SVR is the unc name of the machine the IIS is running on. From a previous post you said "You have to give your IIS Web Server machine's NETWORK SERVICE account access". I didn't understand what you meant but now I think this
error is a result of that. I tried to give the IIS Network Service account access but when I go to select the object (through SQL Server Manager) I don't see ARS\ARCTEC-DEV-SVR in the list.
I clearly need to study this matter on my own, not just make it work. I have found an msdn article and I am going through it now and hope to solve this soon. Thanks again for all your help.
Feb 11, 2009 02:12 PM|Matt-dot-net|LINK
You won't see it. This is a dirty trick. Type ARS\ARCTEC-DEV-SVR$ for your username.
Feb 11, 2009 04:11 PM|AkAlan|LINK
Matt, You are my new hero!! Thanks so much. That did the trick.