Malicious attack or SQL Command?

Hi,
Yesterday here we had an incident on one of our production servers
where a few tables were explicitly dropped and recreated. I knew this
because of the object create date and the table permissions had
disappeared. This could have been a malicious attack or some process,
however my hunch is with the former.
Since the SQL Security here is going through a complete rethink (after
the horse has bolted!), my question would be other than explicitly
dropping and recreating a table is there a SQL Command/Process that
does this? Also for future reference (in case this sort of thing
happens again) I would like to setup server-side tracing, however I
have noticed that the trace doesn't pick up a users/machines IP
Address. I can see that in the SQL Error Logs use Network Address but
can a trace explicitly pick up an IP? I know SQL Server can block
certain IPs but can it log them?
Any other ideas for preventing this sort of thing would be most
welcome. Funny, you never think about security until you *REALLY* have
to. A lesson to be learned here.
Rgds,
qhMost likely this was a person with legitimate access to the SQL Server using
Enterprise Manager (AKA Enterprise Mangler) to change a table. Sometimes EM
drops and recreates tables "under the covers" to accomplish a task that does
not have a corresponding direct T-SQL command. This can expand to multiple
tables when Referential Integrity constraints are involved. Most of the
time this doesn't cause a problem, but if the system is high volume or the
tables are large, it gac get very ugly, very quickly. Personally, I avoid
EM to do production table changes. I prefer to script everything and deploy
to a test/QA system first.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1123148974.573160.310190@.g47g2000cwa.googlegroups.com...
> Hi,
> Yesterday here we had an incident on one of our production servers
> where a few tables were explicitly dropped and recreated. I knew this
> because of the object create date and the table permissions had
> disappeared. This could have been a malicious attack or some process,
> however my hunch is with the former.
> Since the SQL Security here is going through a complete rethink (after
> the horse has bolted!), my question would be other than explicitly
> dropping and recreating a table is there a SQL Command/Process that
> does this? Also for future reference (in case this sort of thing
> happens again) I would like to setup server-side tracing, however I
> have noticed that the trace doesn't pick up a users/machines IP
> Address. I can see that in the SQL Error Logs use Network Address but
> can a trace explicitly pick up an IP? I know SQL Server can block
> certain IPs but can it log them?
> Any other ideas for preventing this sort of thing would be most
> welcome. Funny, you never think about security until you *REALLY* have
> to. A lesson to be learned here.
>
> Rgds,
> qh
>

Making replication more reliable

We have about 90 sites doing merge replication with pull subscriptions over
a DSL VPN.
Replication often stalls following a dropped connection. Normally,
restarting SQL Server Agent at the subsciber gets things moving again.
Any good suggestions on a way to a perform this restart. We can't really do
it from within our user application as they don't have sufficient privilege.
Any easy way to do it from the publisher site?
Also are we making things worse for ourselves by running the merge agent
continuously?
Tony Toker
Data Identic Ltd.
either schedule your agents to run every 10 minutes, or set up your job so
that on job failure it wraps around and starts job step 1 again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xyzzy@.identic.co.uk> wrote in message
news:cgi1vo$p1d$1$830fa795@.news.demon.co.uk...
> We have about 90 sites doing merge replication with pull subscriptions
over
> a DSL VPN.
> Replication often stalls following a dropped connection. Normally,
> restarting SQL Server Agent at the subsciber gets things moving again.
> Any good suggestions on a way to a perform this restart. We can't really
do
> it from within our user application as they don't have sufficient
privilege.
> Any easy way to do it from the publisher site?
> Also are we making things worse for ourselves by running the merge agent
> continuously?
> Tony Toker
> Data Identic Ltd.
>
|||Thanks for the tip.
Some of the agents don't actually fail, but run indefinitely.
I'll look at checking connectivity then running replmerge, or the activex
controls (which aren't currently deployed) from our application to synch
when necessary.
This will be a stupid question whatever the answer but would replmerge work
with a push subscription, ie can you initiate synchronization of a push
subscription at the subscriber?
Thanks for all your help on here, you need to get that book out and start
earning for your advice!
Tony
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uZzHfRqiEHA.3428@.TK2MSFTNGP11.phx.gbl...
> either schedule your agents to run every 10 minutes, or set up your job so
> that on job failure it wraps around and starts job step 1 again.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tony Toker" <xyzzy@.identic.co.uk> wrote in message
> news:cgi1vo$p1d$1$830fa795@.news.demon.co.uk...
> over
> do
> privilege.
>