AUDIT_LOGIN_FAILED Trace Event

Question

AUDIT_LOGIN_FAILED Trace Event

adb2303

SSCertifiable

Points: 7588
More actions
September 27, 2011 at 9:11 am

#248781

Hello,
I've got a job that runs every night doing loads of funky stuff. It runs fine, and completes all the tasks it needs to. However, during the job, it generates a dozen or so login failures (Error: 18456, Severity: 14, State: 16). I've checked the owner's permissions, and it has all the rights it needs to over the default database, and any other databases the job needs to switch to.
I have now set-up Event Notifications using the AUDIT_LOGIN_FAILED trace event. I now get emails anytime there is a login failure on any of our production boxes. However, the database name always returns as 'master', even though I know for sure that the users default database is something else, or I've explicitly tried to connect to a user database.
With SQL Server 2005, using this approach, can you audit the database name a failed login is trying to access?
Thanks,
Andrew

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

I believe the users default database is only used after a successful connection, and only if they user did not explicitly specify the database in the connection string.

so i don't think you can get the database information; there's no database context until after the login occurs, i believe, unless the connection string explicitly passed it.

if it was passed as part of the connection string, it would be available....i'm testing that now to be sure, as it's been a while since i fiddled with login auditing.

if the login is prevented for some reason(login trigger, bad password, whatever), it's only after the connection is successful that you get the database name, right?

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!

Lowell SSC Guru Points: 323518 More actions · Answer 2

just to confirm: database name does not seem to be available at login.

i created a login trigger to prevent all logins,and then changed my connection string to explicitly have the database name.

when my login trigger raised the error message, the master database being used for the login was returned, and not the one passed via connection string:

unless someone can show otherwise, i'd say that a database name is not available for failed logins.

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!

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Did you try setting that user's default database to Master and specifying the db name in the connection string? Chances are it's already in the string, so having it was the default db is redundant and causes oddities like this where the login is relying on the database being accessible.

Lowell SSC Guru Points: 323518 More actions · Answer 4

that's what i had tested, kind of...

my scenario was:

my default database was "PERFECT1000",

my connection string was "SandBox",

the failed login returned master for the db_name() finction that i logged.

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!

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

Good to know that it always attempts the default db first before the explicitly stated one in the string. I have a tendency to set the default database to master and grant them public to it for that exact scenario...Master is never inaccessible (or if it is...crap), it forces the devs to use explicit connection strings, and they'll get a better error message on their end if there's an issue with the database offline/etc. It's even more troublesome on non-production servers after database refreshes when SIDs may have changed/etc.

adb2303 SSCertifiable Points: 7588 More actions · Answer 6

Hi, thanks for your input. I'd performed the same tests with changing the users default database and connection string and saw the same results. I thought I might have made a mistake, or there was another way to get at the information I need.

So, what's the point of having the database name available as part of the AUDIT_LOGIN_FAILED trace event? Presumably for failed logins, it will always return master.

thanks again, Andrew