Problem using sp_attach_db with an encypted file system

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?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?|||Hari,
Thanks for that, I'm now running the service MSSQLSERVER under my user
name and it works fine.
The realisation that only user who encrypted the files, can decrypt
them (and hence services running under system context cannot) solves
an off-topic problem I was having an ASP.Net application returning the
error "Failed to execute request because the App-Domain could not be
created. Error: 0x80070005 Access is denied." when it attempts to load
an encrypted aspx page.
Thanks,
Stephen
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:<ujGc6p6bEHA.2880@.TK2MSFTNGP12.phx.gbl>...
> 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?