May 28, 2012 at 8:11 am
I have just upgraded 2 databases from SQL2000 to SQL2005.
Both are used for running 2 websites (both DotNetNuke sites from different servers).
The first one was upgraded sucessfully. However, I have a problem with the second one. The website doesn't load when trying to connect to my SQL2005 database.
Error message just says: Site Unavailable (default CMS message).
and if i run this /install.aspx page it gives me this error message:
ERROR: Could not connect to database specified in connectionString for SqlDataProvider
I followed the very same steps both databases:
1. Backed up sql2000 db.
2. Placed a copy of the above .BAK file on the SQL2005 server (in the same path/location as above.
3. Created a new database on the SQL2005 server (with the same name)
4. Restored the database (on SQL2005) using the copied .BAK file from the SQL2000 back up (from step 2 location)
5. Re-created the database user (which is the same for both, lets call the user NIGEL) and given dbo rights to the database user
6. Checked for any orphaned users, by using this script below:
EXEC sp_change_users_login 'Report'
7. Used this script to fix orphaned users:
EXEC sp_change_users_login 'update_one', 'NIGEL', 'NIGEL'
NOTE: in both databases - I did get NIGEL listed as an orphaned user, and the above script fixed it.
8. Re-run the script in step 6 again to check that orphaned users have gone.
9. Changed the connection string to point to the IP address of the new SQL2005 server (in web.config file).
SQL2005 INFO:
* Both databases have the db user 'NIGEL' listed under the database >> security >> users.
* Under the properties of this user on General Tab the username and default Schema is the same and db_owner is checked.
* Under the Securables Tab >> Execute permissions have been granted.
SECURITY >> LOGINS >> PROPERTIES >>
>> SERVER ROLES: Public is checked
>>User Mapping : User and Default Schema is the same for both
>> Status: Grant and enabled - are selected.
FURTHER INFO:
* I can ping the SQL2005 server ok from both web servers.
* If I replace the IP address in web.config back to the old SQL200 address the site runs ok.
I don't know what to check next.
Any ideas?
May 28, 2012 at 9:09 am
Did you check that your DNN connection strings have been changed to the new server correctly? IIRC, there is more than one place in the .CONFIG file for DNN that has to be changed for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 9:27 am
yeah, I have changed the web.config in 2 places.
here is a sample of my web.config:
<add name="SiteMyNamedServer connectionString="Data Source=123.45.67.89;Initial Catalog=NIGEL;user ID=NIGEL;Password=MyPasswd" />
</connectionStrings>
<appSettings>
<add key="SiteMyNamedServer value="Data Source=123.45.67.89;Initial Catalog=NIGEL;user ID=NIGEL;Password=MyPasswd" />
May 28, 2012 at 10:00 am
mrichardson 57577 (5/28/2012)
yeah, I have changed the web.config in 2 places.here is a sample of my web.config:
<add name="SiteMyNamedServer connectionString="Data Source=123.45.67.89;Initial Catalog=NIGEL;user ID=NIGEL;Password=MyPasswd" />
</connectionStrings>
<appSettings>
<add key="SiteMyNamedServer value="Data Source=123.45.67.89;Initial Catalog=NIGEL;user ID=NIGEL;Password=MyPasswd" />
Is the database name really the same as the login name?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 10:03 am
yes, same name, (just not NIGEL obviously for security reasons)
but it worked ok before as the same name on sql2000.
I realise now, it might not be good practice (as may add to confusion).
but does this cause any other problem with in sql2005?
May 28, 2012 at 10:54 am
mrichardson 57577 (5/28/2012)
yes, same name, (just not NIGEL obviously for security reasons)but it worked ok before as the same name on sql2000.
I realise now, it might not be good practice (as may add to confusion).
but does this cause any other problem with in sql2005?
Nope, just checking.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 10:58 am
How does the working new server/database compare to the non-working new server/database?:
1) Same box, different instances, same DB name
2) Different boxes, different DB names
3) Same box, same instance, different DB names
4) ... etc.
Oh, and are these new boxes, and new server instances?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2012 at 12:24 am
Hi
Two popular points can cause problems when connecting to SQL Server which have nothing to do with permissions. You can generally rule out a permission problem if there is no record in the SQL Server errorlog - it records login failures there. You can check it by running EXEC sp_readerrorlog as sysadmin (assuming you can connect on the server!)
1) Are connections allowed through TCP/IP? By default this is disabled in 2005
Some defaults were changed on SQL Server 2005. One of them was the ability to connect remotely.
"Program Files" -> "SQL Server 2005" -> "Configuration" -> "SQL Server Configuration Manager"
Expand the tree under "SQL Server 2005 Network Configuration" and click on "Protocols for ..." your instance name. If you have the default instance it is called "MSSQLSERVER".
Is TCP/IP enabled? If not, enable it.
2) Ports and firewalls
The port used for the default instance is 1433. You do not need to use the SQL Server Browser service (new in SQL Server 2005) for connections to this.
If you have a firewall active on the database server you must allow TCP traffic through port 1433.
If you have multiple database server instances on that same server then you will need to have the SQL Server Browser Service running and its port (UDP 1434) allowed through the firewall. The default is for additional instances to use dynamic ports. You can set them to fixed ports and that will allow them through your firewall.
As a diagnostic step you might want to briefly disable your firewall if one is enabled, just to see if the problem is there.
Good luck!
May 29, 2012 at 3:46 am
Thanks for all the replies.
I asked the network team to check the firewall.
it turns out it was indeed a firewall issue - it was outside our DMZ and wasn't allowed to talk to the new sql server.
the first database upgrade was inside our DMZ so didn't have this problem.
cheers,
mark.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply