SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Encryption on database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mng
Yak Posting Veteran

59 Posts

Posted - 09/30/2009 :  05:05:26  Show Profile  Reply with Quote
Hello,

Is it possible to encrypt whole database(all tables data). if so how can we do the same.

Is it correct way encryoting whole DB? any degrade in performance? any drawbacks doing this?

can some one answer my questions helps me a lot.

thanks in advance

rammohan
Posting Yak Master

India
212 Posts

Posted - 09/30/2009 :  05:21:37  Show Profile  Visit rammohan's Homepage  Send rammohan an AOL message  Reply with Quote
The Database Engine for SQL Server Compact 3.5 lets you encrypt databases. Databases that are encrypted are also protected with a database password.
1.Because encrypted databases can only be accessed with a password, if the password for an encrypted database is lost, the data is unrecoverable.
2. You can specify the type of encryption used to encrypt the database by setting the connection string attribute, Encryption Mode, to one of the available encryption modes: Platform Default, Engine Default, or PPC2003 Compatibility.

1. encrypting data base:

You can create encrypted databases by supplying both encryption and password properties when the database is created. Encrypted databases can be created by the following methods:

* Using SQL syntax
To create an encrypted database by using SQL syntax, specify both database_password and the ENCRYPTION ON option. For example:

Create Database "secure.sdf" databasepassword '<password>' encryption on

* Using the .NET Compact Framework Data Provider
To create a password-protected database by using the SqlCeEngine.CreateDatabase method, you must specify the password property in the connection string. For example:

"data source=\secure.sdf;password=<enterStrongPasswordHere>;encrypt database=TRUE"

For more information, see the System.Data.SqlServerCe.SqlCeEngine class in the Microsoft Visual Studio documentation.
* Using OLE DB
To create an encrypted database by using the OLE DB provider for SQL Server Compact 3.5, you must pass the provider-specific property DBPROP_SSCE_ENCRYPTDATABASE as VARIANT_TRUE and specify a password by using the provider-specific property DBPROP_SSCE_DBPASSWORD.

2. accessing encrypted database:
A password must be supplied to open an encrypted database. Encrypted databases can be accessed by the following methods:

* Using the data provider for SQL Server Compact 3.5
To access a password-protected database by using the SqlCeConnection.Open method, you must specify the password property in the connection string. For example:

"data source=ssce.sdf; password=<enterStrongPasswordHere>"

For more information, see the System.Data.SqlServerCe.SqlCeConnection class in the Visual Studio documentation.
* Using OLE DB
To open a password-protected database in OLE DB, you must specify the provider-specific property DBPROP_SSCE_DBPASSWORD.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Go to Top of Page

Mng
Yak Posting Veteran

59 Posts

Posted - 09/30/2009 :  05:39:52  Show Profile  Reply with Quote
Thank you RamMohan. Can we able to convert the existing database(approx size 20 GB) to encrypted one in simple way. if so what's the simple way to convert it.

And also once i encrypt the database, is it necessary to decrypt always for each query using in my .net page to retrive data?

quote:
Originally posted by rammohan

The Database Engine for SQL Server Compact 3.5 lets you encrypt databases. Databases that are encrypted are also protected with a database password.
1.Because encrypted databases can only be accessed with a password, if the password for an encrypted database is lost, the data is unrecoverable.
2. You can specify the type of encryption used to encrypt the database by setting the connection string attribute, Encryption Mode, to one of the available encryption modes: Platform Default, Engine Default, or PPC2003 Compatibility.

1. encrypting data base:

You can create encrypted databases by supplying both encryption and password properties when the database is created. Encrypted databases can be created by the following methods:

* Using SQL syntax
To create an encrypted database by using SQL syntax, specify both database_password and the ENCRYPTION ON option. For example:

Create Database "secure.sdf" databasepassword '<password>' encryption on

* Using the .NET Compact Framework Data Provider
To create a password-protected database by using the SqlCeEngine.CreateDatabase method, you must specify the password property in the connection string. For example:

"data source=\secure.sdf;password=<enterStrongPasswordHere>;encrypt database=TRUE"

For more information, see the System.Data.SqlServerCe.SqlCeEngine class in the Microsoft Visual Studio documentation.
* Using OLE DB
To create an encrypted database by using the OLE DB provider for SQL Server Compact 3.5, you must pass the provider-specific property DBPROP_SSCE_ENCRYPTDATABASE as VARIANT_TRUE and specify a password by using the provider-specific property DBPROP_SSCE_DBPASSWORD.

2. accessing encrypted database:
A password must be supplied to open an encrypted database. Encrypted databases can be accessed by the following methods:

* Using the data provider for SQL Server Compact 3.5
To access a password-protected database by using the SqlCeConnection.Open method, you must specify the password property in the connection string. For example:

"data source=ssce.sdf; password=<enterStrongPasswordHere>"

For more information, see the System.Data.SqlServerCe.SqlCeConnection class in the Visual Studio documentation.
* Using OLE DB
To open a password-protected database in OLE DB, you must specify the provider-specific property DBPROP_SSCE_DBPASSWORD.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN



Go to Top of Page

rammohan
Posting Yak Master

India
212 Posts

Posted - 09/30/2009 :  06:34:06  Show Profile  Visit rammohan's Homepage  Send rammohan an AOL message  Reply with Quote
You can encrypt an unencrypted database by specifying attributes on the connection URL when you boot the database. The attributes that you specify depend on how you want the database encrypted.

* If the database is configured with log archival, you must disable log archival and perform a shutdown before you can encrypt the database.
* If there are any global transaction that are in the prepared state after recovery, the database cannot be encrypted.

When you encrypt an existing, unencrypted database, you can specify whether the database should be encrypted using a boot password or an external encryption key. You can also specify the encryptionProvider attribute and the encryptionAlgorithm attribute on the connection URL. The database is configure with the specified encryption attributes and all of the existing data in the database is encrypted.

Encrypting a database is a time consuming process because it involves encrypting all of the existing data in the database. If the process is interrupted before completion, all the changes are rolled back the next time that the database is booted. If the interruption occurs immediately after the database is encrypted but before the connection is returned to the application, you might not be able to boot the database without the boot password or external encryption key. In these rare circumstances, you should try to boot the database with the boot password or the external encryption key.
Recommendation: Ensure that you have enough free disk space before you encrypt a database. In addition to the disk space required for the current size of the database, temporary disk space is required to store the old version of the data to restore the database back to it's original state if the encryption is interrupted or returns errors. All of the temporary disk space is released back to the operating system after the database is encrypted.

To encrypt an existing unencrypted database:
Specify the dataEncryption=true attribute and either the encryptionKey attribute or the bootPassword attribute in a URL and boot the database. For example, to encrypt the salesdb database with the boot password abc1234xyz, specify the following attributes in the URL:

jdbc:derby:salesdb;dataEncryption=true;bootPassword=abc1234xyz

If authentication and SQL authorization are both enabled, the credentials of the database owner must be supplied as well, since encryption is a restricted operation.

If you disabled log archival before you encrypted the database, create a new backup of the database after the database is encrypted.


One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Go to Top of Page

rammohan
Posting Yak Master

India
212 Posts

Posted - 09/30/2009 :  08:30:47  Show Profile  Visit rammohan's Homepage  Send rammohan an AOL message  Reply with Quote
http://aspnet.4guysfromrolla.com/articles/022107-1.aspx

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000