white papers

How can I “Lock Down” my distributed SQL database application?

In this article, we present a method using Encryptionizer where you can “lock down” your distributed SQL database to protect your data and your intellectual property.

Here are the exploits we guard against:

  • Being logged in as SysAdmin on the the instance
  • Granted control over server, e.g. with CONTROL_SERVER permission
  • Starting SQL in Single-User mode and thus having effective SysAdmin rights
  • Backing up your application database and restoring to another machine
  • Copying your application database to another SQL instance on the same machine or another where they have SysAdmin rights

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 distributed databases, 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 distributed 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.

If any of the exploits we are trying to protect against have been used, either SQL will not start, or your application distributed database will be inaccessible.

Securing Distributed SQL 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 securing Distributed SQL Databases Works

When you start SQL Server or SQL Express, 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

For more information about our NetLib® Encryptionizer® for data encryption view our case studies and additional white papers or request a fully functional evaluation today!