Database Mirroring Login Failures at both Primary and Secondary in high Safety mode

Question

Hello,

I am seeing Login Failures for the same user/Login Accounts at same time at both Primary and Secondary node of Database Mirroring session configured in high Safety mode.

I do not know what connection string is used but I wanted to know if error log is also copied across in Database mirroring session?

Is this normal?

---

Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/

Answer 1

You can turn on a Profiler to specify Audit Login event..

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

I know that the login attempts are coming from Citrix Server with same IP Address and time.

What other information can be found by running a profiler?

---

Vamshi SQL DBA(MCITP) My Blogs: http://sqlserver-dba.co.uk http://sql-developers.co.uk/

Answer 3

I would like to know if this is default behaviour in Database Mirroring if configured in High Safety mode with Witness Server?

Thank you...

---

Vamshi SQL DBA(MCITP)
My Blogs:
SQL Server Administration: SQL Adminstration
SQL Development: SQL Development

Answer 4

A login failed message means exactly that: Login failed... This can either be because the user is missing the right he needs or that the DB is not accessible at that time (e.g. because the server is mirror at that moment.) Having the login state code would maybe help to tell you more...

Answer 5

check the SQL server error log & share the complete login failure error message you are getting.

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 6

Have you tried copying the logins from the principal to the mirror using this KB article? The SIDs on the logins need to be the same in order for SQL Server to resolve the credentials and their appropriate permissions

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 7

Hi All,

Thanks for the reply's. Please find below the login failure messages which are same at both nodes with same username and same time. It is a SQL Login coming from same ip address on both nodes.

 Error: 18456, Severity: 14, State: 8.                                                                                                                 
14-09-2012 04:42 Logon    Login failed for user 'Xuser'. Reason: Password did not match that for the login provided. [CLIENT: XX.XXX.XXX.XX]

Why is application trying to talk to Database Mirror instance at first instance when principal server is still available? So, wanted to check by any chance this is default behaviour where SQL Errorlog login failures are copied from principal to Mirror server by default?

@Edwin: Creating login is ok but want to know why application is trying to connect to Mirror instance when Principal is still available?

Thanks for all reply's........................

---

Vamshi SQL DBA(MCITP)
My Blogs:
SQL Server Administration: SQL Adminstration
SQL Development: SQL Development

Answer 8

No messages get transfered from principal to mirror, no. So if you see a login failed it means the application is trying to login there.

Why? Well, this mainly depends on the application. Either it has troubles connecting to the primary or it is just badly configured.

The error message you see is very clear though, the user on your mirror has a different password than the one on the principal, or maybe it has a different SID...

To futher troubleshoot you also have to look at the application and see what connection string it is using there.

Answer 9

The Logins are scripted using sp_revlogin script and so they have same SID on both nodes with same passwords.

I have just checked SIDs of a login on both Principal and Mirror to see it matches.

I will need to check the connection string used from Application to see if uses something weird in there.

Thanks for letting me know that its not default behaviour.

---

Vamshi SQL DBA(MCITP)
My Blogs:
SQL Server Administration: SQL Adminstration
SQL Development: SQL Development

Answer 10

Checked the Connection String of the Application.

Actuall the application is installed on top of Citrix with ODBC connection(ODBC connection in ODBC Data Source Administrator) with connection string having mirror Server name configured to the Mirror Server Name in SQL Server Native Client 10.0.

I can not think of how to get rid of this issue of Login Failures on Mirror because when citrix has other users connected to primary, why is it trying to connect to Mirror when a user is not able to login to Primary Database.

Any ideas ....

---

Vamshi SQL DBA(MCITP)
My Blogs:
SQL Server Administration: SQL Adminstration
SQL Development: SQL Development