SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automatic Mirroring fail over and see Login failed errors in error log of current Mirror server


Automatic Mirroring fail over and see Login failed errors in error log of current Mirror server

Author
Message
muth_51
muth_51
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 2905
Hi,

We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning without any issues. All the applications are also working as expected when the databases are failed over to Mirror server.

But i am seeing bunch of these error messages in SQL error log on the Current Mirror server(which was principal server before the failover). Can some one suggest is this a normal behavior or not?

Login failed for user 'XXXXXX'. Reason: Failed to open the explicitly specified database
Message
Error: 18456, Severity: 14, State: 38.[highlight=#ffff11][/highlight]


Thanks in advance.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19716 Visits: 17239
muthyala_51 (7/16/2014)
Hi,

We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning without any issues. All the applications are also working as expected when the databases are failed over to Mirror server.

But i am seeing bunch of these error messages in SQL error log on the Current Mirror server(which was principal server before the failover). Can some one suggest is this a normal behavior or not?

Login failed for user 'XXXXXX'. Reason: Failed to open the explicitly specified database
Message
Error: 18456, Severity: 14, State: 38.[highlight=#ffff11][/highlight]


Thanks in advance.

This link describes state 38 pretty well, most likely you have orphaned logins on the mirror partner. Synchronise the logins across from the Primary to the mirror

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3973 Visits: 5798
Expected behaviour, no?

The connection string specifies ServerName and FailoverPartner. So the app tries ServerName first, then FailoverPartner if unsuccessful.
The login errors you're seeing are the attempts of your app to connect to ServerName, which fail as it's the mirror and the database are unavailable. The connection then tries FailoverPartner and is successful.
muth_51
muth_51
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 2905
Perry Whittle (7/17/2014)
muthyala_51 (7/16/2014)
Hi,

We have high safety with automatic failover setup in our Sql Server 2008 R2 environment, and automatic failover is functioning without any issues. All the applications are also working as expected when the databases are failed over to Mirror server.

But i am seeing bunch of these error messages in SQL error log on the Current Mirror server(which was principal server before the failover). Can some one suggest is this a normal behavior or not?

Login failed for user 'XXXXXX'. Reason: Failed to open the explicitly specified database
Message
Error: 18456, Severity: 14, State: 38.[highlight=#ffff11][/highlight]


Thanks in advance.

This link describes state 38 pretty well, most likely you have orphaned logins on the mirror partner. Synchronise the logins across from the Primary to the mirror



Hi Perry,

But i have created the logins/transfered the logins from Principal to Mirror using sp_help_revlogin. I do not see any orphan logins on the mirror server. Thanks.
muth_51
muth_51
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 2905
Gazareth (7/17/2014)
Expected behaviour, no?

The connection string specifies ServerName and FailoverPartner. So the app tries ServerName first, then FailoverPartner if unsuccessful.
The login errors you're seeing are the attempts of your app to connect to ServerName, which fail as it's the mirror and the database are unavailable. The connection then tries FailoverPartner and is successful.


Hi Gazareth,
So as this is not expected behavior, can you please give me some insight on what should be i looking into to troubleshoot and find the cause of these failed logins. Interestingly all the websites are functioning fine. Is there anything i can look into IIS webservers end? to find out that the connection attempt is going to (old principal)current Mirror server instead to the actual current Primary server(old Mirror server). Thanks.
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3973 Visits: 5798
Sorry, bad grammar on my part!
I mean that is the expected behaviour.
muth_51
muth_51
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 2905
Gazareth (7/17/2014)
Sorry, bad grammar on my part!
I mean that is the expected behaviour.



But i am seeing like thousands of thousands login failure for every sec, is this really expected behaviour?

They stopped when i failed back the databases to primary and i don't see that behavior on Mirror server , why?
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19716 Visits: 17239
Orphaned users on the mirror.
Check sids between the primary and mirror

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3973 Visits: 5798
muthyala_51 (7/18/2014)
Gazareth (7/17/2014)
Sorry, bad grammar on my part!
I mean that is the expected behaviour.



But i am seeing like thousands of thousands login failure for every sec, is this really expected behaviour?

They stopped when i failed back the databases to primary and i don't see that behavior on Mirror server , why?


Because they never attempt to connect to the mirror in that situation.

If your connection string is Server=Server1;FailoverPartner=Server2

When Server1 is primary:
App tries to connect to Server1 -- Success!

When Server2 is primary:
App tries to connect to Server1 -- Failure <-- this causes the errors you're seeing
-> App tries to connect to Server2 -- Success!
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3973 Visits: 5798
Perry Whittle (7/18/2014)
Orphaned users on the mirror.
Check sids between the primary and mirror


Perry, this is happening on the primary server when it's acting as the mirror; all the databases are in the restoring state, can't be orphaned logins.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search