April 8, 2010 at 1:57 am
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.
April 16, 2010 at 4:02 am
sohaib.nomani (4/8/2010)
use mastergo
SELECT sid FROM dbo.sysxlogins WHERE name = 'sa'
2.0xFFBC9E1A55F9934EA4AFDEAE8AF9771C
I didnt understand how redundant logins exists in msdb ? two Sa account is very weird thing.Try exec sp_changedbowner 'sa','true'.
and have your tried to login with sa ? did you get any error ? if yes, then post the error state
or at last if possible restore msdb.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 17, 2010 at 2:46 pm
firstly using the SA account to provision access to a sql server database is a really bad idea.
If you restore a database to a different server without exporting the server logins, they will not exist on the new server and if you do create them manually they will have a mismatch in the SIDs.
To check for orphaned users in the newly restored database on the new server use the following
exec sp_change_users_login 'report'
post back the results of this query!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply