Audit Login Failed Event

I know my SQL server is detecting a login failure, but how do I know who log
in incorrectly? Is there a way to record the IP Address or PC name. I need
to find out if someone just typed in a bad password or if someone Is trying
to hack my server.
thank you
JohnAudit Login option is limited with info on the structure of the Windows
Event Log. You can get more info, including comuter name, with Profiler.
Check the Profiler in Books OnLine if this is what you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Jwtimoney" <anonymous@.discussions.microsoft.com> wrote in message
news:418A7655-A12B-4246-8BFD-491F6F087615@.microsoft.com...
> I know my SQL server is detecting a login failure, but how do I know who
login incorrectly? Is there a way to record the IP Address or PC name. I
need to find out if someone just typed in a bad password or if someone Is
trying to hack my server.
> thank you
> John|||As Dejan says, Audit Login failuers in SQLProfiler.

Audit failed logins

Hello:
I am trying to enable failed login attempt auditing and have succeeded with
by using a trace and event log alerts. The problem is there is no IP address
in the trace or alters. I have selected the "Host Name" column in profiler
but it only lists the hosts for successful logins, kind of pointless.
Any ideas on how I can get the IP address of failed login attempts?
Thanks.Hi,
SQL Server will not get IP Address of the Hosts machines. Probably you could
use the utilities like NETMON to track the IPAddress.
Thanks
Hari
SQL Server MVP
"emde" <emde@.na.com> wrote in message
news:%23XFJ8kjNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am trying to enable failed login attempt auditing and have succeeded
> with
> by using a trace and event log alerts. The problem is there is no IP
> address
> in the trace or alters. I have selected the "Host Name" column in profiler
> but it only lists the hosts for successful logins, kind of pointless.
> Any ideas on how I can get the IP address of failed login attempts?
> Thanks.
>
>|||As Hari has pointed out, you can't do this in SQL2000 however in SQL2005,
the IP address is logged in the SQL Errorlog for failed logins.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"emde" <emde@.na.com> wrote in message
news:%23XFJ8kjNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Hello:
> I am trying to enable failed login attempt auditing and have succeeded
> with
> by using a trace and event log alerts. The problem is there is no IP
> address
> in the trace or alters. I have selected the "Host Name" column in profiler
> but it only lists the hosts for successful logins, kind of pointless.
> Any ideas on how I can get the IP address of failed login attempts?
> Thanks.
>
>|||Good news. Hopefully this feature will be backported to SQL2000 in a future
service pack!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23rKrNUoNFHA.1396@.TK2MSFTNGP10.phx.gbl...
> As Hari has pointed out, you can't do this in SQL2000 however in SQL2005,
> the IP address is logged in the SQL Errorlog for failed logins.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org

Attempted Brute Force Attacks

It is not uncommon for me to review the event logs of our SQL Server and notice that someone is attempting to figure out the password for one of the SQL user accounts.......especially the "sa" username. But lately our SQL server has to be rebooted to where it is starting to become a nightly thing.

Last night the SQL Server was bombarded with attempted failed connections for 2 hours before it finally gave a blank BSOD. The SQL Server in question is in mixed mode and is a shared server. Strictly using Windows Authentication mode is not an option for us. The server has "beefy" hardware and has all updates and patches.

Sometimes, when I log on the server and notice that an IP address is making such attacks on the server, I put up an IPSEC policy against that IP. But that is not a good solution for reasons I dont think need to be outlined here.

So my question is, what can I do to better protect our SQL Server from these types of attacks? My thought on these attacks is not different than a DDOS that eventually takes the server down.

I have already done TCP/IP Hardening but not sure what else to do.

Thank you all for your replies.That sounds like Blaster to me. First order of business, patch your server, preferably to sp3a.

Next, consider killing port 1434 (and maybe 1433) at your firewall. That means that your SQL Server won't be visible outside the firewall, so this might not be a viable solution.

-PatP|||1434 is NIC Filtered as well as protected by IPSEC and Firewall.

1433 cannot be blocked becuase this is a public SQL Server used by many of our clients.

I already scanned the machine for Blaster and it is not affected. But thank you very much for your reply.|||Another solution might be to implement some form of challenge-response mechanism. For instance, if the access to the SQL Server is via a web page, have that web page (or one of its predecessors) enable the specific IP or MAC address for port 1433 for some arbitrary period of time. If the access is via a two-tier client, use an RPC to do the same thing. This will effecitvely cut off your SQL Server from all but pre-approved machines, and it is much less cumbersome for the user than using VPN to acheive similar results.

-PatP