Login failed database mirroring from application

Question

I have database mirroring set up on server A and Server B(sql 2005 sp3-9.0.4035) the database is principal on server- A and the database is mirror on server- B. We have one user 'test' with same SID on both servers. This 'test' user tries to connect to principal database from ADO.NET application. In the DSN file we have server A as principal from the front-end the process runs fine but it gives error saying 'the server is not responding, try to connect to server failed.' when i check error log files on sql server i cant see any error msgs but i opened error log files on mirror server-B, I can see this error Error: 18456, Severity: 14, State: 16. Login failed for user 'test' [Client : 192.168.1.XXX] I search on state-16 but name of the db in DSN is correct and we didnt specify failover partner name in our DSN then why its going to server-B(mirror) and try to connect? How can I stop this problem?

Answer 1

Have a look at the link http://www.sql-articles.com/Joomla/articles/troubleshooting/troubleshooting-login-failed-error-18456 for different states with error 18456.
For the state 16 , it means the default database for test user in unavailable (mirrored , restoring) and In your case ,the problem occurs because the mirrored database is still not online SQL cant recognise the default db for test . So you wont be able to connect to the mirored db with any user , unless the mirrored db is failed over.

Hope this clarifies your problem.

---

Thanks, Leks