locked
Cannot Login to failover database. RRS feed

  • Question

  • I setup a mirrored database with a witness server. I can login to the database just fine when I set it up.
    The servers are in Active Directory but the account "rps" is an SQL account.

    When I failover the database for testing, I cannot login to the mirrored database even though it is now the princpal database and it is syncronized.

    Here are the errors I receive.

    11/13/2007 15:05:13,Logon,Unknown,Login failed for user 'rps'. [CLIENT: 172.23.10.118],
    11/13/2007 15:05:13,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.,


    Any idea on how to fix this?
    Tuesday, November 13, 2007 9:18 PM

Answers

  • I guess the login 'rps' is not present in mirror server hence after you failover you are unable to connect to the server ! refer,http://www.mssqltips.com/tip.asp?tip=1166 you need to create the login in the mirror server using the same SID as the principal server........

    Generally when configuring mirroring you need to create the same logins in principal and mirror server so that at failover you can connect to mirror server ! if you are unable to connect to the db you can make use of the sp_change_users_login to map the logins with the users !

     

    - Deepak

    Tuesday, November 13, 2007 9:29 PM
  • I think that login rps already exist in mirror server !Pls drop the login 'rps' and recreate it using the SID as,

    Code Block

    CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>

    Try running the above query and see if it works ?

     

    -Deepak

    Tuesday, November 13, 2007 10:12 PM

All replies

  • I guess the login 'rps' is not present in mirror server hence after you failover you are unable to connect to the server ! refer,http://www.mssqltips.com/tip.asp?tip=1166 you need to create the login in the mirror server using the same SID as the principal server........

    Generally when configuring mirroring you need to create the same logins in principal and mirror server so that at failover you can connect to mirror server ! if you are unable to connect to the db you can make use of the sp_change_users_login to map the logins with the users !

     

    - Deepak

    Tuesday, November 13, 2007 9:29 PM
  • I did setup the RPS account on the mirror server but I don't know how to get the SID from the Primary.

    How would I do that?
    Tuesday, November 13, 2007 9:35 PM
  • Pls execute the below query in principal server and get the SID for 'rps' login

    Code Block

    select name,sid from sys.sql_logins where name ='rps'

     

     

    - Deepak

    Tuesday, November 13, 2007 9:49 PM
  • I have the SID now but when I execute the script on the mirror server I get

    Msg 15025, Level 16, State 1, Line 1
    The server principal 'rps' already exists.

    as an error.
    Tuesday, November 13, 2007 9:52 PM
  • I think that login rps already exist in mirror server !Pls drop the login 'rps' and recreate it using the SID as,

    Code Block

    CREATE LOGIN <loginname> WITH PASSWORD = <password>, SID = <sid for same login on principal server>

    Try running the above query and see if it works ?

     

    -Deepak

    Tuesday, November 13, 2007 10:12 PM
  • That was it. I deleted the rps user from the Mirror server and ran the CREATE LOGIN command and it worked.

    I am new to Mirroring and none of this was in any manual I could find.

    Thanks a lot for your help.
    Wednesday, November 14, 2007 12:56 PM
  • I am Glad that it helped you out !
    you can make use of these links to configure mirroring,
    http://sql-articles.com/articles/intdbmrr.htm  - Prerequisites
    http://sql-articles.com/articles/lship/config-lship.htm - Configuring Database Mirroring
    http://sql-articles.com/articles/ad-dbmrr.htm   -  Pros and Cons of  Database Mirroring
    http://sql-articles.com/articles/dbmrrlchg.htm  - Role Change in Database Mirroring

    If you have anyother questions feel free to ask !
    - Deepak
    Wednesday, November 14, 2007 1:39 PM