Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  MSDE (2000)  login failure on destination server

Author  Topic  sboyql Starting Member 1 Post Posted - 2010-04-08 : 01:16:31 Hy every one I am using Micrsoft Sql desktop engine installed by an application "epihandy". The databse is created by the application so i have no idea about the rules and policies regarding the access. I have a main server A and a Backup server B where i need to use the database, while not interfering the main server. I have use the following commands to backup and restore the data over the destination server. Prior to this, i have also checked the password for "sa" being same on both server. I used the script from here and it seemed to work great. http://www.mssqltips.com/tip.asp?tip=1174 I used the following to backup the database: 1. in command window, type: osql -S "(local)\EPIHANDY" -d "master" -E 2. Set the epihandy0906 database in single user mode, enter the following + enter 3. alter database epihandy0906 set SINGLE_USER 4. GO 5. Then enter the following and hit enter: 6. RESTORE DATABASE epihandy0906 FROM DISK = ' c:\epibackup\restore.bckp' WITH REPLACE 7. GO 8. Then enter the following and hit enter: 9. alter database epihandy0906 set MULTI_USER 10. GO Eventually the restoration process was successful. The issue rose when I try to access the database through the epihandy interface. there is a table in the database 'dbo.epiusers' which contains users, ids passwords etc which the application uses to access the database. All the user information from the “dbo.epiuser” is retrieved as expected but upon inserting the password, “LOGIN FAILURE” occurs. I also envisaged over the “LOGIN AND PASSWORD TRANSFER” while moving the database. I went through an article provided by Microsoft( http://support.microsoft.com/default.aspx?scid=kb;en-us;246133 ) and proceeded step by step. I added sp_help_revlogin procedure in the master database over the original server. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Output is as under: DECLARE @pwd sysname -- Login: BUILTIN\Administrators EXEC master..sp_grantlogin 'BUILTIN\Administrators' -- Login: distributor_admin SET @pwd = CONVERT (varbinary(256), XYZ) EXEC master..sp_addlogin 'distributor_admin', @pwd, @sid = ABC, @encryptopt = 'skip_encryption' I then created the same login at the destination server. It did not work either. I also matched the SID’S of all existing logins in the “sysxlogins” table in MASTER as well as “sysusers” table in epihandy0906. One thing that I unexpectedly found out on the live server was that the “sa” login was having two SID’S. I believe SID’S are supposed to be unique. I made the following query. use master go SELECT sid FROM dbo.sysxlogins WHERE name = 'sa' Following were the results: 1. 0x01 2. 0xFFBC9E1A55F9934EA4AFDEAE8AF9771C use master go SELECT sid FROM dbo.sysxlogins WHERE name = 'distributor_admin' 1. 0xFFBC9E1A55F9934EA4AFDEAE8AF9771C 2. 0xFFBC9E1A55F9934EA4AFDEAE8AF9771C 3. NULL use master go SELECT name FROM dbo.sysxlogins 1. sa 2. BUILTIN\Administrators 3. distributor_admin 4. NULL 5. distributor_admin 6. sa 7. distributor_admin There are two entries for the “sa” login. The only database user “dbo” seems to be correctly matched with the “sa” account along with the SID. i guess the uses in the tabled 'dbo.epiusers' are mapped back to the database sysuser table and sysuser is in turn mapped over sysxlogin table in the master database. Is the linkage between the NT login and the user information changed? I am using windows authentication. I had also user sp_change_users_login and sp_helplogins as well. There were no orphaned users found. It seems that the SIDS are matched properly but still no access to the database. I will appreciate any one can help me to go around it as I have tried many alternatives. thanx