April 1, 2008 at 8:36 am
OK I had a topic yesterday Help!!! Trying to get SQL 2000 database up in SQL 2005. I got through and the gentleman was able to help me get the script out of my old db up to the 2005 db. And I know they came over because the old passwords work to log into Query Analyzer. But I am still getting an error on my front end client that 'Login failed for user'. My TCIP (or whatever it is!) is enabled and pipes are ON. Can anyone think of something else to check? I don't even know why the front end client asks for a user we don't even use! But it has basically locked us out of our client. I have tried both versions of the ODBC driver meaning sql server and sql native server. And I have tried have the db set to 2000 mode (80) and 2005 mode (90). Any other ideas?
Beyond that scope. What is the damage if I try to install SQL 2000 Server on an Small business server 2003? Our outsourced IT said not to do that. But that is beginning to be out last hope. Then maybe I could at least upgrade the database in place.
Also how do I close the topic I opened yesterday? since that one was solved as far as finding the user and permisisons and getting them over!
April 1, 2008 at 8:48 am
How did you move the databases over? Was this a wizard or backup/restore?
The logins don't move, so you have to move them. The easiest way to do this is search sp_help_revlogin on the MS site and use that to script out logins from the old server and run on the new server. Password should be the same.
If you have logins, are they mapped back to the users in the database? they might be orphaned, in which case, sp_change_users_login can help synch them up. Look that up in BOL.
I wouldn't recommend SQL 2000 on SBS, but you could just install it on another machine, get it up and you'd be working.
April 1, 2008 at 8:56 am
I had to do a restore. Our server crashed and all we have was the .bak file. I was able to build an SQL 2000 server on a separate machine not connected to anything and ran the script to pass over the users, passwords and permissions. That did work but still having the login issue. I will try the second sp that you posted and see what that gives me. What should it tell me when running it?
We had the SQL 2000 running on a server that ran Small business server 2000. But to get it up and running on the other machine I lost the network card when installing the Server OS. I had to install the OS to get SQL 2000 on a machine. I can't seem to find a download of SQL 2000 anywhere to add to just one of my regular computers, otherwise I would try that as an option as well.
April 1, 2008 at 9:11 am
When running the sp_change_users_login @Action='Report' under master db I get nothing. That is nothing appears in the report.
April 1, 2008 at 9:16 am
I do see in my 2000 instance db that can't connect to any computers, that LIMSUSA which is the one having a login error has is the owner to 5 specific tables: LIMSODBC, MASTERUSR, NENVIRONMENT, REGDICT, and ENVIRONMENT. If that LIMSUSA schema is under permissions under those tables does that mean it is the owner? I'm trying to find anything at this point!
April 1, 2008 at 11:28 am
OK, I remembered that I had an evaluation version SQL 2000 from a SQL class I took. I installed it on my computer versus the server. So now I have a 2000 instance of the database that I can access with my front end client. But now the question I have is what is the best way for me to get it upgraded to 2005 instance on the server (different computer). Anybody have any help on this? I guess it would be a bit easier if my 2000 instance was on the server, I'm guessing having it on a different machine may pose a problem. Any assistance anyone has on this would be greatly appreciated!!
(Should I start this as a new topic versus continuing this one?)
April 1, 2008 at 11:47 am
I would suggest following :-
1. Execute sp_help_revlogin on sql server 2000 and save the output.
2. Take backup of sql server 2000 database and restore on Sql Server 2005 on other machine..
3. Execute login script generated by sp_help_revlogin on sql server 2005 database.
4. Execute sp_change_users_login 'report' to see if still there are any orphaned users and fix them.
5. Update statistics for new sql server 2005 database.
If still have any problem with connectivity. Check or post the connection string of your frontend.
NJ
April 1, 2008 at 11:54 am
OK I am going to remove the SQL 2005 instance on server and re-install to make sure I start from scratch. But what do you mean by Update statistics for new sql server 2005 database?
Also once I get that connected will I need to change the ODBC on the workstations that connect through the 2000 driver?
April 1, 2008 at 12:08 pm
when running
use master
exec sp_help_revlogin
get this error message:
Server: Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'sp_help_revlogin'.
I've seen a couple of copies of the sp_help_revlogin which one do you recommend using to store on 2000 instance?
I'm going to use this one that SQLServerCentral posted in one of its articles:
Moving Your Users with Their Databases
By Corey Bunch, 2005/11/16
Total article views: 15225 | Views in the last 30 days: 195
Moving databases with Users, Logins, Passwords, and SIDs
Submitted by Corey Bunch
April 1, 2008 at 12:25 pm
you can use script on microsoft site..
Execute script in method 2 in following article..
http://support.microsoft.com/kb/246133/
After this do..
Exec sp_help_revlogin and save the output script to be executed on new Sql Server 2005
April 1, 2008 at 12:28 pm
I would suggest following :-
1. Execute sp_help_revlogin on sql server 2000 and save the output.
2. Take backup of sql server 2000 database and restore on Sql Server 2005 on other machine..
3. Execute login script generated by sp_help_revlogin on sql server 2005 database.
4. Execute sp_change_users_login 'report' to see if still there are any orphaned users and fix them.
5. Update statistics for new sql server 2005 database.
If still have any problem with connectivity. Check or post the connection string of your frontend.
NJ
OK I have gotten to step #5 (Nothing was listed in Step #4 when running).
NOw just need this info and I can test it:
OK I am going to remove the SQL 2005 instance on server and re-install to make sure I start from scratch. But what do you mean by Update statistics for new sql server 2005 database?
Also once I get that connected will I need to change the ODBC on the workstations that connect through the 2000 driver?
April 1, 2008 at 1:10 pm
ok just did a search on update statistics and found this:
Update statistics - To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server 2005 database.
So that is what I'm going to do next.
--Only one index was updated.
April 1, 2008 at 1:15 pm
update statistics is advised to improve the performance of queries as after the upgrade all the statistics get outdated. You can skip this step and test connectivity from frontend..
Also change the compatibility level of database to '80' which is default for sql server 2000. see sp_dbcmptlevel in BOL
sp_dbcmptlevel
April 1, 2008 at 1:15 pm
OK still getting error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login in failed for user 'LIMSUSA',LIMSPROD
This message appears when trying to connect on the front end client on another machine. Pipes are enabled and TCP is enabled.
ok the
EXEC sp_dbcmptlevel 'LIMSPROD', '80';
GO
went through fine, but still get the error above during login.
April 1, 2008 at 1:31 pm
Verify these options
1 The Trusted Connection box is not selected in the System DSN definition.
2 The SQL Server security mode is not Windows NT Integrated should be mixed mode.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply