Encryption in MDF file possible?

I am working on a distributed application that will use a SQL Express 2005 MDF file for the primary application data storage. The program will be storing sensitive data and I would like to encrypt the data in it.

I have searched through the forums and cannot find any reference to how to enable encryption for an MDF file.

I thought about writing my own encrypt / decrypt functions, however, I'm using databound controls and do not have a home-grown data access layer that I can tap into to implement the encryption.

When I originally added the MDF file to my application, under the "Advanced" settings, I saw that I could switch an "Encryption" property to "True"... figuring that would do the trick. However, when the MDF file is created I get an error stating, "The instance of SQL Server you attempted to connect to does not support encryption".

What is the trick for encrypting data in an MDF file that is being used with databound controls? Remember that this will be a distributed app - so if there are any customizations that are required on the SQL Express side to support this - I will need to find some way to configure SQL Express on the target machines during my bootstrap install of it.

Any help would be greatly appreciated! Thanks!

I think you actually want to encrypt the data and not the file. Here is a link to a starting point:

http://msdn2.microsoft.com/en-us/library/ms190357.aspx

|||

Yes - that is what I meant... encrypting *data* in the MDF file.

As I wrote in my original post, I'm using data-bound controls, so I do not have a data layer that have created to pass data through - it is all being handled by the magic of Microsoft. That being said, I'm not sure how to implement the information contained in the link you provided. That seemed in line with me writing my own encryption functions and passing the data through them during read / writing to the database - which isn't an option for me using databound controls. Or is it?

|||

I've never tried using the the SQL Server 2005 encryption with databound controls but it should work as long as you can edit the query. You basically extend the query to include encryption and decryption information.

The pointer I sent you is just one of several BTW

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...
>

Encrypting mdf files

Hi,

We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.

Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?

You can access 'service account's cert store through mmc -

Quote:

Originally Posted by

>Certificates Snap-in -Service account

Thanks,
rsm
---rsm (prakandapandit@.yahoo.com) writes:

Quote:

Originally Posted by

We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.
>
Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?

No.

If you are using SQL 2005, there are encryption routines builtin,
so that you encrypt some columns. Keep in mind that encrypting key
columns will have a very serious impact on performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 16 Feb, 04:16, "rsm" <prakandapan...@.yahoo.comwrote:

Quote:

Originally Posted by

Hi,
>
We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.
>
Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?
>

No. Assuming you are using SQL Server 2005 you should read the
encryption topics in Books Online.

It is in principle possible to encrypt every bit of user data in a
database, but I can't think of any good reasons for wanting to do that
- and there are many good reasons why NOT to do it. Could you explain
a bit more about your requirements.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||We are using SQL Server 2000.

We are trying to provide an encryption solution for SQL Server
database. ldf and mdf files are some thing we want to encrypt.

Problem is that if we encrypt using user cert, we need to run SQL
Server service as that user which works fine until user changes his
password. In this case, we have to some how automatically change SQL
Server service 'run as' user password. I was wondering if there is a
way to install user cert as service cert so SQL Server can decrypt the
ldf files on its own.|||"rsm" <prakandapandit@.yahoo.comwrote in message
news:1172172849.993451.142190@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

We are using SQL Server 2000.
>
We are trying to provide an encryption solution for SQL Server
database. ldf and mdf files are some thing we want to encrypt.
>
Problem is that if we encrypt using user cert, we need to run SQL
Server service as that user which works fine until user changes his
password. In this case, we have to some how automatically change SQL
Server service 'run as' user password. I was wondering if there is a
way to install user cert as service cert so SQL Server can decrypt the
ldf files on its own.
>

There is no built-in encryption in SQL 2000, so I'm 99% sure the answer is
no.

Simple answer; the user SQL Server runs under shouldn't be changing its
password often and when it does, should go through a normal change
procedure.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Encription for MDF files

Dear Friends
I want to use the encryption for the MDF files so that no one can just copy
the data and attach the MDF files to access the database.
When i have done the same the database is in Suspect Mode. Please suggest
what can be the reason.
I also have the following questions.
1. Can another user restore the database backup for the encrypted MDF files.
Thanks in advance.
Best regards
ShaileshI don't believe SQL Server supports encrypted files. If you don't want
someone to get ahold of your files then set the security on the box so that
only the people who need access have it. As for backups you should look at
SQL LiteSpeed from www.imceda.com . It allows you to encrypt the backup on
the fly.
Andrew J. Kelly SQL MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:9667DEDF-C6BD-4817-BC5E-88941ABE10D2@.microsoft.com...
> Dear Friends
> I want to use the encryption for the MDF files so that no one can just
> copy
> the data and attach the MDF files to access the database.
> When i have done the same the database is in Suspect Mode. Please suggest
> what can be the reason.
> I also have the following questions.
> 1. Can another user restore the database backup for the encrypted MDF
> files.
> Thanks in advance.
> Best regards
> Shailesh|||Hi
Did you encrypt the file/directory when logged in as the SQL Server Service
Account? If not, you need to as the encryption/decryption can only be done b
y
one account.
Just be aware, if you change the user's password from Computer Manager / AD
User Manager, the encryption certificate becomes invalid and you loose total
access to the data on the encrypted file/directory.
If you do a SQL Server BACKUP, the resultant backup can be restored by
someone else, as long as it is not encrypted.
Copying the encrypted file to a different location, will result in it being
decrypted.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sharad2005" wrote:

> Dear Friends
> I want to use the encryption for the MDF files so that no one can just cop
y
> the data and attach the MDF files to access the database.
> When i have done the same the database is in Suspect Mode. Please suggest
> what can be the reason.
> I also have the following questions.
> 1. Can another user restore the database backup for the encrypted MDF file
s.
> Thanks in advance.
> Best regards
> Shailesh|||Hi,
You can use the Encrypted File System Support on Windows 2000
Windows 2000 support encrypted file system property.
Below are the steps encrypt the data files:
1) Logon with the SQL Server startup account
2) Stop SQL Server and sql agent service
3) Right click the data files, select properties, click Advance button,
check the "Encrypt contents to secure data"
4) Start the SQL Server service
See the below KB for more information:-
HOW TO: Encrypt Data Using EFS in Windows 2000
http://support.microsoft.com/defaul...kb;en-us;230520
Note:
If you change the SQL Server startup accout you have to redo the same,
otherwise SQL Server service will not start.
"With EFS, database files are encrypted under the identity of the account
running SQL Server. Only this account can decrypt the files. If you need to
change the account that runs SQL Server, you should first decrypt the files
under the old account, then re-encrypt them under the new account."
Thanks
Hari
SQL Server MVP
"Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
news:9667DEDF-C6BD-4817-BC5E-88941ABE10D2@.microsoft.com...
> Dear Friends
> I want to use the encryption for the MDF files so that no one can just
> copy
> the data and attach the MDF files to access the database.
> When i have done the same the database is in Suspect Mode. Please suggest
> what can be the reason.
> I also have the following questions.
> 1. Can another user restore the database backup for the encrypted MDF
> files.
> Thanks in advance.
> Best regards
> Shailesh|||Respectfully, EFS by itself is not a complete solution.
If the box is stolen - including the EFS recovery keys, then you are no
better off as the system administrator password can be hacked, the filestore
accessed and viola!
If you want to protect against this, then research the syskey command and
also read up about EFS recovery agents and make sure you know the full in's
and out's of it as otherwise it could cost you all your data. It is common
for people to lose data by losing EFS keys.
If some of your data is critical - e.g. columns containing say credit card
numbers, then encrypt it before it is stored and decrypt it when retrieving.
It is easy to code using .Net. This is not an easy solution tho' as you also
have to protect the keys used for encryption and decryption here. If you
want to store some type of passwords - use hashing instead - it is a one way
process. Research SHA1, SHA256 etc.
HTH
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ut72iu6kFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Hi,
> You can use the Encrypted File System Support on Windows 2000
> Windows 2000 support encrypted file system property.
> Below are the steps encrypt the data files:
> 1) Logon with the SQL Server startup account
> 2) Stop SQL Server and sql agent service
> 3) Right click the data files, select properties, click Advance button,
> check the "Encrypt contents to secure data"
> 4) Start the SQL Server service
> See the below KB for more information:-
>
> HOW TO: Encrypt Data Using EFS in Windows 2000
> http://support.microsoft.com/defaul...kb;en-us;230520
> Note:
> If you change the SQL Server startup accout you have to redo the same,
> otherwise SQL Server service will not start.
>
> "With EFS, database files are encrypted under the identity of the account
> running SQL Server. Only this account can decrypt the files. If you need
> to
> change the account that runs SQL Server, you should first decrypt the
> files under the old account, then re-encrypt them under the new account."
> --
> Thanks
> Hari
> SQL Server MVP
>
> "Sharad2005" <niitmalad@.yahoo.co.uk> wrote in message
> news:9667DEDF-C6BD-4817-BC5E-88941ABE10D2@.microsoft.com...
>

Emptying Log file

I have a database with mdf size 10 gb and ldf size 8 gb. i want to
reduce the size of ldf file how can i do it.
Regards,
Farid.
*** Sent via Developersdex http://www.examnotes.net ***You can shrink transaction log size.
1. DBCC ShrinkDatabase
2. Dbcc ShrinkFile --log
3. Auto shrink option
"Ghulam Farid"?? ??? ??:

>
> I have a database with mdf size 10 gb and ldf size 8 gb. i want to
> reduce the size of ldf file how can i do it.
> Regards,
> Farid.
> *** Sent via Developersdex http://www.examnotes.net ***
>