locked
Mirroring Issue with SQLServer Authentication Mode RRS feed

  • Question

  •  

    Hi

     

    We have a Database mirroring environment set up using sql server 2005 Enterprise.


    The setup behind primary, secondary and witness servers and the databases are fully synchronized.

    The principal server has 1 database which is mapped with sqlserver user (sqlserver authentication mode).

    Whenever the principal get failed, the secondary server is taking care of the rest job automatically.
    The issue is whatever i mapped the database to that particular user in primaryserver which is not

    reflecting over into secondary server automatically. Because of this, i am not able to continue my

    application with this sqlserver authentication mode even mirror is working.  Hope you guys understand my issue!!

     

    Anyone has any ideas why this mapping setup is not restoring into secondary server? 
    Any suggestion is greatly appreciated!  Thanks!

    kumar

    Friday, August 1, 2008 5:38 PM

Answers

  • Before configuring mirroring, create the same login with same SID as in principal in the mirror server using Create login with SID command. This will ensure that when you failover you can still connect to mirror database. Use the below command and get the SID of the principal server login, (Run this is in principal server)

    Code Snippet

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

    Then execute the below command in mirror database before configuring mirroring,
    Code Snippet

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

    Replace the SID in the above query with the one from principal server.Refer this link for more info, http://www.mssqltips.com/tip.asp?tip=1166

    - Deepak

    Saturday, August 2, 2008 12:26 AM

All replies

  • Before configuring mirroring, create the same login with same SID as in principal in the mirror server using Create login with SID command. This will ensure that when you failover you can still connect to mirror database. Use the below command and get the SID of the principal server login, (Run this is in principal server)

    Code Snippet

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

    Then execute the below command in mirror database before configuring mirroring,
    Code Snippet

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

    Replace the SID in the above query with the one from principal server.Refer this link for more info, http://www.mssqltips.com/tip.asp?tip=1166

    - Deepak

    Saturday, August 2, 2008 12:26 AM
  • thanks for your response deepak.

     

    when i create login user in mirror server, i am not able to map the mirrored

    db which i have already mapped in pricipal server. Is it ok to create login user without

    mapping in mirror server ?  Will it take this database mapping automatically

    when the primary server gets failed ?

     

    Anyhow let me try to "create login...with sid" and will let you know.  Hopefully

    this has to work!!!

     

    Thanks

     

    --Kumar

     

    Tuesday, August 5, 2008 1:13 PM
  • You have to create the login with sid in mirror server before configuring mirroring..

    - Deepak
    Tuesday, August 5, 2008 4:53 PM
  • hi deepak

     

    Thanks for your quick response.

    As per your suggesion, I have removed mirror and trying to create a login in my secondary database

    with the below command :  (This script i created from my primary server.)

     

    create login [sql_wyn_odbc_prod]

    with password = 0x0100b1a7168e8d161a607ac718e9e7b3a95cd73d405a893e8ab2 hashed,

    sid = 0x138c225bb9509745a4d7e1209887ff2b,

    check_expiration = ON, check_policy = OFF, default_database = wri_prod,

    default_language = us_english

     

     

     

    I got the error below :

    Database 'wri_prod' cannot be opened. It is in the middle of a restore.

     

    FYI : In secondary database, shows like wri_prod(Restoring...).

     

    Can you tell me where i am making mistake ?

     

    -- kumar

    Tuesday, August 5, 2008 5:43 PM
  • Execute this command in mirror server after removing mirroing. It will bring the mirror database online from restoring state.


    RESTORE DATABASE dbname WITH RECOVERY


    - Deepak

    Wednesday, August 6, 2008 12:46 AM
  •  

    Thanks for your reply deepak.

     

    I agree your point the way said all the steps. When i finish all your steps, ie removing mirroring, run the Restore statement,  run the create login statement, etc.

    Now i am trying to setup the mirror between two databases which should need (restoring... ie, norecover mode).

    This case, i have to drop the database and go for restore with norecover mode ?  Is it the right way ??

     

     

    Wednesday, August 6, 2008 2:45 PM
  • No need to drop the database. Just take a full backup and 1 tran log backup and restore it using with norecovery option then configure mirroring as shown here, http://sql-articles.com/index.php?page=articles/cng-dbmrr.htm

    - Deepak
    Wednesday, August 6, 2008 2:55 PM
  • thanks deepak

     

    Let me try and let you know. thanks for your timely help.

     

    --- kumar.

    Wednesday, August 6, 2008 7:46 PM