Detaching, encrypting, and attaching a database encrypted with EFS

There might be times when you do not want to stop the SQL Server service during database encryption; for example, when there are several databases running on that service and you do not want them to be unavailable. You can detach, encrypt, and attach the database instead.

Before you begin

'The service account' must be used to log in to the SQL Server service and to encrypt any other databases on the same SQL Server.

Procedure

  1. Go to Start > Administrative Tools > Services and stop the SQL Server service that hosts the AppScan® Enterprise database you are going to encrypt. The default service is SQL Server (MSSQLSERVER).
  2. Right-click the name of the service to open the properties dialog. On the Log on tab, select This account, enter the credentials of the service account, and then click OK.
  3. In the Services window, start the SQL Server that hosts the AppScan® Enterprise database.
  4. In Windows Explorer, right-click the <databasename.mdf> file and go to Properties > General > Advanced > Encrypt contents to secure data, and click OK.
  5. Open Microsoft SQL Server Management Studio and connect to the SQL Server that serves that database.
  6. Under the 'Databases' tree, right-click the database you want to encrypt and click Tasks > Detach.
  7. In the Detach Database window, if there are open connections, select the Drop Connections check box and click OK.
  8. In Windows Explorer, right-click the <databasename.mdf> file and go to Properties > General > Advanced > Encrypt contents to secure data, and click OK.
  9. Repeat Steps 3 and 4 for the <databasename.ldf> file.
  10. In Microsoft SQL Server Management Studio, right-click the Databases tree, and choose Attach.
  11. In the Attach Databases window, click Add and navigate to the encrypted <databasename.mdf> file. Select it and click OK > OK
  12. Repeat Step 11 for the <databasename.ldf> file.