October 21, 2003 at 9:29 am
Hello all,
Several weeks ago I was shoved into the position as group DBA since our old DBA turned out to be a flake. Since then I've gotten a good grasp of T-SQL and SQL Server itself, and really enjoyed myself.
Today, however, it looks like someone managed to use direct access to the database to make some nasty changes and deletions. Fortunately, everything is carefully backed up, and our security team is looking at firewall and other access issues, but I've been given a requirement to determine who exactly accessed SQL Server and when.
With that in mind, how do I check the SQL Server logs to determine IP addresses of users? I can't seem to find it in the documentation, and one would think that there would be an easy way to read into that big .LDF file.
Thanks!
Eamon Voss
'Learn a new database system in 20 minutes and get a raise out of it? Sure!'
October 21, 2003 at 10:01 am
The SQL Server logs won't store the IP addresses, unfortunately, so you won't find that information available. SQL Server keeps track of MAC addresses in sysprocesses, but that's not recorded to a log anywhere. And in the event where you have subnets, etc., it's going to have the MAC address of the router/switch and not the actual client.
If you're talking about the database log files (which SQL Server uses to keep track of changes to the database) you'll need a program like Log Explorer from Lumigent. Lockwood Tech also has a new log reader out.
If you mean SQL Server logs as in where SQL Server stores what's going on in the server (like an event log for the OS), it's a text file and can be read by any text program or through EM.
If you know improper access is happening, look at running traces. At the very least you can run Profiler interactively on a workstation but you can also use the various trace stored procedures to accomplish the same thing.
You mentioned firewall... has the application been checked to see if it is vulnerable to SQL Injection attacks?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
October 22, 2003 at 8:40 am
If you’re concerned about a web app, I wrote an ASP application that recorded info on all users of our corporate ASP pages, including IP addresses. It was pretty easy - the ASP pages simply passed the IP address to sql server.
I also kept other info on what users did & when they did it. I also ran a trace (aside - be careful to limit the info in traces cause the tables produced otherwise can get very large, very fast), so I could track pretty much anything.
It was about the brightest thing I did on that job, other than leaving. It stopped the ever-popular blame IT game, because I could track what users really did. All this tracking could also help security I suppose, but that didn't seem to be the problem.
Hope this helps. I imagine any server-side web app can provide the IP address to your database. Then there is probably a method of getting it in the logs if you want it there.
Data: Easy to spill, hard to clean up!
October 22, 2003 at 9:13 am
Hi,
SQL server cannot record the connection IP address, you can try the ways which Bkelley and Stubob said. And you also can audit the login, maybe it will help u.
Good Lucky.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply