SQL Server Performance

login failure on restore over the destination server

Discussion in 'General DBA Questions' started by sboyql, Apr 8, 2010.

  1. sboyql New Member

    Hi 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
      estore.bckp' WITH REPLACE7.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: BUILTINAdministrators
    EXEC master..sp_grantlogin 'BUILTINAdministrators'
    -- 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.BUILTINAdministrators
    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.
  2. MohammedU New Member

    First make sure what login is using the app?
    Make sure the login is not DISABLED or DENIED...
    You can use SSMS...
    Expand the logins folder/Double click on the login/click on STATUS tab....
  3. sboyql New Member

    there is only one user in the database 'dbo' no other user exist.

    in first made query to the sysuser table in database
    status for 'dbo' user is set to "2"
    and it is mapped on the sid of "sa" user in the master database i.e. 0 x 01
    this is the result of : select * from sysusers where name = 'dbo'
    • uid status name sid roles createdate updatedate altuid password gid
      • 12dbo0x010x012002-12-17 14:34:47.9502005-03-17 10:48:02.3200NULL 0
        • environ hasdbaccess islogin isntname isntgroup isntuser issqluser isaliased issqlrole isapprole
          • NULL 1 1 0 0 0 1 0 0 0
            • srvid sid xstatus xdate1 xdate2 name
              • NULL0x01182002-12-17 14:34:41.733 2010-03-15 12:16:04.980sa
                • password dbid language
                  • **********1NULL
                    • isrpcinmap ishqoutmap selfoutmap
                      • 0 0 0there is a table called 'dbo.epiusers'. this contains all the user information that that appears on the application interface, but login failure appears always upon checking in. can i find out how and where this table is mapped? i believe dbo.sysxlogin sysusers and dbo.epiusers have got something unlinked which is messing around. kindly help!!!
  4. MohammedU New Member

    Run the profiler to what code the application is execution...
    what login application is using? If it is using other than 'sa', you should have another user in the db.
  5. sboyql New Member

    whats profiler? please explain so that i could do that, if it could solve my problemin sql users, only one user exist. 'dbo' which is mapped to the 'sa' login.the database's own table 'dbo.epiusers' contains all the application user/ id information. I can only guess that it would be mapped over the 'dbo' user of the database. There aint any 'sid' column in the table 'dbo.epiusers'Please help.
  6. satya Moderator

    As you are using MSDE it will not have montioring tools such as PROFILER as mentioned above, I believe the behaviour you are having may be a flaky and if you can suggest the service pack of that MSDE engine by running a SELECT @@VERSION it will help, its best to udpate it with SP4 and even ask the vendor to do so.

Share This Page