Login errors

A login error is if a connection is trying to be made to e.g. an offline database, or a user or service suddenly can’t connect to a database anymore.

To query the Performance Store database for login errors, use:

select top 100 e.server_name, reverse(substring(reverse(e.message), charindex('[ .''', reverse(e.message)) + len
('[ .'''), charindex('''', reverse(e.message), charindex('[ .''', reverse(e.message)) + len('[ .''')) - 
charindex('[ .''', reverse(e.message)) - len('[ .'''))) database_name, *
from dbo.v_errors e
where e.error_number = 18456
and e.state = 38
and e.client_app_name != 'Microsoft SQL Server Management Studio'
and e.server_name = '[server_name]'
order by e.sample_time desc

A quick overview of the number of login errors can be seen on the Overview 1 dashboard:

In-depth information about the errors can be seen on the Errors dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.