473,323 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Headaches moving an SQL2K database between servers

Greetings all,

This should be an easy task, and Im sure it is, but as many times as I have
tried, I cant seem to get this to work properly.

We changed ISPs recently from a shared host to a co-located server, and our
former host was nice enough to send us a backup of our old SQL2000 database
(about 5MB).

I went into Enterprise Manager, created an empty database with the same name
and used the Restore Backup tool successfully. I took a look at the db and
saw all the Tables and data intact.

Next I wanted to re-create the DSN to match the old one so that I wouldnt
have to fiddle with any of the old connection strings in my asp pages.
Thats where the fun began. The old DSN was created by our host via an email
request and I never got a look at the actual creation process of that DSN.
I created one on our colo server with the same name, but it would only pass
the connection tests when I used the Windows User Authentication rather than
SQL Server Authentication. No big deal I figured, and just went ahead and
set it up using the same DSN name.

Next I tried the main.asp page to test the DSN and lo and behold I got the
ODBC connection errors. I tinkered with the connection strings a bit and
managed to get a wide variety of connection errors and fine-tuned to the
point that it said "Unable to login with user 'SERVER169/nacog'". At this
point, I went into Enterprise manager and added 'nacog' to the User list and
the connection string no longer produced errors. (By the way, my connection
string simply contains "DSN=YAVAPAICONNECT;")

My next step was to actually execute a SELECT statement which produced the
following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on
object 'ADMINS', database 'YAVAPAICONNECT', owner 'dbo'.

/nacog/admin/main.asp, line 189

Why was I not surprised?

I went back to Enterpise Manager, saw the all the tables had 'dbo' as the
owner, and tried to give 'nacog' all the permissions at the table level, but
the error persists.

To preserve my sanity I stopped there, because I spun my wheels for hours
and days the last time this happened. and my eye has not stopped twitching
since ;)

I am quite sure that this has something to do with one or all of the
following:

1) The way SQL2K was installed (it was installed by someone else)
2) My creation of the db with the windows login, rather than SQL auth, prior
to import
3) My creation and handling of the DSN

My background is mainly in ASP programming, and I understand bits and pieces
of this puzzle, but for the life of me I cannot piece this thing together.
Can anyone help point me in the right direction or suggest a good tutorial?

I would be very thankful to anyone who could help put me on the right track.

Best Regards,

Ben M.

Jul 20 '05 #1
1 4133
> I went into Enterprise Manager, created an empty database with the
same name
and used the Restore Backup tool successfully. I took a look at the db and saw all the Tables and data intact.
There was no need to create the database beforehand. No harm done but
this step was unnecessary because SQL Server creates the database during
the restore.
(By the way, my connection
string simply contains "DSN=YAVAPAICONNECT;")
Since your old connection string did not specify a userid and password,
Windows authentication must have been specified in your old ISP DSN and
the anonymous IIS Windows account would have been used for the SQL
Server connection. That account must have been granted access to SQL
Server and added to your database. Permissions on your tables would
also have been needed unless the account was a member of a privileged
role, like db_owner.

When a user database is restored, logins are not restored because these
are stored separately in the master database. Database users will be
retained but may become orphaned because the mapping between syslogins
(in the master database) and sysusers (in your user database) gets
out-of-sync. This renders them useless unless you correct the mismatch
with sp_change_users_login or drop and recreate the users.

If you want to continue to use the anonymous IIS account, you can run a
script like the following to grant the account access to your database
(assuming your IIS anonymous account is SERVER169/nacog):

USE MyDatabase
EXEC sp_grantlogin 'SERVER169/nacog'
EXEC sp_grantdbaccess 'SERVER169/nacog'
GO

You can then grant object permissions to the account. The following
example creates a role, adds the account to the role and grants
permissions:

USE MyDatabase
EXEC sp_addrole 'AnonymousWeb'
EXEC sp_addrolemember 'AnonymousWeb', 'SERVER169/nacog'
GRANT SELECT ON MyTable TO AnonymousWeb
GO

Also, rather than use ODBC, you might consider using OLEDB and getting
rid of your nasty DSN. To do this, all you need to do is change your co
nnection string to the following format:

Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=MyDatabase;Data Source=MyServer;Application
Name=MyApp

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Ben M." <bm******@stny.rr.com> wrote in message
news:EL*****************@twister.nyroc.rr.com... Greetings all,

This should be an easy task, and Im sure it is, but as many times as I have tried, I cant seem to get this to work properly.

We changed ISPs recently from a shared host to a co-located server, and our former host was nice enough to send us a backup of our old SQL2000 database (about 5MB).

I went into Enterprise Manager, created an empty database with the same name and used the Restore Backup tool successfully. I took a look at the db and saw all the Tables and data intact.

Next I wanted to re-create the DSN to match the old one so that I wouldnt have to fiddle with any of the old connection strings in my asp pages.
Thats where the fun began. The old DSN was created by our host via an email request and I never got a look at the actual creation process of that DSN. I created one on our colo server with the same name, but it would only pass the connection tests when I used the Windows User Authentication rather than SQL Server Authentication. No big deal I figured, and just went ahead and set it up using the same DSN name.

Next I tried the main.asp page to test the DSN and lo and behold I got the ODBC connection errors. I tinkered with the connection strings a bit and managed to get a wide variety of connection errors and fine-tuned to the point that it said "Unable to login with user 'SERVER169/nacog'". At this point, I went into Enterprise manager and added 'nacog' to the User list and the connection string no longer produced errors. (By the way, my connection string simply contains "DSN=YAVAPAICONNECT;")

My next step was to actually execute a SELECT statement which produced the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'ADMINS', database 'YAVAPAICONNECT', owner 'dbo'.

/nacog/admin/main.asp, line 189

Why was I not surprised?

I went back to Enterpise Manager, saw the all the tables had 'dbo' as the owner, and tried to give 'nacog' all the permissions at the table level, but the error persists.

To preserve my sanity I stopped there, because I spun my wheels for hours and days the last time this happened. and my eye has not stopped twitching since ;)

I am quite sure that this has something to do with one or all of the
following:

1) The way SQL2K was installed (it was installed by someone else)
2) My creation of the db with the windows login, rather than SQL auth, prior to import
3) My creation and handling of the DSN

My background is mainly in ASP programming, and I understand bits and pieces of this puzzle, but for the life of me I cannot piece this thing together. Can anyone help point me in the right direction or suggest a good tutorial?
I would be very thankful to anyone who could help put me on the right track.
Best Regards,

Ben M.


Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: GuyInTn | last post by:
Hi all, I have been given the task of moving an sql database from one server to another. These servers are not on the same network. I know I can detach the database, copy it to a new location,...
1
by: barbara | last post by:
We've all data and log files located in SAN mount point. In case of primary server crash, is it possible to unmount the volumes and remount at secondary server? Do we have any problems with master...
3
by: Michael . | last post by:
I am trying to move a postgresql database from one server to another. The original server is 7.1.3, and the new one is 7.3.4. I went on the old and used the command: pg_dumpall > dump On...
1
by: Nirbho | last post by:
Hi, I've got a c#.net web app that uses SQl Server 2000. It all works very well on my Development PC, but now I've got to install the app onto some real servers. I have 3 servers: 2 for the web...
3
by: MartyNg | last post by:
I have been looking online for pointers, and read mixed things. I was hoping if I post direct questions here, I could get some solid answers. I work for a small company with less than 10 web...
0
by: BD | last post by:
I'm doing an install for a proof-of-concept environment; the server I'm installing to is W2K Sp4. I have installed to several servers in this environment under the same project, all of which are...
11
by: garyusenet | last post by:
Please explain to me. Im writing an app using visual studio professional and sql server express (although I also have sql server with one client licence.) My app uses c# , windows forms, and...
8
by: rick | last post by:
Hi I m trying to move only stored procedures from one database to another and also onto a database on another server, I tried db2 -x "select text from syscat.procedures where procschema =...
26
by: Bookham Measures | last post by:
Hello We are planning to set-up a load balanced web environment. Accordingly, we are going to change the session management on our website from the classic ASP Session State and session...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.