white papers

How Can I “Lock Down” my distributed SQL Server / SQL Express Application?

As a commercial developer looking to distribute your SQL Server based or SQL Express based application, you would like to provide your users with the ability to encrypt their data that is held in your application. In addition, you want to prevent users from accidentally (or maliciously):

  • Issuing ad-hoc queries through SQL Management Studio, Enterprise Manager, or other query tools.
  • Backing up the database and restoring to an unsecured instance of SQL Server or SQL Express
  • Copying your application database to another instance of SQL Server or SQL Express and attaching it

However, when you install SQL Server or SQL Express on the customer’s computer, that customer automatically inherits certain roles and permissions that allow them unfettered access to your data. For example, anyone who is a Windows Administrator on the local machine will automatically inherit Sysadmin rights to the SQL instance. In addition, there are other permissions that will allow them full access to your database, even if not in the Sysadmin role, including “control server” or “view any database”. Even if you have removed these permissions and roles from your database, the less-than-honest user can simply attach your database to another instance of SQL Server or SQL Express and have full rights again.

Lastly, if SQL Server is started in Single User Mode, anyone logging in with Windows Authentication automatically has full Sysadmin rights on the system, regardless of how you have adjusted permissions.

In this article, we present a method, using Encryptionizer, where you can “lock down” your SQL database so that you can, as completely as possible, protect your intellectual property.

Here are the exploits we will try to guard against:

  • End User logged in as Sysadmin on instance.
  • End User being granted control over server, e.g., with CONTROL_SERVER permission
  • End User starting SQL in Single-User mode and thus having effective Sysadmin rights
  • End User backing up your database and restoring unencrypted database on another machine
  • End User copying your database to another instance of SQL Server where they have Sysadmin rights

Prerequisites

The only prerequisites for this technique are:

  • You must use SQL Authentication Mode or Mixed Mode (SQL and Windows).
  • You must assign an SA Password to the instance. In addition, the SA Password should be unknown to anyone who is not supposed to have access to your database.

Script Based Installer

You likely won’t be present to deploy via point-and-click on a customer machine. Or you may have hundreds or even thousands of machines to install on. You can create a one click installer by following the directions that come with your script-based installer. Simply create an XML script that:

  • Specifies at least two encryption keys (encrypted with a special utility).
  • Encrypts Master Database with Key1
  • Encrypts application database(s) with Key2
  • Secures SQL instance with both keys
  • Optionally changes SA password
  • …some additional script options …

How it Works

When you start SQL Server or SQL Exress, Encryptionizer makes the following checks:

  • Is Master database encrypted?
    • If not encrypted, do not start SQL
  • Has anyone been placed back in the Sysadmin Role or granted server-wide permissions?
    • If yes, make the application database appear Suspect.
  • Has SQL been started in Single User Mode?
    • If yes, make the application database appear Suspect
  • Has the application database been copied to another instance or server?
    • Encrypted database will appear Suspect
  • Has someone tried to restore the application database to another instance or server?
    • Encrypted backup will be inaccessible

As you can see, if any of the exploits we are trying to protect against have been used, either SQL will not start, or your application database will be Suspect and inaccessible.

Top