Encrypting, backing up, and restoring a SQL Server database with EFS

The Encrypting File System (EFS) is a feature of Microsoft™ Windows™ that lets you store information on your hard disk in an encrypted format. EFS enables transparent encryption and decryption of files by using advanced, standard cryptographic algorithms. Use this method to encrypt the database file if you have SQL Server Standard Edition.

Before you begin

This task assumes that you have:
  1. Chosen a service account for SQL server service that
    • remains available for the lifetime of the encrypted database and its backup.
    • can be used to transfer the database or its backup across the network, if needed.
    Note:
    • The service account can be the same or different than the one you use for AppScan® Enterprise.
    • Use one service account to log in to the SQL Server service and to encrypt any of the databases that are hosted through that service.
    • The SQL Server service account will be referred to as 'the service account' in these instructions.
  2. Located the filepath of the database, if different than the default locations listed here. You will need this information for step 3. You can find the default location by opening Microsoft™ SQL Server Management Studio. Right-click the SQL Server that hosts the database. Click Properties > Database settings > Database default locations.

About this task

This procedure must be completed before you run the configuration wizard; otherwise, you won't be able to access the database. See Configuring the SQL Server database for AppScan Enterprise.

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 Windows™ Explorer, right-click the folder where the database resides, and go to Properties > Security to give the service account Read and execute and read access to both the <databasename.mdf> file and the parent folder.
    Note: The credentials of the user that is logged in will be used to encrypt the database. If you are not logged in as the service account, do that now.
  4. Right-click the folder that contains the <databasename.mdf> file and go to Properties > General > Advanced Attributes. Select the Encrypt contents to secure data check box and click OK.
    Note:

    If the folder is not encrypted yet, select Apply changes to this folder, subfolders and files when prompted. If you select this option after you run the Server Configuration Wizard, then the database is not encrypted. If this process is applied to the database and the corresponding log file after the Server configuration wizard is run, then the database might get into a "Recovery Pending" state. Then, the encrypted database is not accessible in SQL Server Management tools and AppScan Enterprise.

  5. In the Services window, start the SQL Server that hosts the AppScan® Enterprise database.

Results

The DATA folder C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA (if defaults were used during Microsoft SQL Server 2014 Standard installation) will appear in green in Windows Explorer after it gets encrypted. Any databases that are added after this procedure are encrypted, including the AppScan Enterprise database created by the Server Configuration Wizard.

Note: Only the user who encrypted the file can decrypt it. You can determine who encrypted specific files in the Details section on the Properties > Advanced Attributes window. The backup of the encrypted database will NOT be encrypted automatically. Follow the steps in Backing up and restoring an EFS-encrypted database.

Backing up and restoring an EFS-encrypted database

You can move an encrypted backup database file to a network-shared location hosted on the same Windows™ version to preserve the file encryption. You can restore the database from any location where the encrypted database file is stored. When restoring into a SQL Server, that Server's service should be running with the service account credentials of the user who encrypted the database. However, a restored database file is NOT encrypted, so you must encrypt it using the steps in the above task.

Procedure

  1. In Windows™ Explorer, expand the folder where the database backup resides, and give the service account Read and execute and read access to the <databasename.bak> file.
    Note: The credentials of the user that is logged in will be used to encrypt the database. If you are not logged in as the service account, do that now.
  2. Right-click the <databasename.bak> file and go to Properties > General > Advanced > Encrypt contents to secure data, and click OK.