How to find the IP of someone repeatedly trying to login as SA

I'm running a Windows 2003 Server, with MS SQL Server 2000 which is using
mixed mode security. The SQL server is being used for development and
testing purposes.
The server needs to be exposed to the internet as it is also accessed off
site
When checking the windows logs, the application log showed repeated failed
login attempts to the sa SQL account and new entries were being created at a
rate for a few a minuet.
How can I find out the IP address of where these login attempts were coming
from?
and also how can I get the SQL server to ignore requests for this address or
maybe ip range?
To temporarily stop this I just disabled the TCP/IP network protocol for the
SQL server.
Thanks for any help,
GrantGreat questions:
How can I find out the IP address of where these login attempts were coming
from?
Answer: Best ways are either running Microsoft Network Monitor on the
server & make a capture of the traffic.
Or, netstat -an to capture the sessions connected via IP. To send a login
the client first needs to make
a tcp session using the 3 way handshake.
and also how can I get the SQL server to ignore requests for this address or
maybe ip range?
Answer: By default there is no way to do this from SQL. You'd need to
configure a firewall in front of SQL to allow connection requests from only
one machine.
I've requested that we allow auding for failed login request & log the IP
address for the next release of SQL.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin McDonnell [MSFT] wrote:

> and also how can I get the SQL server to ignore requests for this address
or
> maybe ip range?
> Answer: By default there is no way to do this from SQL. You'd need to
> configure a firewall in front of SQL to allow connection requests from onl
y
> one machine.
If you are familiar with IPSec you could set up a custom IP Sec policy
to only allow connections from machines that you trust. You don't want
to specify who you deny, but instead specify who you trust.

> I've requested that we allow auding for failed login request & log the IP
> address for the next release of SQL.
>
It would be nice if the auditing was built to use windows auditing,
however I'm sure that this is only available if using windows
authentication, and in this case it was sql auth that was tried.
Aaron Weiker
http://blogs.sqladvice.com/aweiker
http://aaronweiker.com/sql

how to filter output based on user's AD group membership?

I want to filter reports based on a users active directory security i.e. what
parameters they can select, what columns they can see
But I can't figure out the best way of doing this and the documentation on
AD seems to assume that the user already understands AD.
From what I can tell I have two options
1) use sql with the openquery syntax - don't think this is going to be an
option due to the hassle here of setting up linked servers (bureaucracy in
the extreme)
2)use system.directoryservices with some code built into to the report - i
don't really understand how to do this, none of the code samples i have seen
seem to do what I want e.g. pass in username from global report parameters
along with the groupname i want to check against, and return whether they are
in that particular group as true/false
help greatly appreciated!
do you know a better way of doing this? code samples? etc
thanks!AD queries in SQL Server using the ADSI can be a problem... I am told it will
only search the first 1000 rows returned by the AD..
if you are using SQL 2005... Take a look at the sys.login_token
It shows all of the AD groups the user is a member of.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"adolf garlic" wrote:
> I want to filter reports based on a users active directory security i.e. what
> parameters they can select, what columns they can see
> But I can't figure out the best way of doing this and the documentation on
> AD seems to assume that the user already understands AD.
> From what I can tell I have two options
> 1) use sql with the openquery syntax - don't think this is going to be an
> option due to the hassle here of setting up linked servers (bureaucracy in
> the extreme)
> 2)use system.directoryservices with some code built into to the report - i
> don't really understand how to do this, none of the code samples i have seen
> seem to do what I want e.g. pass in username from global report parameters
> along with the groupname i want to check against, and return whether they are
> in that particular group as true/false
> help greatly appreciated!
> do you know a better way of doing this? code samples? etc
> thanks!