Re: Problem using sp_attach_db with an encypted file system

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 10:40:44 +0530

Hi

Are you using "Windows 2000 Encrypted File System option". If yes then you
have to follow this way.

1. Uncheck the File encryption
2. Attach the database using SP_ATTACH_DB
3. Stop SQL Server service
4. Login as the user SQL server service starts
5. Select the properties of the folder(s) in which the database files reside
using Windows Explorer.

6. Select the advanced option button and follow the prompts to encrypt the
files/folders.

7. Change the service startup account to he user you logged in (Control
panel -- services - mSSQL Server -- logon option)
7. Re-start the SQL Server service.
8. Verify the successful start-up of the instance and databases affected via
the encryption (or create databases after the fact over the encrypted
directories).

-- By any chance if you change the service startup account the database will
not start.

See the below link:-

http://www.sql-server-performance.com/ck_database_encryption.asp

Thanks
Hari
MCDBA

"Stephen Miller" <jsausten@hotmail.com> wrote in message
news:cdb404de.0407212013.acd74aa@posting.google.com...
> I want to run a copy of our Sql2000 production database on my WinXP
> laptop for development. Because this database contains sensitive
> information and the laptop cannot be physically secured, I have
> enabled File Encryption on the project directory to protect the
> database in the event that someone steals the laptop. To set up a
> development environment I installed Sql2000 personal edition, and
> disconnected the production database with:
>
> EXEC sp_detach_db @dbname ='myDB'
>
> I then copied the mdf and log files to the encrypted directory on the
> laptop and attempted to attach with:
>
> EXEC sp_attach_db @dbname = N'myDB',
> @filename1 = N'C:\Projects\Data.mdf',
> @filename2 = N'C:\Projects\Log.ldf'
>
> This failed with the error message: "Device activation error. The
> physical file name 'C:\Projects\Data.mdf' may be incorrect". This was
> freaking me out, because the exact same command worked fine on the
> production server to reattach the database. After some trial and
> error, I removed file encryption on the mdf and ldf file and the
> database attached without any problem.
>
> So my questions is, is this a known problem and is it possible to have
> file encryption on an SQL database?