Ken Simmons (11/26/2008)

---

Failed logins are really the most important. It just depends on how busy your system is. It is all preference on what you would like to monitor.

I use the maintenace database to hold procedures and tables for...
reindexing, capturing database size for trending, capturing critical events from the error log, etc.

Jonathan Kehayias did a real good presentation called Automating Common DBA Tasks that has a lot of other good ideas. It can be found under the live meeting recordings here.

http://www.sqlpass.org/Community/SIGs/DatabaseAdministrationSIG/tabid/80/Default.aspx

Steven Webster (11/26/2008)

---

Great article Ken. Much appreciated.

Can I ask your views on the following - I recently had auditors in and one of their recommendations was to increase the maximum number of error log files to a value of 25000 or higher via a registry hack. In the vulnerability report they give the following reason for this:

To prevent the loss of auditing data, it is recommended that you set this value high enough that the error logs will not be overwritten when restarting the database. Also note that there is a stored procedure, sp_cycle_errorlog, that closes an errorlog and creates a new file. An attacker could attempt to cover their tracks by overwriting files using this stored procedure. It is recommended that you set the value high enough that an attacker could not cycle the logs enough times in a reasonable amount of time to overwrite the error log containing an attack.

Many thanks

Steven

Steven Webster (11/26/2008)

---

I'd certainly agree that a registry hack is not the way forward. Unfortunately though where I work an auditors word is always taken ahead of mine! Interesting point tho whether the registry would be overwritten if a service pack were to be applied - I'll test that out and get back.

Steven Webster (11/26/2008)

---

I'd certainly agree that a registry hack is not the way forward. Unfortunately though where I work an auditors word is always taken ahead of mine! Interesting point tho whether the registry would be overwritten if a service pack were to be applied - I'll test that out and get back.

MarkusB (11/27/2008)

---

I would add two more tasks to the checklist
1. Remove Built-in admiinstrators
2. Disable or at least rename the sa account (in 2005 or higher).

About hte loging of succesful logins I would recommend to use a LOGIN TRIGGER which records the login and the last time it connected. This avoids filling up the SQL errorlog and it's much easier to search through in case you want to know when was the last time a login was used.