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
>

makeing a db backup for transport

I have a production machine and a development machine.. I need to have a
copy of the SQL database to follow me around with the laptop development
machine, what is the best wat to make a copy of the production db onto the
laptop system? just importaing data with EM or is there some process that is
easier? (the dev machine has MSDE on it instead of sql server 2000 like the
production one has)"Brian Henry" <brianiup@.adelphia.net> wrote in message
news:OMocDNnjDHA.3612@.TK2MSFTNGP11.phx.gbl...
> I have a production machine and a development machine.. I need to have a
> copy of the SQL database to follow me around with the laptop development
> machine, what is the best wat to make a copy of the production db onto the
> laptop system? just importaing data with EM or is there some process that
is
> easier? (the dev machine has MSDE on it instead of sql server 2000 like
the
> production one has)
There are a couple of ways to do this...
- you could take a BACKUP of the production database and RESTORE to your
laptop.
- you could use the sp_detach_db to temporarily detach the database from the
production server, copy the files to your laptop, then use sp_attach_db to
re-attach the database on your server and laptop (see BOL for more details
on the syntax).
Steve|||An easier way is to stop the SQL server for a moment copy the files to the
laptop and attach these files to the SQL server instance on the laptop.
Invoking the sp_detach_db is not required.
Majid
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:u5OlZbnjDHA.2444@.TK2MSFTNGP09.phx.gbl...
> "Brian Henry" <brianiup@.adelphia.net> wrote in message
> news:OMocDNnjDHA.3612@.TK2MSFTNGP11.phx.gbl...
> > I have a production machine and a development machine.. I need to have a
> > copy of the SQL database to follow me around with the laptop development
> > machine, what is the best wat to make a copy of the production db onto
the
> > laptop system? just importaing data with EM or is there some process
that
> is
> > easier? (the dev machine has MSDE on it instead of sql server 2000 like
> the
> > production one has)
> There are a couple of ways to do this...
> - you could take a BACKUP of the production database and RESTORE to your
> laptop.
> - you could use the sp_detach_db to temporarily detach the database from
the
> production server, copy the files to your laptop, then use sp_attach_db to
> re-attach the database on your server and laptop (see BOL for more details
> on the syntax).
> Steve
>|||> Invoking the sp_detach_db is not required.
Attach is only guaranteed if you actually detached first. It might work even if you just "picked up
the files", but if you check BOL you find that BOL explicitly say that you should detach first.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Majid" <majid@.dynatechsolution.com> wrote in message news:uqCUvKGkDHA.4008@.TK2MSFTNGP11.phx.gbl...
> An easier way is to stop the SQL server for a moment copy the files to the
> laptop and attach these files to the SQL server instance on the laptop.
> Invoking the sp_detach_db is not required.
> Majid
>
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:u5OlZbnjDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > "Brian Henry" <brianiup@.adelphia.net> wrote in message
> > news:OMocDNnjDHA.3612@.TK2MSFTNGP11.phx.gbl...
> > > I have a production machine and a development machine.. I need to have a
> > > copy of the SQL database to follow me around with the laptop development
> > > machine, what is the best wat to make a copy of the production db onto
> the
> > > laptop system? just importaing data with EM or is there some process
> that
> > is
> > > easier? (the dev machine has MSDE on it instead of sql server 2000 like
> > the
> > > production one has)
> >
> > There are a couple of ways to do this...
> >
> > - you could take a BACKUP of the production database and RESTORE to your
> > laptop.
> > - you could use the sp_detach_db to temporarily detach the database from
> the
> > production server, copy the files to your laptop, then use sp_attach_db to
> > re-attach the database on your server and laptop (see BOL for more details
> > on the syntax).
> >
> > Steve
> >
> >
>

Make changes to a production database!

Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.

Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.

I've no clue about what steps should i implement.

Please guide me!

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***The ideal solution is that you take the scripts which you used to make
the changes in test from your source control system and simply run them
against the production database. For a large number of .sql files (or a
large number of target servers/databases), this can be automated using
osql.exe in batch files, or perhaps a script in your preferred
scripting language.

However, if you made the changes in some non-repeatable way (such as
using Enterprise Manager), then you would need another solution. One
possibility is to use a database compare tool of some sort to generate
a script for you, but you might still need to make other site-specific
changes such as updating data or creating new logins. So whatever you
do for now, it would be a good idea to plan on working with scripts in
a source control system in the future.

Simon|||Build a TSQL script to perform the upgrade and then test it out.

If you didn't save your change scripts when you did your own upgrade
then you'll have to re-create them (and remember to save the scripts
next time!). Enterprise Manager can help with this (right-click the
database, select All Tasks > Generate Script).

To script the data you can use this proc:
http://vyaskn.tripod.com/code.htm#inserts

or, if the data is particularly large you may find it easier to dump it
to a file and use BCP to perform the load.

It may help to drop or disable some constraints and indexes for the
duration of the upgrade - that's assuming the relevant tables aren't in
use at the time.

Also, you may want to check out some of the diff and synchronization
utlilities, such as:
http://www.red-gate.com/sql/summary.htm

Hope this helps.

--
David Portas
SQL Server MVP
--|||debian mojo (debian_mojo@.yahoo.com) writes:
> Hello faculties,
> We've a production database that is being used by one of our clients
> as a backend for his website. The database size is around 1GB.
> Recently we added some tables to our local database which is a
> replica of the production database. Now we need to apply the same
> changes at the production one aslo.
> I've no clue about what steps should i implement.

In addition to what Simon and David said, I just like to underscore
that whatever you do, first run your upgrade scripts in a test environment.
Don't deploy until production, until everything is working OK in test.

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

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

Make a copy of SQL Server database

I'm trying to make a copy of our 2005 SQL Server production database (25 meg) so we can install it in a new office being set up in another state. I have right clicked on the database I want to copy, selected Tasks, then option Copy Database... I'm trying to put the copy on the same server, and I am doing this work signed into the Management Studio as "sa" The wizard creates 5 steps, the first 4 run ok, then the 5th step runs for 10 to 20 seconds, then quits. The message says to look in the log on the server for the reason it quit. The only log I can find is the Error Logs at the bottom of the Object Explorer window. I look into the current log, then select the SQL Server log, the current one. There is only one line in here about the copy I just tried, and it only says "Starting up database XXXXXXXX" which is the database I'm trying to create through the copy process. Within Database Mail, SQL Agent, and Windows NT log lines, I can not find any reference to the copy job that just bombed.

Needless to say, I'm VERY new to all of this SQL Server admin stuff. If someone could point me in the right direction, it would really help!

Thanks!make a backup and restore it to your new server.|||Make a copy_only backup. Documentation (http://msdn2.microsoft.com/en-us/library/ms191495.aspx) is available at MSDN.

Maintenence plan backup schedule doesnt work

I am running the latest MSDE with the SQL Server 2000 client tools onWindows 2K Server on my production server. I have a simliar setup on mydevelopment machine except it is running Windows 2K (non-server). Ihave the same issue on both machines (I am wondering if it is relatedto the structure of the database). When I open Enterprise Manager,connect to the server, navigate to the Management folder, and add a newmanagement plan, I experience the following error.
I create a management plan that includes nothing but a completedatabase backup (i.e. no reorganization of index pages, logging, etc.).I set the backup schedule to occur every Sun at 12:00AM and hit OK.Sometimes I get the following error :"Error 8114: Error converting datatype int to tinyint" sometimes not. Either way, though, the same thinghappens, the schedule for the backup is not saved. If I reopen themanagement plan and go to the "Complete Backup" tab, there is noschedule in the schedule box.
I have found this link:
http://www.technologyone.org/new-4581847-3733.html
Which appears to be the same issue that I am having, however, there isno resolution there. I was wondering if anyone had any ideas.
Jason
just a quick dirty question: can you perform a normal backup? Is there a job for the backup? If so will this force run?|||Yes, I can perform manual backups without a problem.And, in fact, I can successfully create a maintenence plan withschedules for things like "Index pages" etc. And these schedules worksuccessfully. However, I am only interested in making a maintenenceplan for complete backups on a schedule and that does not work. Oneworkaround that would probably work is to just use the SQL ServerBackup dialog and set a schedule in there. However, this does not allowme the flexibility I require to manage the backups, such as saving thefiles with their dates appended and triaging the backups into seperatefolders for each database. It also does not allow any extensibility fordown the line when I would like to automate the cleaning andoptimization tasks as well as logging.Finally, I think I should alsomention, that I do have SQL Server Agent running, because this isrequired for the schedules to work correctly.
Jason
|||Have you thought of writing back-up scripts for these processes. Thesecould then be controlled by DTS or if you really have to xp_cmdshell?
|||I have thought about it, but have decided that my efforts are betterspent getting the maintenence plan working for several reasons. Firstoff, all I need is a basic backup and I do not require the flexibilitythat custom scripts would provide (nor do I need the overhead ofmaintaining said scripts). Lastly, the maintenance plan SHOULD work, Ijust have to figure out why it is not working rather than determining aworkaround.
Jason
|||

ok, just is case you do want a script here's one you could use.
--Declare variables

declare @.BACKUP varchar (100)
declare @.day varchar(50) -- date dd/mm/yyyy
declare @.hour varchar (4) -- hour hh
declare @.copy varchar (200) -- copy statement to where ever you want

--set day This is used for the day FULL backup
set @.day = convert(varchar(50), getdate(), 103)
set @.day =replace (@.day, '/', '_')

--set hour This is used for the LOG transaction backups Not needed for full backup
--set @.hour = datepart(hour, getdate())
--set the name of the backup
set @.BACKUP = location of backup i.e. 'E:\data\MSSQL\BACKUP\Northwind\Northwind_'+@.day+'.bak'
--backup database SQL
backup database Northwind todisk=@.BACKUP with stats
--copy the file to where
set @.copy = 'copy'+@.BACKUP+ location'\\servername\drive$\folder\