Problem migrating SQL Server 2000 database to 2005

I have an application that is running on Windows server 2003 and SQL 2000
Server. This application was written in VB so it has both windows client
interface and Web client interface. Due to the age of this server we
purchased a new server and installed windows server 2003 R2 SP1 and SQL
Server 2005 standard edition. To migrate the SQL Server 2000 database I
backed up the SQL 2000 server database and then restore it to the SQL 2005
server. I then run the sp_revlogin per the KB - I migrated the logins with
their SIDs and password. I tested it by login into management studio and
running queries in the database with the migrated logins. I then run update
statistics and DBCC updateusage.
However, when the users try to open the application they get the error
message that, " Login failed for AppUser" Appuser is the login that the
application used to authenticate to the database. I have tried the operation
allover again a few times to ensure that I did not miss anything in the
process. I even tried upgrade compatibility level to 9.0 but the same error
is still coming. I have also tried detach/reattach but I got the same error.
I tried the copy database wizard and got the same error.
Any assistance will be greatly appreciated.
Thanks!
ODOD wrote:
> I have an application that is running on Windows server 2003 and SQL 2000
> Server. This application was written in VB so it has both windows client
> interface and Web client interface. Due to the age of this server we
> purchased a new server and installed windows server 2003 R2 SP1 and SQL
> Server 2005 standard edition. To migrate the SQL Server 2000 database I
> backed up the SQL 2000 server database and then restore it to the SQL 2005
> server. I then run the sp_revlogin per the KB - I migrated the logins with
> their SIDs and password. I tested it by login into management studio and
> running queries in the database with the migrated logins. I then run update
> statistics and DBCC updateusage.
> However, when the users try to open the application they get the error
> message that, " Login failed for AppUser" Appuser is the login that the
> application used to authenticate to the database. I have tried the operation
> allover again a few times to ensure that I did not miss anything in the
> process. I even tried upgrade compatibility level to 9.0 but the same error
> is still coming. I have also tried detach/reattach but I got the same error.
> I tried the copy database wizard and got the same error.
> Any assistance will be greatly appreciated.
> Thanks!
> OD
>
Did you try connecting via Management Studio using this "AppUser" login?
Verify remote connectivity to the server? Confirm that AppUser
exists, reset the password?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy:
I connected to the database via Management studio using the AppUser login
and password. AppUser exists on the database. Connectivity is good since the
server also host CRM and its database for our company. Any ideas?
OD
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45017AD2.8050402@.realsqlguy.com...
> OD wrote:
>> I have an application that is running on Windows server 2003 and SQL 2000
>> Server. This application was written in VB so it has both windows client
>> interface and Web client interface. Due to the age of this server we
>> purchased a new server and installed windows server 2003 R2 SP1 and SQL
>> Server 2005 standard edition. To migrate the SQL Server 2000 database I
>> backed up the SQL 2000 server database and then restore it to the SQL
>> 2005 server. I then run the sp_revlogin per the KB - I migrated the
>> logins with their SIDs and password. I tested it by login into management
>> studio and running queries in the database with the migrated logins. I
>> then run update statistics and DBCC updateusage.
>> However, when the users try to open the application they get the error
>> message that, " Login failed for AppUser" Appuser is the login that the
>> application used to authenticate to the database. I have tried the
>> operation allover again a few times to ensure that I did not miss
>> anything in the process. I even tried upgrade compatibility level to 9.0
>> but the same error is still coming. I have also tried detach/reattach but
>> I got the same error. I tried the copy database wizard and got the same
>> error.
>> Any assistance will be greatly appreciated.
>> Thanks!
>> OD
> Did you try connecting via Management Studio using this "AppUser" login?
> Verify remote connectivity to the server? Confirm that AppUser exists,
> reset the password?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||OD wrote:
> Tracy:
> I connected to the database via Management studio using the AppUser login
> and password. AppUser exists on the database. Connectivity is good since the
> server also host CRM and its database for our company. Any ideas?
>
Verify that the connection string or ODBC connection that the app uses
is configured properly. If you can connect as AppUser from Management
Studio, then the problem is not on the database side.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy:
That is correct. The problem was in the application. The developer gave me
the correct password that was hard coded in the application and it works.
Thanks!
OD
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:450182C2.2070001@.realsqlguy.com...
> OD wrote:
>> Tracy:
>> I connected to the database via Management studio using the AppUser login
>> and password. AppUser exists on the database. Connectivity is good since
>> the server also host CRM and its database for our company. Any ideas?
> Verify that the connection string or ODBC connection that the app uses is
> configured properly. If you can connect as AppUser from Management
> Studio, then the problem is not on the database side.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com