I posted some info last night about how to move the TempDB database in SQL Server. That morning I had made the changes and just needed to restart SQL Server for the changes to take effect. This morning I restarted the SQL Server service and after it restarted, tried to log into our website (since it's the main interface to the data in SQL). I received a network connection error from ASP.NET which wasn't surprising, so I reset IIS (IISRESET from a DOS prompt). After it restarted, which only took 10 seconds or so, I tried hitting the site again. And nothing. The page sat there attempting to connect and finally timed out.

Uh oh, not good.

I still had Management Studio open so I tried a few test queries, which seemed to work OK. I opened up the Activity Monitor and didn't see any connections from ASP.NET. OK, so SQL Server is probably denying the connection or blocking for some reason. I took in a look in the new tempdb location and SQL had recreated the mdf/ldf files like I expected, so what's up?

I opened up the Event Viewer and saw a number of entries about the various databases starting up, which was good. Hey, what's that "Failure Audit" entry?

"Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: 192.168.1.8]".

Hmmm...192.168.1.8 is the web server. What user does SQL Server run under? I opened up Services, found "SQL Server" and double clicked on it. Then I went to the "Log on" tab. It runs under NT AUTHORITY\NetworkService. Could it be??? I opened Explorer and right-clicked on the folder where tempdb was located and selected "Sharing and Security" then went to the Security tab. Sure enough, "NETWORK SERVICE" wasn't listed. I went ahead and added it to the folder level then test the site again.

Success!

What was really strange about this was that most of our SQL databases are (and have been) located in this same folder. I'm guessing the permissions for those was set on the file level, which is why they've been working OK. The other weird thing was that it was even able to create the tempdb files at all.