How to protect the sql server 2005 MDF file

How to set the Password for sql server 2005 MDF file.

Becoz i want to give the trail package to the client,package including the MDF.

After installing the package, the MDF will be placed in C drive, user data will store in MDF file through the application. but not allow to attach that MDF file using sql server in that system.

回答1:

If someone is sysadmin of an instance, one will be able to attach the mdf regardless of what you do. If you want to protect data from sysadmin, there are some tricks possible but it is rather hard. If you want to prevent most of the people (with exception of owner of database and sysadmin) from connection to database, implement role based security, where you can define what roles can and cannot do with data in database. You can also look at application roles, they may be useful for you.

回答2:

You can encrypt data held in a column using EncryptByPassPhrase.

See Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005

Transparent Data Encyption was only introduced in SQL Server 2008:

In SQL Server 2008 (Enterprise Edition only), a new form of database encryption has been introduced: Transparent Data Encryption (TDE), which includes these major features:

•Encrypts the Entire Database: With essentially a flip of a switch, the entire contents of MDF files, LDF files, snapshots, tempdb, and backups are encrypted. Encryption occurs in real-time as data is written from memory to disk, and decryption occurs when data is read from disk and moved into memory. Encryption is done at the database level, so you can choose to encrypt as few or as many databases as you want. The major benefit of encrypting a database with TDE is that if a database or backup is stolen, it can’t be attached or restored to another server without the original encryption certificate and master key. This prevents those nasty situations you hear about in the news where a backup of a database has been shipped from one location to another and is “lost,” which potentially exposes a company to liability issues.

•Easy to Implement and Administer: As its name implies, Transparent Data Encryption is transparent to applications. This means that your applications, and database schema, don’t have to be modified to take advantage of TDE. In addition, initial setup and key management is simple and requires little ongoing maintenance.

•Uses Minimal Server Resources to Encrypt Data: While additional CPU resources are required to implement TDE, overall, it offers much better performance that column-level encryption. The performance hit averages only about 3-5%, according to Microsoft.