gaqjohn.blogg.se - Sql error 18456 severity 14 state 5

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. If this was a production server, SQL would be spending I/O time writing the same message to the Error Log.The reason I wanted to dig deeper was because the error was happening many times, almost every ten seconds, and in my view it had these potential problems: The server was running an instance of SQL Server 2008 and although it was a test instance, I decided to spend some time to find out what database was being accessed by this account. It could also be the default database for the login where explicit permission was not given.

The database could be offline, shutdown, deleted, dropped, renamed, auto-closed, or inaccessible for some other reason. As I found out later, States 38 and 40 (in SQL 2008 and later) and States 16 and 27 (before version 2008) mean that there is an issue with the database being connected to.

Like other error messages, it also has a State number and there are different State numbers associated with it, each having a different meaning for the failure. Įrror 18456 generally means a failed login attempt.