ASP.NET, IIS and SQL Server … and Application Pool Error !!!

 

Hi All, today again I am with another error … and possible workout … I think job of fresh developer is more or less like life of newly wed couple smile_sarcastic … I mean you are always excited to know more about your partner (same way the developer always excited about his new job and environment)… and on the other hand you may face new challenge every day (may be because its your first marriage ?? smile_zipit) … just the same as a new developer, you don’t have much worth real experience before and you are trying to walk on your feet … and you always find your self doing more “Googling” then work but I think its really great experience to learn byself, I think perhaps it is the reason why I am able to do something better because I had learned hard way (and still learning).

Also, I really have no idea what the title should be for today’s post. Because it is basically @ an error that you may have or will encounter … the error will be displayed something like … “Login failed for user ‘IIS APPPOOL\ASP.NET ” and yes your guess is correct, it is because of failed authentication for user. It means that if you application is using database for anything, and your application can’t access it due to invalid credentials. Now, you have double checked server name, database name, other parameters in connection string but nothing seem to be wrong … with just a little note … you were using WINDOWS AUTHENTICATION for SQL Server database … ahaaa … if you have used SQL Server, you might be knowing that SQL Server database can be accessed using two type of authentication, namely

Windows Authentication : In which user’s windows credentials are used to access dbms, and its more secure, easy to maintain .. and convenient if you are using domain controller and have homogeneous network

Mixed Mode Authentication : In this mode user can use either windows credentials or SQL Authentication where user can enter user id and password … this method of authentication is specially good if you have heterogeneous network which includes other OSes that doesn’t work with domain controller (e.g. Linux, OS X, Solaris)

Now, you might be thinking I am using same m/c where I have logged in, and so all my application will have SAME windows user … well … that is correct in general but not for IIS. As you know that IIS serves every request using threads that are being managed by Application Pool, so when some application wants to access database application pool’s worker thread tried to access it… now this application pool uses, IIS_IUSRS group and IUSRS user and obviously you have not created database login for that user / group, and so you get this error …

That is what it is theoretically … and all you need to do is to add that user for dbms login and users … and you are all set … but wait there is more (yes, I have been watching late night tv ads smile_teeth) … I created database login and use for that windows user but I was not able to make it work … In theory it should work but I don’t know why it didn’t work …smile_thinking … but I will try to find some solution some other time it’s already midnight (just like first few years of marriage … time just flies awayyyyyyyyyyyyyy  )…

So, finally I tried to take a little detour … I simply applied SQL Login and Password of my “SA” … to my application connection string .. and voila … it worked … I know it should have worked with my first approach but its better than have nothing smile_wink …  I am posting out here both screenshot (with error … and after error cleared for my test application)

error_1 solved

So, moral of story is … If you are trying to use Windows Authentication as method to access database, and getting this error then give it a try by manually entering login and password for “SA” account of SQL Server.

It’s just a thought …

Leave a Reply

Your email address will not be published. Required fields are marked *