?
Solved

Importing a backed up SQL database to a new one with a new name/user/pw

Posted on 2007-10-18
11
Medium Priority
?
407 Views
Last Modified: 2008-01-29
A client of mine recently switched to a new webhosting account so we're in the process of restoring their website for them.  Their website was built in ASP and depends on a small SQL database for some information.  I've restored all data to the new host, and created a new SQL database to restore that to.  The account is with 1and1.com, so anyone familiar with their setup will know that they assign you a database name, user and password and you can't alter this information.

Now, after I imported my SQL backup (something.bak) into the new database, it looks like the tables and data are all there but after updating the settings in my main ASP page, I get an "HTTP 500 - Internal server error" when trying to navigate to, lets say: http://xxyyzz.com/products.asp?catCode=TP

I edited the below info in products.asp; no other files I found contained any reference to a database so I guess this is the only one I need to change.

[[[OLD]]]
ConnectString="PROVIDER=SQLOLEDB;DATA SOURCE=64.xx.xx.92;UID=oldUSR;PWD=oldPW;DATABASE=oldDB"

[[[NEW]]]
ConnectString="PROVIDER=SQLOLEDB;DATA SOURCE=mssql.yyyy.com;UID=user22222;PWD=newPW;DATABASE=db11111"

1) Does the "PROVIDER=" field need to be changed from "SQLOLEDB" to something else?

2) There were some other lines like this one in the same page that had "connectstring=..." but they began with an apostrophee, I gather that means that those lines were commented out and were probably for older setups?

3) When browsing through 1&1's SQL Light viewer, or something like that, I see that the userid for all the tables and such is still "oldUSR" (the username from the old DB setup) instead of "user22222" from the new DB. Could this be causing the issue?


Thanks very much in advance!
0
Comment
Question by:Malevolo
11 Comments
 
LVL 4

Expert Comment

by:DeeEmm
ID: 20100882
It could be the datasource variable - the old file seems to have an IP address as the datasource wheras the new file you have put mssql.yyyy.com I would assume that this needs to point at the sql server for your new host - maybe you need to ask 1and1 for this info.

Generally localhost will suffice - maybe try this first.

DM
0
 
LVL 3

Expert Comment

by:ibrusett
ID: 20100915
1) Does the "PROVIDER=" field need to be changed from "SQLOLEDB" to something else?

If the restore had been on a SQL SERVER database, no. this means that you are using OLE DB provider for SQL Server to connect.

2) There were some other lines like this one in the same page that had "connectstring=..." but they began with an apostrophee, I gather that means that those lines were commented out and were probably for older setups?

Not sure, but ' should mean comment (at least in VB6)

3) When browsing through 1&1's SQL Light viewer, or something like that, I see that the userid for all the tables and such is still "oldUSR" (the username from the old DB setup) instead of "user22222" from the new DB. Could this be causing the issue?

What do you mean for userid for all the table? the owner? this is the old user because of the restore. You should correct this, but I think your issue comes from another problem.
Be sure to grant to the new user (user22222) the correct permissions (select, insert, update, delete) on the database objects (tables, views and so on).
You can do it all at once giving permissions on the SCHEMA to which the tables belongs.
0
 
LVL 8

Assisted Solution

by:matrix_aash
matrix_aash earned 300 total points
ID: 20100926
1) Not too sure what database 1&1 uses. Try find out then hope the information below helps you out.
For IBM AS/400 OLE DB Provider: IBMDA400.DataSource.1
For JET OLE DB Provider: Microsoft.Jet.OLEDB.4.0
For Oracle OLE DB Provider: OraOLEDB.Oracle
For SQL Server OLE DB Provider: sqloledb
For Sybase ASE OLE DB Provider: Sybase ASE OLE DB Provider


2) In ASP, anything after an apostrophee (') will be commented
E.g.
' This is a commented line
Dim TestVariable ' This is a comment

So most likely  thise lines were older setups


3) This could be the problem. Try all combinations of username and password in your connection string.
New User, New Pass
New User, Old pass
Old User, New Pass
Old User, Old Pass
And see if any of those works.


Let me know if you have any problems with this!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Malevolo
ID: 20100963
DeeEmm:

1&1's SQL servers are not on the same machines that run the webservers; this is the server source required. I could resolve the name to an IP if you think this might help.

ibrusett:

Could it be that 1&1 is using an alternative method to connect to the SQL Server; something other than OLE DB? If there are multiple ways maybe I'll call and ask...

You're correct, I meant the owner is listed as "oldUSR".  The username I'm using now should have full access over the database; are these rights not attributed automatically to data imported from a backup file? If they aren't how can I do this? Is there a query or something I can send to change owner from "oldUSR" to "user22222"?
0
 

Author Comment

by:Malevolo
ID: 20100986
this is what I got from 1&1's site right now, I'm on the phone trying to confirm which databse they use exactly.  this is the application they provide to administrate SQL:  myLittleAdmin (for SQL Server and MSDE) v.2.7 r.123

Server MSSQL
Server name : MSSQL
Server version : Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
 
Language : us_english
0
 

Author Comment

by:Malevolo
ID: 20101157
I've altered my connection string to use the one that "myLittleAdmin" advised:

ConnectString="Provider=sqloledb;Data Source=ms.yyyy.com,1433;Initial Catalog=dbsdfdfdf7;User Id=dbosdfdfd7;Password=*****"

I'm still getting the same issue.  How can I modify tables or data to change the name of the owner? is there a query or something I can send out or issue to do this for me?

any other ideas?!
0
 
LVL 3

Expert Comment

by:ibrusett
ID: 20101970
So you are using ole DB for SQL Server.
To add your user as db_owner to the DB launch this script:

USE [database name] -- same as initial catalog
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [newuserid]
GO

this should be enough.
0
 

Author Comment

by:Malevolo
ID: 20102718
USE [db221152097]
GO
ALTER AUTHORIZATION ON SCHEMA::[oldUSER] TO [newUSER]
GO

I ran the above command and it completed successfully, but the owner is still listed in tables as oldUSER.  Am I doing something incorrectly? I'm submitting this into "Query Analyser"
0
 

Author Comment

by:Malevolo
ID: 20102920
this is the error I get from FireFox when trying to load the page:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

/inpages/products.asp, line 25
0
 

Author Comment

by:Malevolo
ID: 20104954
I get the below error when trying to execute those commands. Please advise!!!

Error -2147217900
Object 'ACTNDetails' does not exist or is not a valid object for this operation.

EXEC sp_changeobjectowner 'ACTNDetails', 'dbo221152097'
EXEC sp_changeobjectowner 'AColour', 'dbo221152097'
EXEC sp_changeobjectowner 'AHangerTypes', 'dbo221152097'
EXEC sp_changeobjectowner 'ACategories', 'dbo221152097'
EXEC sp_changeobjectowner 'AResin', 'dbo221152097'
EXEC sp_changeobjectowner 'AR_Products', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailerColour', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailerResin', 'dbo221152097'
EXEC sp_changeobjectowner 'ARetailers', 'dbo221152097'
EXEC sp_changeobjectowner 'dtproperties', 'dbo221152097'
0
 
LVL 3

Accepted Solution

by:
ibrusett earned 1200 total points
ID: 20107343
>I ran the above command and it completed successfully, but the owner is still listed in tables as oldUSER.  
>Am I doing something incorrectly? I'm submitting this into "Query Analyser"

This is correct, but your newUSER should also be a owner for the database.

>Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Looks like that you does not have an ODBC DSN configured, or are referencing a wrong one in the "line 25"

>Error -2147217900
>Object 'ACTNDetails' does not exist or is not a valid object for this operation.

Be aware that sp_changeobjectowner acts only on existing table, view, user-defined function, or stored procedure in the current database.
To change the owner of a securable, use ALTER AUTHORIZATION. To change a schema, use ALTER SCHEMA.

Be also careful with sp_changeobjectowner, because it removes all existing permissions from the object. You will have to reapply any permissions that you want to keep after running sp_changeobjectowner.
So it is recommender that you script out existing permissions before running sp_changeobjectowner. After ownership of the object has been changed, you can use the script to reapply permissions. You must modify the object owner in the permissions script before running.

For more details search in books on line for sp_changeobjectowner!!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question