Hooo whee did I have fun yesterday and once again learned a lot about something that was low on my list of things to learn! This is about solving the problem of the default IIS6 security account not having access to SQL Server and some lessons in managing that security account.

I have a new Win2003 Server with IIS6 set up. I’m not sure in Windows creates this account or IIS6 does, but IIS6 defaults to using the account “NT Authority/Network Service“ as it’s main security account. You can actually change this if you want. In the Application Pool you see in IIS, you have an option to change the identity of the main security account for IIS. There are 3 defaults or you can select your own account.

I have a domain controller. In Win2000 Server, I had to use the IWAM_Server as my main security account. That means I had to give this account NTFS permissions on my web folders and also in SQL Server. I don’t know if there was a way to change the account in IIS5, but if there was, it was elusive. In fact, I had to deal with this when I made some changes to my Win2000 Server and suddenly couldn’t debug ASP.NET, etc. It’s because I now had to give taht IWAM account access to everything. In fact, here is the post where I learned that lesson – the real lesson was thanks to a comment by Mark Pearce.

On to Windows2003 and IIS6. I was able to debug, but my asp.net apps now didn’t have access to SQL Server.

Googling, I saw that I could via TSQL, just add the Network Service account to SQL Server. Through Enterprise Manager, that account is not exposed, but you can do it through TSQL (and then see it in Ent. Mgr). But I didn’t do it correctly the first time. I added it to a user database (Users), and not to the “master” database, which is the same as adding it to Security/Logins. So thinking that it just hadn’t worked, I looked a little harder which is where I got my big lesson. What I did not want to do (even though this is just my development environment and used only by me) was just switch over to mixed authentication. I was bound and determined to use integrated security!

Remember, I am a developer, not a “real“ dba or sysadmin!

What I learned, thanks to some help from a Dave Burke post and also a conversation with Brad Kingsley from Orcsweb on the ASPAdvice security listserv, was how to change the security account in IIS (Application Pool/Identity). Not only can you choose from one of the defaults, but you can also choose any of the server accounts (well, they may be filtered) but my IWAM_server account was what I wanted and knew how to work with so that’s what I chose. This cleared up all of my sql access problems, because I had set up that account when I installed SQL Server on that box. But, it was the NETWORK SERVICE account that had been given all of the appropriate permissions on my application folders. I started getting errors left and right when trying to run/debug my asp.net app. Not even on the folders where the application was, but the windows/.net framework/v…/aspnet temp  folder and other folders on my system drive (my app folders are on a separate drive.)

I started hacking – permission here permmission there, but the problem didn’t go away AND I did NOT like that I was doing this manually because I might have been creating another bad effect elsewhere. I did think later that maybe some tool like IISReg might have done this for me. I’m not saying this is a bad option. The nice thing here is that if you need to, you can have this type of granular control over IIS. Me – I don’t happen to need them, gimme those defaults and thank you for all of the automated setup!!!

But instead, I set everything back to the defaults, including the IIS security account, and went back to SQL Server. This time, in TSQL, I went to the master database and typed

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!