Log IP address of remote login attempts
-
I would like to keep a log of the IP address of all failed login attempts (made over TCP/IP obviously) as this information does not appear to be included in the standard failure auditing.
Does anyone know an easy way of configuring this?
-
Not sure this is captured. Much of the information in the TDS stream that you know about a connection is sent by the user.
Running netmon on the server, catching 1433 traffic with some filter looking for the login would probably work.
Maybe BKelley has some other ideas.
Steve Jones
-
You could log the host name of the machine, and if you want you could create a scrip to execute a ping to the host and resolve and catch the ip adress.
-
The hostname can be 'spoofed' by simply setting it to some arbitrary name in a connection string, so if you really need to log this for security reasons that wouldn't help you much.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Are they hitting the DB directly? I use soap services to expose our com+ objects, and internally inside the com+ object when I obtain ObjectContext I also get a reference to the "Request" variable. The same one in asp. It lets me get the REMOTE_ADDR from the caller. I use that to log the machine the user is logging in from. But then IP's can be spoofed as well. But ip spoofing usually means hacker....
Tim C.
//Will write code for food
Tim C //Will code for food -
You could also do a DNS lookup through the API. That is if they ar enot putting spoofing their hostname like Chris said.
Tim C.
//Will write code for food
Tim C //Will code for food -
Sorry, should have mentioned that I will post example VB6 code to do DNS lookups if anyone wishes.
Tim C.
//Will write code for food
Tim C //Will code for food -
Thanks for the responses.
To clarify, the connection attempt is being made directly to the server through TCP/IP on 1433.
The netmon suggestion would certainly work and is probably what I will do, however I would be interested in a SQL Server based solution -- if only to increase my knowledge of what is going on down there.
I have virtually no knowledge on this topic. I am aware of a sysprocesses table which contains the hostname field and netaddress -- although as has been said I don't think these can be trusted as an indication of source IP. Would a row be added to this table for an attempted (rather than established) connection? And if so how would one capture it .. would you set a trace on the security logon event and use it to somehow trigger a backup of the sysprocesses table? (if this is the right approach I'ld be happy to do the background reading myself).
The connection attempt is totally invalid - only known IP addresses should be making the attempt -- and a firewall will ultimately filter this (don't get me started on why it is not there now!). However I remain interested in how this information could be obtained through SQL Server -- and slightly surprised that the details are not logged by default. Any good links/resources?
-
A few things:
(1) There is no pure SQL Server solution. SQL Server captures the MAC address but not IP address. This is a problem they'll hopefully fix in Yukon.
(2) You'll have to go with a solution like NetMon to capture network packets. If you know what you're looking for, you can capture the packets returned by the server indicating failure. The issue is TDS is not publically documented. However, you can find info on the protocol at the link I'm providing from folks that have reverse-engineered it. You'll clearly see "Login failed" and the token type will be 0xAA for Error.
http://www.freetds.org/tds.html
(3) DNS reverse lookup only works if the following is true. One, the client is connecting with the actual system name. Again, this can be spoofed by simply changing a text file and connecting with a File DSN. Two, the reverse lookup can be performed successfully on the DNS server because either (a) the client has a valid PTR record for the reverse lookup or (b) the reverse lookup zone is configured to use WINS and the client has a valid WINS entry (Windows 2000 DNS only).
(4) In addition to the firewall, you might also consider the use of IPSec. IPSec can filter based on protocol type (in this case TCP), port (1433 on one side), and IP range (meaning you can say only said computers can connect on 1433 to this server). This would be a layered approach in addition to the firewall. However, configuring IPSec will require the cooperation of your sysadmins as well as your network engineers. It's simple in concept, it can be difficult in implementing.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
The simple answer: No, it can't be done in SQL Server. If a connection attempt fails then no row is added to sysprocesses. You can have your server audit failed logins to the Event Log, but it doesn't say anything about where the connection came from, only which account it tried to log in with.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Hmm, you must have answered while I was writing my own answer Brian. Good post however. A question for you:
quote:
SQL Server captures the MAC address but not IP address.Where does SQL Server log the MAC address?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Oops, you're right. I just ran a job every second to try and capture sysprocesses and never grabbed the failure even though it temporarily has a SPID but it doesn't seem to make it to sysprocesses. My goof.
So bottom line is you'll need something that can do packet anaylsis. Of course, if you encrypt your data stream due to security reasons you're out of luck.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
(3) DNS reverse lookup only works if the following is true. One, the client is connecting with the actual system name. Again, this can be spoofed by simply changing a text file and connecting with a File DSN. Two, the reverse lookup can be performed successfully on the DNS server because either (a) the client has a valid PTR record for the reverse lookup or (b) the reverse lookup zone is configured to use WINS and the client has a valid WINS entry (Windows 2000 DNS only).Brian, why would you use Wins on Win2K? Win2K supports supports dynamic dns. Wins was a MSFT one-off to support networks using DHCP because they did not support dynamic DNS with NT 4.0. Are you running mixed-mode? That would be the only reason I can think of for using Wins. I have yet to run a mixed-mode network so I am not sure why one would continue to use Wins with 2K. All excellent points in your post, just wanted clarification on this one.
Tim C.
//Will write code for food
Tim C //Will code for food -
Windows 2000 DNS supports the use of WINS RR records to resolve queries against an existing WINS server if the DNS server isn't able to resolve the name.
While WINS is a technology that should be on its way out, there are a lot of environments that are still running WINS, especially ones that have not gone to Active Directory yet. Active Directory adoption is accelerating, but it's not across the board. That's why Microsoft has extended free support on Windows NT 4.0 to Dec 31 of this year and support of Windows NT 4.0 on a pay-basis (though no patches or hotfixes unless they are security related) until the end of 2004.
I make the assumption that some people reading this thread might still have WINS in their environment but are leveraging Windows 2000 DNS. Keep in mind you don't have to run AD to use Windows 2000 DNS. If you are a Microsoft shop and you need DNS, this is typically preferable to the other options.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
I know we're getting off topic, but we are native mode, but still have some NT4 stuff around. We still have WINS running, though I suspect it's more because people are scared to remove it than it is really needed. Everyone once in awhile we get some weird things happening that are traced back to records that need to be cleaned in WINS, but we still keep it around.
My vote on the main topic is still use NetMon or some other sniffer.
Steve Jones
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply