Track IP Address of SQL access

Anyone know of any way to control or at least track the IP address of users hitting my MS SQL database? I realize I can do this from within my app, but I'm concerned with remote access that bypasses my app (eg SQL Server Management Studio). I've got good passwords and all that; but right now someone could be hacking in and I'd never know it.

A logon trigger will work, but these are not allowed (understandably) in the hosted environment.

An enhancement that would only allow access from specified IP addresses similar to the ftp and/or Control Panel API would be wonderful.

thanks

 

The only way anyone could do so would be with your authentication. If, as you say, your passwords are good, you don't really have anything to worry about as far as a compromised login (generally speaking of course, since logins are typically compromised on the user's computer these days rather than via a server or network).

Brute force/dictionary attacks are pretty common on the web servers, not as common on the database servers. The far more likely database exploitation scenario is SQL injection, which of course doesn't rely on authentication or direct server access.

All of which is to say that there are a lot of ways in, but common safeguards will protect you from all of them.

 

As mjp said this might be more trouble than it's worth. However if you've really convinced yourself that you really need to track or report when an unauthorised connection is made to your database, there's one technique I know of that might help:

This T-SQL has been tested and does work on the DASP SQL servers:

Code:

SELECT net_address FROM sys.sysprocesses WHERE spid = @@SPID

The net_address from sysprocesses is the mac address of a connected client so not an IP address as you originally mentioned but a mac address is just as reliable as an IP address for identification purposes. Yes they can both be spoofed so neither are infallible methods of client identification but for most purposes a mac address or IP address will often be good enough.

So how's this as a suggestion for something you could do:

1. Create a new table in your database to store a white list of safe well known mac addresses of clients that you know are authorized to have access to your database
2. Create a new table in your database to act as a reporting table to store rows identifying unauthorized access to your database
3. Create a new stored procedure / user defined function that when called, will execute the query above to identify the mac address of the connected client. When the mac address is not in your white list, your sproc / function can add a row to the reporting table you created at step #2
4. Add triggers to all of your tables for INSERT, UPDATE and DELETE operations to call the sproc / function you created at step #3

This goes some of the way to reporting unauthorized access to your database, but what do you do when a malicious hacker gets into your database, clears down your unauthorized access reporting table and deletes all your triggers? Here's where you need to get a bit clever with the data in the unauthorized access report table in the database - the idea is you use your web application to automatically report to you immediately when data in this table changes. If you do this then even if a hacker gets in to your db and starts taking things apart, your web application will have notified you of unauthorized access when it occurred. The damage may be done at that point but at least you know as soon as possible that it's happened and now you can go looking for your latest db backup..

The idea is you:

1. Setup Sql Server Broker Notification Services in your database. Yes it does work around here - I have it working with my application on the DASP Sql Server.
2. Your web application creates a subscription to a SqlDependency bound to a query on the unauthorized access db table you create above. It's likely this would be initially setup on application start in global.asax. The SqlDependency gets called back from the database whenever data in this db table changes. If you're familiar with this pattern you'll recognize it as a typical automatic cache invalidation mechanism.
3. When the SqlDependency is notified of the change, your web application knows that a client not in the white list has managed to gain unauthorized access to your db. Your web app can now send an email or do whatever..

About the last thing you'll need to do to make this all work reliably in near real time is to keep your web application alive and prevent your app pool from shutting down by creating a scheduled task to regularly ping your web site - you do this with the DASP task scheduler.

Yes it's all a bit long winded and perhaps beyond the call of duty..but at least there's always a way

 

Nice work. It seems like a pretty involved exercise unless you were sure you'd been breached, but I dig the process.

If jemery takes the time to implement it I'd be interested to see what he finds.

 

I know I'm probably paranoid, but just trying to lock things down as best I can.

I'm most concerned with unscrupulous downloads of our sensitive data. So although CystalsCMS's reply is very intriguing; it only traps updates, inserts and deletions and therefore doesn't solve my greatest concern. Though I'll definitely want to keep the mac address idea in mind. thanks.

Yes, the data in encrypted and I've also done all I can re: SQL Injection, etc. Just trying to cover all the bases I can think of.

Thanks for the replies and thoughts.