Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server 2016 Database Audit Specification

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
US
Hello All!
I am working on getting the Failed_Database_Authentication_Group and Successful_Database_Authentication_Group specifications to work, but my changes do not seem to be taking.

I disable the audit and the database audit specification, I modify the database audit specification, adding

Code:
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP )

save the changes, enable the audit and then the database audit specification, logout of SSMS and then log back in to SSMS and I do not see any entries with action_id = DAGS. My question is, does this not work the way I am expecting it to? I was thinking that it would record every time a user connects to that server and authenticates to the DB in which the database audit specification was created. I have gone into the same database audit specification and added

Code:
ADD (SELECT ON OBJECT::[dbo].[CIB_ThirdPartyProvider] BY [public])

and then run a select against that table and it logs the query, but still doesn't seem to log the database authentication.

Am I missing something?

Thanks in advance,
Willie
 
Looking at the MS Docs for that audit group, it says that this audit "Indicates that a principal successfully logged in to a contained database." I'm not sure if it's referring to a true Contained Database or not, but if it is that may be your problem (unless you actually are using a Contained Database).

You may want to try using SUCCESSFUL_LOGIN_GROUP instead, although I believe it tracks every successful login to the server so you may get a lot of results from it.


Hope This Helps!

Eric Cobb | SQL Server Database Administrator
MCSE: Data Platform
MCSE: Data Management and Analytics

"My work is a game, a very serious game." - M.C. Escher
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top