Is it possible to prevent databases from being copied?

Hi,

We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end.

Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data.

Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies.

Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication.

Is this possible?

Regards,

Sigol.

I'm assuming you meant 'steal' (to take), rather than 'steel' (to harden).

There are several issues.

1. As you noted, a backup 'could' be restored on another server. Various third party backup programs allow passwords and encryption for backups. Any SQL Admin can create a backup that can be restored elsewhere.
2. A SQL administrator could take the database 'offline' for a few minutes and copy the data file (*.mdf)
3. A local administrator could shut down the SQL Service for a few minutes and copy the data file (*.mdf).
4. Even with an Encrypted database, or tables, or even specific columns, a local SQL Administrator can usually get around the protections.

Don't allow any local administrators to be in the SQL Admins role.

So if you are concerned about protecting a database, the 'best' solutions, in a situation where you can't control the local administrators, is to look into database encryption using encryption keys, or better yet, certificates.

|||

Thank you for your comments, Arnie. This was very helpful to me.

Regards,

Sigol.

|||

Arnie Rowland wrote:

I'm assuming you meant 'steal' (to take), rather than 'steel' (to harden).

Having a grammatically bad day, Arnie!

sql

Is it possible to append and sum together?

Hi there,

I was wondering if this is possible:

I need to update 'customers sales' in a database - can this be done so that when I enter the customers new sale to the database (via update page) it will add it to their current sales, giving them a new sales total.??

Angie.Originally posted by blondini3
Hi there,

I was wondering if this is possible:

I need to update 'customers sales' in a database - can this be done so that when I enter the customers new sale to the database (via update page) it will add it to their current sales, giving them a new sales total.??

Angie.
You could do that, using a database trigger. The syntax of a database trigger varies by DBMS.

However, I wouldn't do that. Don't store the sales total at all. When you want to see a sales total, just SUM up the individual sales. You could have a view that does this for you, if you like:

CREATE VIEW customer_total AS
SELECT cust.cust_id, SUM(sales.value) tot_value
FROM cust, sales
WHERE cust.cust_id = sales.cust_id
GROUP BY cust.cust_id;

You are probably concerned that calculating the total sales "on demand" like this is bad for performance. But really, it is what databases are good at. Only very exceptionally is it necessary to store summed values - only in very big, high-access databases. And the downside of doing so is data integrity - i.e. you have to do a lot of work to ensure that the stored total is accurate (e.g. if a sale is deleted, updated).|||This works, thanks...BUT......when I go to addanother column from the Merlot table either before or after the SUM column, i get a ERROR MESSAGE saying 'column' not part of aggregate function !!!

Can you only select two columns ? I want it to display the CardRecordID, LastName, FirstName from Merlot Table, and total sales from Sales Table...but i can't get it to work...

SELECT Merlot.CardRecordID, SUM(sales.sales_value)
FROM Merlot, sales
WHERE Merlot.CardRecordID = sales.CardRecordID
GROUP BY Merlot.CardRecordID

thanks...|||It should be straight forward only..

select Merlot.CardID, Merlot.firstname,Merlot.lastname, sum(sales.values)
from Merlot,sales
where.....etc etc

thats what i always do and works..mebbe its some syntax error like a comma or something..check it up..
also sometiems the "group by" attribute causes problems so first jus see your output without grouping and then try again...

And im a beginner at this too so dun count on my words..jus tryin to extend some help:)

-s|||Thanks for your reply....yeah, it should be straight forward allright, but it comes up STILL with an aggregate function error ?!!!!

when i run this:

SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, sum(Sales.Sales_Value)
FROM Merlot,Sales
WHERE Merlot.CardRecordID = Sales.CardRecordID

I get an error message saying 'cardrecordID' not part of an aggregate function.

BUT......ALTHOUGH THIS ONE BELOW WORKS...it doesn't total up the sales for each recordID - it displays each cardID's sales individually...

SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, Sales.Sales_Value
FROM Merlot,Sales
WHERE Merlot.CardRecordID = Sales.CardRecordID

it won't let me add any more than two fields in the SELECT statment or it gives me the aggregate error....because when I run this one below it totals the sales fine

SELECT merlot.cardrecordid, sum(sales.sales_value)
FROM merlot, sales
WHERE merlot.cardrecordID = sales.cardrecordid

how do I get it to total the sales for each cardID AND ALSO show more than two fields ??

I'm using dreamweaver MX to create the queries.

angie...|||SELECT Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName, sum(Sales.Sales_Value)
FROM Merlot,Sales
WHERE Merlot.CardRecordID = Sales.CardRecordID
group by Merlot.CardRecordID, Merlot.FirstName, Merlot.LastName

Hope this will help u.|||THANK YOU THANK YOU THANK YOU KUMAR AP,,,,,,you are the best..........the best...............did i mention you're the greatest???.............i will always, always, remember to put the other fields in the GROUP BY statement from now on...

thankyou

Angie.|||Hi,
Whether your problem solved ?.. or else yet to be?.|||yep...it's all working now...thanks heaps.
Angie.

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
Thanks
Yogish
|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"

> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.

> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!
|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
Thanks
Yogish|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"

> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.

> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish

Is backing up an MDF safe?

Heard a frightening thing: apparently a number of our customers do
backups by just copying the MDF and LDF and backing those up...on a
live database. My hunch is that that could well result in a backup
that's not usable. But I'm told "oh, people have been doing this for
years!"
Is this a sane way to do 'backups' at all? I know that it could be a
reasonable way if a server is shut down and/or the files are detached.
But live?
Any links to information discussing this would be appreciated--if its
dangerous, I'd like to be able to point some of my people to it and
point out the dangers.
Thanks!> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
No, that is definitely frightening, and I'd love to see what happens when
you ask them to demonstrate the whole process. (a) you can't copy an MDF
file if it is active, and (b) if the MDF is not detached properly, there is
little chance that you will be able to attach it.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mike,
One of my basic question is, how are they able to copy .mdf and .ldf files,
when the database is active? It gives sharing violation error, as the files
are used by the database.
Normal practice is to take full backup during off-peak hours if the db is
not huge. And if they want to copy data files and log files, they should
detach the files from sql server instance by applying sp_detach_db. Copy it
to a different folder and then take the file backup.
If they have been doing it for years, I guess they are either taking SQL
server offline or detaching the datafile and logfile, before taking the file
backup.
To confirm, ask them if they are able to restore the database from the
backup they have taken.
--
Thanks
Yogish|||"Mike Jones" <barker_djb@.yahoo.com> wrote in message
news:eaObDxO%23EHA.1292@.TK2MSFTNGP10.phx.gbl...
> Heard a frightening thing: apparently a number of our customers do
> backups by just copying the MDF and LDF and backing those up...on a
> live database. My hunch is that that could well result in a backup
> that's not usable. But I'm told "oh, people have been doing this for
> years!"
"And how many practice restores have you done?"
> Is this a sane way to do 'backups' at all? I know that it could be a
> reasonable way if a server is shut down and/or the files are detached.
> But live?
Some backup agents will backup SQL Server "live". If they're using one of
those they're probably ok.
But otherwise I'd worry.
> Any links to information discussing this would be appreciated--if its
> dangerous, I'd like to be able to point some of my people to it and
> point out the dangers.
> Thanks!|||The problem with detaching a database is that it becomes unavailable. This
is unacceptable in an environment where you want 24x7 availability. The
best option (in my opinion) is to backup to disk using the Transact-SQL
BACKUP command. It is easy to restore a database backed up using this
method via the RESTORE statement. Whatever method people use to backup
their data they should know how to restore/recover the data. It is
important to test the restore process. Test it on a separate server and see
if you can indeed restore the database.
--
Keith
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:C210C859-E155-44F1-83DA-6A62440594B8@.microsoft.com...
> Hi Mike,
> One of my basic question is, how are they able to copy .mdf and .ldf
files,
> when the database is active? It gives sharing violation error, as the
files
> are used by the database.
> Normal practice is to take full backup during off-peak hours if the db is
> not huge. And if they want to copy data files and log files, they should
> detach the files from sql server instance by applying sp_detach_db. Copy
it
> to a different folder and then take the file backup.
> If they have been doing it for years, I guess they are either taking SQL
> server offline or detaching the datafile and logfile, before taking the
file
> backup.
> To confirm, ask them if they are able to restore the database from the
> backup they have taken.
> --
> Thanks
> Yogish