How Can I Lock Down my distributed SQL or SQL Express Application?
As a commercial developer looking to distribute your SQL 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
- Copying your application database to another instance of SQL and attaching it
However, when you install SQL 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 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
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.
Overview of Process
These are the steps to be used. They are not necessarily in strict order. For example, you can install SQL and limit Sysadmin rights prior to installing Encryptionizer. All of these steps can be automated in an installation script.
- Install Encryptionizer files and registry entries.
- Install instance of SQL or SQL Express if not already present.
- Limit Sysadmin access by customizing secure_sa.sql to your needs (note 2):
- Remove all Logins (except SA) out of the Sysadmin role.
- Change the SA password
- Remove permissions that allow control of the server
- Add a login that will access the application database (note 1)
- Add a login that is in the SecurityAdmin role (note 1)
- Add a login that will be used to backup / restore databases (note 1)
- Encrypt the Master database via API or CLI with key#1.
- Encrypt your application database via API or CLI with key#2. (If your application database comes encrypted in your installation package, make sure it is encrypted with key#2, i.e., a different key than is used to encrypt the Master database.
- Build a security profile via API or CLI specifying:
- key1 and key2
- that the Master database must be encrypted or SQL will not start
- the login of the SecurityAdmin (note 1)
- new databases and backups should be automatically encrypted with key #2
- Secure the instance via API or CLI.
- Reboot computer/server
- Start SQL or SQL Express logging in as the application user and attach the application database.
Note 1: These logins can all be the same login, alternatively you can use SA. We recommend using SQL Logins rather than Windows Logins since you will have no control over the customer’s Users and Groups.
Note 2: Thanks to Michael Hlavinka for his help in constructing the script.
How it Works
When you start SQL, before starting SQL, 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.