locked
Login failed error when conneting to mirrored db. RRS feed

  • Question

  •  

    Hi All,

     

    I have been struggling to make my classic ASP to call mirrored db successfully for the last few days. I am using a ODBC SQL Native Client. The front end application works perfectly untill the database is switched from principal to mirror server. I am getting the following error.

    Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'

    [Microsoft][SQL Native Client][SQL Server]Cannot open database "testDb" requested by the login. The login failed.

     

    I will briefly explain what I have done. I hae created a SQL Login testUser and this login has the dbowner rights to testDb. This is being done in Principal server. Till now the application works fine. Once I switch it to failover, the mirror server acts as a principal - this is what I was expecting. On database level it works. But my ASP application doesnot seem to like it. When I closely looked into the security, I found that testUser does exist in the testDb but the real problem is testUser is not associated with testUser Login. It's not possible to change as well.

     

    Any sort of pointer is appreciated.

     

    Many thanks.

     

    Sincerely,

    Milan G

    Monday, September 17, 2007 12:59 PM

Answers

  • To correct the issue with the user not being tied properly to the Login, take a look at sp_change_users_login in BOL.  When failing over, this will need to be run against all SQL users to tie them back to their logins.  If you're able to use Windows authentication, that's preferable since the SIDs are the same and you don't need to mess with doing this.

    Monday, September 17, 2007 6:43 PM
  • Hi M,

     

    Many thanks for the pointer. Finally, I succeeded with your pointer. I found the this link a useful. http://www.mssqltips.com/tip.asp?tip=1166

     

     

    cheers,

    Milan G

    Tuesday, September 18, 2007 11:06 AM

All replies

  • To correct the issue with the user not being tied properly to the Login, take a look at sp_change_users_login in BOL.  When failing over, this will need to be run against all SQL users to tie them back to their logins.  If you're able to use Windows authentication, that's preferable since the SIDs are the same and you don't need to mess with doing this.

    Monday, September 17, 2007 6:43 PM
  • Hi M,

     

    Many thanks for the pointer. Finally, I succeeded with your pointer. I found the this link a useful. http://www.mssqltips.com/tip.asp?tip=1166

     

     

    cheers,

    Milan G

    Tuesday, September 18, 2007 11:06 AM