Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Login Auditing Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing thru SSMS instance properties and I know that the messages are written to the SQL Server error log and the Windows Application log, however, the security people would like to see official documentation for every version and edition of SQL Server. I have searched the Microsoft support site, but could not find anything that listed or talked about where these messages are written to. Does anyone have any information on this?
Post #1392578
Posted Tuesday, December 4, 2012 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285
well, for the SQL log, exactly where the logs go is configurable...when you first install the server, one of the options is to decide on the folders.

it defaults to a certain path, of course if not changed @ setup

where it is right now, you can find out via TSQL like this:
SELECT SERVERPROPERTY('ErrorLogFileName');

you can see the current setup in the facetsSee the ErrorLogPath this screenshot was from showing how to change the default paths for databases and backups);
to change it after installation, i believe is a registry edit and a stop and start of the server is required.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1392589
Posted Tuesday, December 4, 2012 9:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:46 PM
Points: 42, Visits: 290
Hello,

Please refer to the below link. The second sentence says "Login auditing can be configured to write to the error log on the following events".

http://msdn.microsoft.com/en-us/library/ms175850(v=sql.105).aspx
Post #1392591
Posted Tuesday, December 4, 2012 9:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
I am aware of both these thing, but this does not answer the question I asked.
Post #1392593
Posted Thursday, December 6, 2012 7:24 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:50 AM
Points: 709, Visits: 1,441
Which question exactly was not answered?

Where are login audits stored?
SQL Error log, if you configure auditing successful/failed logins through the instance properties. This is the same for every version of SQL.

MS documentation stating such:

SQL 2012
http://technet.microsoft.com/en-us/library/ms175850.aspx

SQL 2008 R2
http://technet.microsoft.com/en-us/library/ms175850(v=sql.105).aspx

SQL 2008
http://technet.microsoft.com/en-us/library/ms175850(v=sql.100).aspx

SQL 2005
http://technet.microsoft.com/en-us/library/ms175850(v=sql.90).aspx


Joie Andrew
"Since 1982"
Post #1393521
Posted Thursday, December 6, 2012 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 8:12 AM
Points: 137, Visits: 297
I know that, when you are auditing logins, the messages go to the SQL Error log and the Windows Application log. I have been asked to provide documentation that explicitly states that these are the locations where the messages go.
Post #1393609
Posted Thursday, December 6, 2012 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 12,921, Visits: 32,285
that documentation here is a little inferred ("the log" means sql error log), but "you mean like this one:
http://msdn.microsoft.com/en-us/library/ms175850(v=sql.90).aspx


How to: Configure Login Auditing (SQL Server Management Studio)
SQL Server 2005 Other Versions 1 out of 5 rated this helpful - Rate this topic
Use login auditing to monitor SQL Server Database Engine login activity.

Login auditing can be configured to write to the error log on the following events.

Failed logins
Successful logins
Both failed and successful logins


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1393616
Posted Tuesday, December 11, 2012 12:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236
login auditing can be done easily using below option.

Failed logins
Successful logins
Both failed and successful logins

which write to error log .

but if you select successful logins and Both failed and successful logins

it will create events in huge manner in errorlog,which might affect your performance of server
Hence select only failed login.
Post #1394920
Posted Wednesday, February 6, 2013 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 2:20 PM
Points: 4, Visits: 7
I have then tried loging into the server with an incorrect password and then checked the security logs again with event viewer and there is no event listed.

bloated stomach
Post #1416432
Posted Wednesday, February 20, 2013 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:29 PM
Points: 4, Visits: 8
We tried to do the same and couldn't find a solution in audit vault. Be interested to know how to setup this. There is just too much noise from our app and since we trust the ip it comes from - we don't want to audit that but we need to audit if it's not from the trusted ip. we are evaluating a 3rd party tools called core audit by blue core research to do this.

business plans
Post #1422332
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse