Post reply

Finding info about a failed login

  • January 15, 2016 at 9:25 am

    #323632

    I am getting the following error every 5 minutes in my error log:

    Login failed for user '<DOMAIN>\<user>'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]

    I tried using the Profiler to "Audit logins", and I added the DatabaseName and Success columns to try and determine which database is being attempted, but the Profiler looks to only monitor successful logins (unless I'm using the Profiler wrong, which is entirely possible). Any hints on using the Profiler, or Extended Events, or some other method to find the issue would be appreciated.

    The <local machine> piece has me really confused as SQL Server 2008 is the only thing running on this machine.

    Thanks

    -=Chuck

  • January 15, 2016 at 10:27 am

    #1852296

    it looks like you've got a process, maybe a sql job or windows scheduled task, that runs every five minutes?

    might also be an application or Service, installed on the server, that does monitoring or something.

    that error often occurs when a logins default database has been deleted or renamed, so i'd check that first.

    review the login and see what the default database is:

    select default_database_name,* from sys.server_principals where name = 'domain\user'

    select * from sys.server_principals WHERE default_database_name is not null and default_database_name not in(select name from sys.databases)

    after that, it's the connection string of the application that could be selectign the database; so if you had , for example, a monitoring software or service running on the server, and someone dropped it's database, you'd get that error.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 15, 2016 at 10:36 am

    #1852298

    I checked the default database, which is 'master' for this account. Hmmm... I'll look around on the server some more to see what kind of services might be running on the machine itself, but I didn't find anything significant the first time.

    Thanks for the help, btw.

    --=Chuck

  • January 15, 2016 at 10:47 am

    #1852301

    You need to audit for failed logins using "Audit Login Failed".

    You might also check the default trace if the event occurred recently it should have captured it in that file as well, under "Audit Login Failed".

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • January 15, 2016 at 11:02 am

    #1852302

    "Audit Login Failed" worked, but there's not much more information than I received in the actual error message. I did at least find that whatever this is, is using the .Net Sqlclient Data Provider, so let's see where that takes me.

    Thanks,

    --=Chuck

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply