Well, this is embarrassing...

I need to upgrade several DNN sites running different DNN versions (4+), but the server is running MS SQL Server 2000 Personal Edition (default instance), and cannot be upgraded to MS SQL Server 2008.

So, I have MS SQL Server 2008 installed as a named instance, but cannot seem to get DNN to connect to it. I can connect using Server Studio, but not with DNN.

I have tried the connection string using both "Data Source" and "Server" = "computername\SQLserverInstanceName", but no luck. It won't connect. I keep getting errors telling me that the user cannot connect.

I have double and triple checked that the user name and password are correct, and that it has database owner permissions.

Could somebody please help out with some suggestions or point me to some resource that will get me started?

I've spent a day and a half on it, and will be experimenting with new DNN installations, but I need to get these other sites upgraded to SQL Server 2008 so that I can upgrade the DNN installations themselves.

Thank-you in advance for any help!

Hello Dave,

Thanks for chiming in.

Yes. I configured Named Pipes to be open and checked the firewall settings. I copied the MDF and LDF SQL Server 2000 database files to the default SQl Server 2008 data folder then attached them there. I then created the same user name with the same password in MS SQL Server 2008.

Attaching went smooth, and I couldn't see any problems. (Logged in as sa.)

Incidentally, I just tried logging in to Microsoft SQL Server Management Studio with the DNN site user name and password (as above), and the login failed. (DOH!)

I have a sneaking suspicion that this is my real problem, and that I probably should have been paying more attention to SQL Server 2005 and 2008 over the past several years... (2000 was working fine -- it's hard to break off that love affair with Enterprise Manager and Query Analyzer... :) )

I'm obviously screwing this up out of ignorance of SQL Server 2008.

Any pointers to straighten me out? (I'll continue to look into this and will post back when/if I figure out which brand of ignorance/idiocy I am suffering from.)

Thanks again!

Thanks for chiming in as well, Bill!

As it turns out, it was the orphaned user problem. I'd tried near everything I could think of or solutions to problems that were in any way similar. I found something similar to your suggestion here. The solution posted was:

Try this connecting with the Windows user and selecting your database

sp_change_users_login 'report'

If your application user is listed there, then run

sp_change_users_login 'auto_fix', 'your_user'

Anyways, I've got 1 of the sites up and running now.

Thanks again for the help! Muchly appreciated~! =D

(For anyone with a twisted sense of humor, the site is here -- It is NSFW, but fun.)