sql server odbc: Headaches moving an SQL2K database between servers -- sql server odbc

Headaches moving an SQL2K database between servers

Ben M.
8/25/2003 2:45:56 AM

sql server odbc: 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.

Re: Headaches moving an SQL2K database between servers

Dan Guzman
8/25/2003 4:37:49 AM

[quoted text, click to view]

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.

[quoted text, click to view]

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.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

[quoted text, click to view]