How find user is sending a packet every .02 sec to MS SQL?

pigeon

I have 2 users that their client software must be going crazy.. they
are sending packets every .02 seconds to the db server... I know this
because I stuck a sniffer on teh traffic.. but now i just need to know
what user is doing this (all traffic is encrypted.. so i couldn’t
sniff out that.. i could only get an IP).

any ideas?

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict232446.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=805276

Jul 23 '05 #1

joshsackett

You can open Enterprise Manager and check Current Activity. Match the
IP address to the hostname or ID. If that doesn't give you good
information then you can run "nbtstat -an IPADDRESS" at a command
prompt and maybe get lucky.

Jul 23 '05 #2

Simon Hayes

"pigeon" <UseLinkToEmail@dbForumz.com> wrote in message
news:4_805276_8ad527d329c64023e6bc7f994748bbb8@dbf orumz.com...[color=blue]
>I have 2 users that their client software must be going crazy.. they
> are sending packets every .02 seconds to the db server... I know this
> because I stuck a sniffer on teh traffic.. but now i just need to know
> what user is doing this (all traffic is encrypted.. so i couldn't
> sniff out that.. i could only get an IP).
>
> any ideas?
>
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/General-Disc...ict232446.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=805276[/color]


If you already have an IP address, then you should be able to find the PC by
checking for its DHCP lease or DNS entry.

Simon

Jul 23 '05 #3

serge

One thing you can do to help with your task:

In your SQL EM check the Current Connections and search for the IP
address. When you find the IP address you will also have the SPID, this
can show you the T-SQL statement being run.

Also if you know the IP address, you can find out the computer name.
You find out the computer name, you can find out the user(s) using those
computers.


"pigeon" <UseLinkToEmail@dbForumz.com> wrote in message
news:4_805276_8ad527d329c64023e6bc7f994748bbb8@dbf orumz.com...[color=blue]
> I have 2 users that their client software must be going crazy.. they
> are sending packets every .02 seconds to the db server... I know this
> because I stuck a sniffer on teh traffic.. but now i just need to know
> what user is doing this (all traffic is encrypted.. so i couldn't
> sniff out that.. i could only get an IP).
>
> any ideas?
>
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:[/color]
http://www.dbforumz.com/General-Disc...ict232446.html[color=blue]
> Visit Topic URL to contact author (reg. req'd). Report abuse:[/color]
http://www.dbforumz.com/eform.php?p=805276

Jul 23 '05 #4

pigeon

"serge" wrote:[color=blue]
> One thing you can do to help with your task:
>
> In your SQL EM check the Current Connections and search for
> the IP
> address. When you find the IP address you will also have the
> SPID, this
> can show you the T-SQL statement being run.
>
> Also if you know the IP address, you can find out the computer
> name.
> You find out the computer name, you can find out the user(s)
> using those
> computers.
>
>
> "pigeon" <UseLinkToEmail@dbForumz.com> wrote in message
> news:4_805276_8ad527d329c64023e6bc7f994748bbb8@dbf orumz.com...[color=green]
> > I have 2 users that their client software must be going[/color]
> crazy.. they[color=green]
> > are sending packets every .02 seconds to the db server... I[/color]
> know this[color=green]
> > because I stuck a sniffer on teh traffic.. but now i just[/color]
> need to know[color=green]
> > what user is doing this (all traffic is encrypted.. so i[/color]
> couldn't[color=green]
> > sniff out that.. i could only get an IP).
> >
> > any ideas?
> >
> > --
> > Posted using the http://www.dbforumz.com interface, at author's request
> > Articles individually checked for conformance to usenet[/color]
> standards[color=green]
> > Topic URL:[/color]
> http://www.dbforumz.com/General-Disc...ict232446.html[color=green]
> > Visit Topic URL to contact author (reg. req'd). Report[/color]
> abuse:
> http://www.dbforumz.com/eform.php?p=805276[/color]

Well.. they are logging in over the internet.. So even if i know the
computer name, it will not help me that much (since there are hundreds
and hundreds of people logging in all the time).

Also, will the user be listed in active connections evey though his
login fails? or if he logs in and logs out very quickly? I believe his
software islogging in and out very quickly (many many many times a
second).


What do you’ll think?

thanks!
Lee

Jul 23 '05 #5

serge

I am not a security expert so you'll still have to search more on this
topic. What I would do is turn on SQL Server auditing for Failure.
Do properties on your SQL server in SQL EM, Security tab, Audit Level.

If you say there are 2 users but don't know the users. Do you mean
you have 2 IPs that keep hitting your SQL server every .02 second?
Those IPs, why don't you have them blocked if you don't know if
they are valid IPs or someone trying to attack your SQL Server
or like you say some software is hitting your SQL server non-stop?

Can you run SQL Profiler and see if you can see what is being run
by those two "users" every .02 seconds?



[color=blue]
> Well.. they are logging in over the internet.. So even if i know the
> computer name, it will not help me that much (since there are hundreds
> and hundreds of people logging in all the time).
>
> Also, will the user be listed in active connections evey though his
> login fails? or if he logs in and logs out very quickly? I believe his
> software islogging in and out very quickly (many many many times a
> second).
>
>
> What do you'll think?[/color]

Jul 23 '05 #6

pigeon

"serge" wrote:[color=blue]
>I am not a security expert so you’ll still have to search more
>on this
>topic. What I would do is turn on SQL Server auditing for Failure.
>Do properties on your SQL server in SQL EM, Security tab, Audit[/color]
Level.[color=blue]
>
>If you say there are 2 users but don’t know the users. Do you
>mean
>you have 2 IPs that keep hitting your SQL server every .02 second?
>Those IPs, why don’t you have them blocked if you don’t
>know if
>they are valid IPs or someone trying to attack your SQL Server
>or like you say some software is hitting your SQL server non-stop?
>
>Can you run SQL Profiler and see if you can see what is being run
>by those two "users" every .02 seconds?
>
>
>
>[color=green]
>> Well.. they are logging in over the internet.. So even if i know[/color]
>the[color=green]
>> computer name, it will not help me that much (since there are[/color]
>hundreds[color=green]
>> and hundreds of people logging in all the time).
>>
>> Also, will the user be listed in active connections evey though[/color]
>his[color=green]
>> login fails? or if he logs in and logs out very quickly? I[/color]
>believe his[color=green]
>> software islogging in and out very quickly (many many many times[/color]
>a[color=green]
>> second).
>>
>>
>> What do you’ll think?</font>[/color][/color]

Well.. I found their hostname (server) through profiler.. And i see
they are trying to login to ’sa’ account.. but i wouldn’t think
trying to login (every 5seconds or so) would result in me getting
packets from that IP every .02 seconds...


What can I do after this? I guess i could block their ip via my cisco
PIX firwall.. but is there anyway to automatically block a user that
tries to login to ’sa’? From what I have read, SQL doesn’t have a
feature to block users based on their ip


thanks for any suggestions!

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Disc...ict232446.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=810667

Jul 23 '05 #7

Erland Sommarskog

pigeon (UseLinkToEmail@dbForumz.com) writes:[color=blue]
> What can I do after this? I guess i could block their ip via my cisco
> PIX firwall.. but is there anyway to automatically block a user that
> tries to login to 'sa'? From what I have read, SQL doesn't have a
> feature to block users based on their ip[/color]

Well, as far as SQL Server is concerned, that user is 'sa', so you
better make use of that firewall.

And be very glad that this was no serious attempt to brute-force
attack. In that case, the intruder would probably have cracked your
sa password by now.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #8

serge

> Well.. I found their hostname (server) through profiler.. And i see[color=blue]
> they are trying to login to 'sa' account.. but i wouldn't think
> trying to login (every 5seconds or so) would result in me getting
> packets from that IP every .02 seconds...[/color]
[color=blue]
> What can I do after this? I guess i could block their ip via my cisco
> PIX firwall.. but is there anyway to automatically block a user that
> tries to login to 'sa'? From what I have read, SQL doesn't have a
> feature to block users based on their ip[/color]

Why do you have your SQL Server open to the Internet in the
first place? The application being used is what? If you have an
IIS Server hosting the application then you could simply have
your SQL Server port 1423 blocked.

Jul 23 '05 #9