I am getting same Error “Login failed for user ‘XXX’. If possible, you can do a failover and check if the problem persists. When it tries to do so, it cannot access the database because the DB is unavailable when acting as a secondary. I believe this is happening because your app is trying to connect to the secondary database in the mirroring setup, instead of the primary one. The next occurance showed up in the SQL Server error log. In my case the message Id was 4060 so I executed the following: This way you don't have to turn on a profile trace to capture the error. HostName was also not required because I knew the name of the requesting server already.Īnother option would be to find the "cannot open database" message in the sys.messages system table and alter it to log when it occurs. In this case, it was the account's default database: master. Again, you may think the DatabaseID or DatabaseName columns would yield the required information, but in reality it only shows the context of the database from which the connection was requested. This would also mean other messages sent to the client would also be captured, such as "database context changed to." etc.įor the columns, only five were kept: TextData, ApplicationName, NTUserName, LoginName and SPID. This is the event that would capture the error returned to the client (in this case the web server the connection request was being made from) and the message would contain the name of the database being accessed. The second and the most important event was the "User Error Message". First, the "ErrorLog" event - this would trap every instance the Error Log is accessed. Each login attempt would be recorded and the trace would have unnecessary entries that you would not need.įrom the Errors and Warnings category, only two events were chosen. It may be tempting to keep the "Audit Login" event as well, but if you think about it this would cause the trace to grow really big very quickly.
Instead, I chose two different categories of events:įrom the Security Audit category (which is automatically listed in the default trace), I only kept the "Audit Login Failed" event. Since the problem was not associated with a query, I could get rid of the default TSQL and Stored Procedures events listed. Next came the question of choosing events. nslookup is a command that can take an IP address as a parameter and tell you the name of the machine: it's like the reverse of the ping command. And of course there is the question of security - you should want to know why database access is being refused.įrom the frequency of the messages, I knew the request was probably coming from an application or web server, so I ran the following command against the IP addresses to confirm this.With the same error message repeated over and over, it would be difficult to sift through the log and a DBA could miss other errors or warnings.Things would be even worse for instances where the log was not cycled regularly. Even with this instance where the Error Log was cycled every night, it was still taking time to load thousands of rows of log entries. The potentially large Error Log would take longer and longer to load.In other words, disk access would be almost continuous.