October 22, 2008
This is another "helping myself remember later" post. Ignore if you're not an IIS/MSSQL admin.
We had a problem with a brand-spankin' new SQL server at work recently. We could ping the new DB server (let's just call it \\SQL for the sake of argument) from the web server (\\WEB) just fine. We could open up Query Analyzer and talk to it with no problems. Our helper computers could read and write with no issues. But IIS on \\WEB returned a DBNETLIB error 8004005 "SQL Server does not exist or permission denied".
Needless to say, that pissed me off a bit.
The thing that got me was, \\WEB could talk to \\SQL through every means but IIS, and IIS could talk just fine to \\OLDSQL -- flip the connection string between them and watch as the site comes and goes. But you can run a query just fine and talk to the other servers and ack-phhhppbbbbt.
Other web servers couldn't connect over IIS either, and rebooting \\SQL didn't help. Upon inspection in Enterprise Manager, the msdb schema was listed as "suspect". Msdb is how SQL Server takes care of scheduling and other things, and apparently the installation was partially b0rken. Reinstalling SQL Server resulted in a non-suspect database and IIS happily did its thing.
But Wait, There's More
When we ran sp_attach_db on the existing schemas we wound up with schemas that were looking for users that didn't exist. We couldn't recreate the logins because the schemas said, "we already have that guy".
So you have to connect to the affected schema and run sp_dropuser on the ghost/orphaned user(s). Then when you create the user on the fresh install you can assign it to the right schema and continue as normal.
Or you could just not install off a defective DVD drive and bypass that whole ball of hate.
