Encrypting the actual MDF file - not the data column

Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FPConsider using Encrypting File System (EFS).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
Hi there,
We have a website application that we needed to replicae in various laptops
for our team that does not have internet access to our production site
constantly. We wrote the application that keeps in sync the website, the
database and the relevant data between the laptop and our production server.
In order to do that, we had to replicate the production database and
production "compiled" website on the local laptop, and have the applicaion
downloading and uploading the relevant data in order to keep the whole thing
in sync. Success !!!
Now comes the problem:
At this point we have a laptop that holds a production compiled dotfuscated
website (we are ok with that) and the production database with a minimum
subset of data. This means that the Database schema and the data is open to
anybody that has access to that laptop. So what happen if the laptop gets
stolen ?
The only way we can allow that is if we find a way to encrypt somehow the
MDF file.
Constraint:
We cannot change the production website and SP code to ENCRYPT - DECRYPT
certain data columns.
So my question is ..can I secure the DB file in case the laptop get stolen?
Thanks,
FP|||Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||You'd have SQL running under a domain account and that account would then be
writing files (data and log) on the disk. If you copy files to be attached
or restores, be sure to use that same domain account.
How you connect to SQL Server doesn't matter. The login that you use to
talk to SQL Server isn't the account that writes to those files. The SQL
Server service account is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
Tom,
With EFS, would my local application be able to connect and run queries
normally?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Consider using Encrypting File System (EFS).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:%23QK1780ZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hi there,
> We have a website application that we needed to replicae in various
> laptops
> for our team that does not have internet access to our production site
> constantly. We wrote the application that keeps in sync the website, the
> database and the relevant data between the laptop and our production
> server.
> In order to do that, we had to replicate the production database and
> production "compiled" website on the local laptop, and have the applicaion
> downloading and uploading the relevant data in order to keep the whole
> thing
> in sync. Success !!!
> Now comes the problem:
> At this point we have a laptop that holds a production compiled
> dotfuscated
> website (we are ok with that) and the production database with a minimum
> subset of data. This means that the Database schema and the data is open
> to
> anybody that has access to that laptop. So what happen if the laptop gets
> stolen ?
> The only way we can allow that is if we find a way to encrypt somehow the
> MDF file.
> Constraint:
> We cannot change the production website and SP code to ENCRYPT - DECRYPT
> certain data columns.
> So my question is ..can I secure the DB file in case the laptop get
> stolen?
> Thanks,
> FP
>
>|||You wouldn't encrypt it programmatically. It's done automatically.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:O5Il8p%23ZHHA.1300@.TK2MSFTNGP02.phx.gbl...
Also,
Since I do need to keep in sync the local DB and sometimes I need to "drop"
it and then recreate an empty one, would I be able to programmatically
encrypt the file using EFS?
If so, do you have a code sample (possibly in C#)?
Thanks again,
Filippo
"Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>|||Also have a look at:
http://technet.microsoft.com/en-us/.../aa906017.aspx, for a new
technology that would be helpful in this scenario.
Thanks
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23DmRu$MaHHA.4552@.TK2MSFTNGP05.phx.gbl...
> You'd have SQL running under a domain account and that account would then
> be
> writing files (data and log) on the disk. If you copy files to be
> attached
> or restores, be sure to use that same domain account.
> How you connect to SQL Server doesn't matter. The login that you use to
> talk to SQL Server isn't the account that writes to those files. The SQL
> Server service account is.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "Hey it's Filippo" <pandiani69@.hotmail.com> wrote in message
> news:uar7Yq1ZHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Tom,
> With EFS, would my local application be able to connect and run queries
> normally?
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eYxH5O1ZHHA.4000@.TK2MSFTNGP02.phx.gbl...
>