SQL Server Performance

Mirroring Issue with SQLServer Authentication Mode

Discussion in 'SQL Server 2005 Database Mirroring' started by kumaravel v, Aug 5, 2008.

  1. kumaravel v New Member

    <P mce_keep="true">I have configured Database Mirroring successfully between 2 SQL Server 2005 instances. My application is connecting to SQL Server using a SQL Server login, and is built using&nbsp;ODBC and the SQL Native Client. My connection strings/connection settings specify the correct information, including the appropriate failover partner. I have also created all the same logins on the mirrored server as on the principal server. Upon testing a database failure, the mirror successfully assumes the principal role and everything looks correct on the SQL Server. I can even connect successfully to the mirror using my Windows login. However, If i try to connect through mixedmode login, i am getting problem. I have mapped one database with that user in principle server, at the time of failover, the secondary(mirror) server which is not taking that mapped database automatically. because of this, my application is not working with the mirrored server.&nbsp;Is there a way to configure the mirroring setup so this is not a problem?</P><P align=left>I have tried with the below command&nbsp;in my mirroring server which i have created from priciple server :&nbsp;</P><P align=left>create<FONT color=#000000 size=2> </FONT><FONT color=#0000ff size=2>login</FONT><FONT size=2><FONT color=#000000> [prod_user]&nbsp; </FONT></FONT><FONT color=#0000ff size=2>with</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>password</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> 0x0100b1a7168e8d161a607ac718e9e7b3a95cd73d405a893e8ab2 </FONT><FONT color=#0000ff size=2>hashed</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>sid</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> 0x138c225bb9509745a4d7e1209887ff2b</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>check_expiration</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>ON</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>check_policy</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>OFF</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>default_database</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> prodDB</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>default_language</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> us_english</FONT></P><FONT size=2><P align=left><FONT color=#003300><U>I&nbsp;got the error below&nbsp;: <IMG src="http://forums.microsoft.com/msdn/WebResource.axd?d=NySzF1eivP_rMoc50GQJzcvS4MHMOEKwYrCIgDtzuzlw7GsNki3H_INlfYaLgkxFAL9T9LN8WIWIcruN7zjfywlHQYIkPzHn5pbVs-MEQgc1&amp;t=633337194230757564"></U></FONT></P><FONT color=#ff0000 size=1><P>Database 'prodDB' cannot be opened. It is in the middle of a restore.</P><P>- kumar</P></FONT></FONT>
  2. satya Moderator

    Welcome to the forums.
    The obvious reasons of using windows authentication is you are connected to the server and not to the database, in this case when you are using SQL authentication it will be checking the status of databases on the mirror server and causing such an error.
    You should not attempt to open or access database on mirror server when it is paired with DB mirroring.
  3. kumaravel v New Member

    hi satya
    I understood that we cannot access as well as open the database on mirror server at the time of mirroring, but all the way what i am expecting is once primary gets failure, then secondary server has to take care of rest of all jobs including user level database mapping. Otherwise i will be facing problem in my front end application at the time of connecting the mirrored server with sqlserver authentication mode. I dont have any problem with windows authentication mode. Problem only in sqlserver authentication mode!!
    thanks
    --kumar
  4. satya Moderator

    ...take care of jobs...
    Are you talking about SQLagent jobs?
    If so you need to ensure to create them (disable too) on the mirror server and same for login creation as the system databases will not perform mirroring.q
  5. kumaravel v New Member

    thanks satya. Let me try and let you know.
    kumar.[:)]

Share This Page