November 19, 2009 at 3:10 pm
I had setup logshipping for production DB from prod to DR server. Now we are testing ours users to connect to Website thru Dr server DB(stand by mode). throwing error like login can't have access to open database and can't read database.
Please advice...I noticed that once did logshipping in secondary server permisssion are not there for login to that database
November 19, 2009 at 3:15 pm
Are you also copying over the logins? google sp_help_revlogin to see the process for doing this.
---------------------------------------------------------------------
November 19, 2009 at 3:46 pm
No. but same logins and permission are in DR server whatever production has
November 19, 2009 at 4:13 pm
how were they copied over? what is the exact error in the errorlog (need the state value in particular from the login failure)
---------------------------------------------------------------------
November 20, 2009 at 8:28 am
Manually I created logins with same passwords in secondary server. I totally configured secondary server same as primary server. and had setup logshipping for user databases(restored user db's in standby mode)
Do I need to restore system databases too???:hehe:
Our's is a healthcare application and users will connect to a website thru database. Going forward we want to use secondary server as main primary server. so testing
error
database is not accessible, login failed to connect...
November 20, 2009 at 8:36 am
When you create the logins on the destination server, you need to use the sid from the source server. The sids on the logins are not aligned because they are different. You can't change the database, so you need to create the login with the sid from the source.
CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }
<option_list1> ::=
PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]
<option_list2> ::=
SID = sid,
...
November 20, 2009 at 8:38 am
Looks like its login issue. Try this,http://support.microsoft.com/kb/918992/. This will help you to transfer logins and passwords between servers.
November 20, 2009 at 8:41 am
or else. drop the logins and use sp_help_revlogin to copy them over again.
OR
when you failover properly for the first time use stored procedure sp_change_users_login to tie the user in the database to the login again. I recommend the first option.
---------------------------------------------------------------------
November 20, 2009 at 10:13 am
george sibbald (11/20/2009)
or else. drop the logins and use sp_help_revlogin to copy them over again.OR
when you failover properly for the first time use stored procedure sp_change_users_login to tie the user in the database to the login again. I recommend the first option.
I have taken sp_help_revlogin script and ran in secondary server. command successfull
AND MANUALLY CREATED USERS IN SECONDARY SERVER system databases, which are in primary server
So it means everything is fine now?
November 20, 2009 at 10:32 am
you will be fine if you if you:
run sp_help_revlogin against primary server and save output to file
copy file to secondary and load into SSMS query window
edit to remove those logins you do NOT want to replace on secondary (e.g. builtin\admins, service accounts)
drop the users logins from secondary
run the sp_help_revlogin output
this will tie the logins on the secondary to the userids in the database and therefore logon should be successful
---------------------------------------------------------------------
November 20, 2009 at 11:38 am
george sibbald (11/20/2009)
you will be fine if you if you:run sp_help_revlogin against primary server and save output to file
copy file to secondary and load into SSMS query window
edit to remove those logins you do NOT want to replace on secondary (e.g. builtin\admins, service accounts)
drop the users logins from secondary
run the sp_help_revlogin output
this will tie the logins on the secondary to the userids in the database and therefore logon should be successful
Ihave dropped all login in secondary servers and ran sp_help_revlogin output but throwing error
November 20, 2009 at 11:58 am
A couple things to try. Try to connect to the database through Management Studio with one of the logins, preferably one from the site, and see if that login fails for you.
If that does not fail, then check the SQL server log for a login failure, if they are turned on. If you are getting a login failure, maybe the password on the front end app is incorrectly set. The next option would be to run a trace (SQL Profiler) and catch all of the login errors and any other errors in the profiler to see why it might be failing.
If you have access to the app programmer, maybe they can trace against the secondary database to see where the error is occuring.
November 20, 2009 at 12:04 pm
george sibbald (11/20/2009)
you will be fine if you if you:run sp_help_revlogin against primary server and save output to file
copy file to secondary and load into SSMS query window
edit to remove those logins you do NOT want to replace on secondary (e.g. builtin\admins, service accounts)
drop the users logins from secondary
run the sp_help_revlogin output
this will tie the logins on the secondary to the userids in the database and therefore logon should be successful
Still SID is different for 2 logins....how to map SID?
November 20, 2009 at 12:19 pm
Still SID is different for 2 logins....how to map SID?
If it is just for 2 logins, then follow my previous post on how to create the login and specifiy the sid. The other posts related to sp_help_revlogin should have done this.
CREATE LOGIN MyLogin PASSWORD = 'password', SID = [0x4t8eur7djfdf8d73kfjdkfjd8fskd9]
You will need to include the other options as well if you need them, like DEFAULT_DATABASE, CHECK_EXPIRATION, CHECK_POLICY
If you include the sid from the other server in this creation statement, it will align the sid and the login will work. You can get the sid from the other machine by executing SELECT * FROM syslogins on the master database.
November 20, 2009 at 1:31 pm
chk2009 (11/20/2009)
george sibbald (11/20/2009)
you will be fine if you if you:run sp_help_revlogin against primary server and save output to file
copy file to secondary and load into SSMS query window
edit to remove those logins you do NOT want to replace on secondary (e.g. builtin\admins, service accounts)
drop the users logins from secondary
run the sp_help_revlogin output
this will tie the logins on the secondary to the userids in the database and therefore logon should be successful
Ihave dropped all login in secondary servers and ran sp_help_revlogin output but throwing error
what error? check the errorlog and post whats in there for the login error.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply