Securing the MS SQL Server 2005 database on client side

Question

Hello!

I developed database driven .NET application and I need to deploy it. I faced a problem, which is "how to protect my database against direct access". I use MS SQL Server 2005 Express Edition as a DBMS and appropriate database.

I want to make possible to manipulate with data in my database only through my client application.

1. How do I define SA password and instance name in silent mode of MS SQL 2005 EE installation with Mixed type of Authentication?

1.1. Can I change SA password after the installation?

2. If my database be attached to my new instance... Is it possible to copy my database, attach it to another instance and get a direct access to its objects?

Is there solution that make impossible to connect to my database on third-partie's side Idirectly, without using the client application?

I appriciate for a help.

Answer 1

To some extent what you can/should do to restrict direct access depends on you application and deployment requirements, and what type of direct access you are trying to prevent.  For instance, if you are sole administrator of all machines that the app will be deployed on, you may be able to restrict access by locking down the SA password and dissallowing access to the DB files via the filesystem.  If, on the other hand, you are not the admin, it will be pretty much impossible to completely block direct access against a determined admin.  The databases can always be copied via the filesystem to another machine with a useable SA account.

Even if you are not the admin of the machine, there are some things you can do to avoid casual access outside of the app itself.  Here is an article that talks about securing your connection string: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/THCMCh14.asp

You can also use an application role for server-side permissions.  See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_89ir.asp

Answer 2

Dear Alazel,

I want to make impossible to retrieve data from tables inside deployed MS SQL database in different way than through my .NET client application.

In ideal, I want create user credentials for my database on my site and than deploy it with client application to third parties. It is always possible with MS Access, but it is also easy to restore  database password and another credentials for MS Access database using cheap tools. This because MS Access is not suitable and I try to find solution with MS SQL Server 2005 EE. I also know that I can encrypt and decrypt data in my database tables "on-fly", but it will decrease a performance of my client application and i want to avoid using the cryptography.

Answer 3

This is a topic that came up pretty often. See, for example, this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1.

What you are looking for is a DRM (digital rights management) type of solution.

SQL Server does not provide a DRM solution. Such a solution cannot be built without hardware support and even hardware support doesn't guarantee that a solution would be unbreakable. You can search the Internet for DRM in conjunction with words such as crack, hack, broken, to get an idea of the current state of the art of DRM technologies 

So, your options are:

(a) - rely on your software licensing rules. In USA you have the DMCA law as well. You should consult a lawyer to analyze what you can do on this front.

(b) - use some DRM solution and rely on the fact that few people will be interested in breaking your protection scheme. If people will be interested, they'll most likely break it and they will distribute their solution as well, so you can't rely on people not knowing how to break it - you would have to assume that they don't know how to search the Internet either. The option of using encryption as a custom DRM scheme would fall under this category as well.

Thanks
Laurentiu

Answer 4

Hi, Laurentiu Cristofor!

Really constructive and helpful answer!

By the way, what do you think about one post  on a thread that you recommended to me. That post was about some triks. Here is a quotation of it:

"Ok, exist a way doing properly what you want, but is compliacted, and not automated.

Follow this steps:

1. Create a Windows User with Administrative rights.
2. Log on with that user
2. Create a folder for the mdf file.
3. Remove rights from that folder for all other Windows Users excpet the user you just created.
4. Encrypt that folder with windows encryption (owner will be only the user you are logged on)
5. Go to Services and change MSSQL service user to the user just created
6. Copy MDF to the encrypted folder and attach database
7. Log off

Of course dont forget to remove Administrator Login from SQL Server and add the windows user you created. And don't leave sa pass blank...

In this mode even administrator can't access that folder. Changing password from User Management for that user is not possible, because in that mode all encrypted folders are lost. "

Answer 5

The problem with this solution is that you have to implement it in the setup program for your application. The solution will break at step 1: what password do you specify for that user? The password will have to be hardcoded in your application, which you ship, which means that whoever runs setup to install it can also look to see what password you've set. Then the whole scheme unravels.

You can use this scheme if you're an administrator and want to protect your data against the other users or administrators. But you can't use this as a solution for protecting data that you ship with your applications, which I thought is what you are trying to accomplish.

This will only work if you're going to be the manually installing the application on customer machines.

Thanks
Laurentiu

Answer 6

I'll make another attempt to explain the DRM problem.

What is the situation today? We have a content provider that wants to sell content but limit its use somehow. We have a content consumer that wants to use the content. The provider packages his content in an application and sells it to the consumer who installs it on his PC. Today, the consumer has full control over his PC machine, so if the application accesses the content, the consumer can retrieve that content too. The provider can try to prevent this by obfuscating the content and forcing the consumer to reverse-engineer the application and break the law in the process of recovering the content. But if the consumer ignores the law and is determined and knowledgeable, he can recover the content. Note that the law is the strongest protection here. The reason why the consumer can break the application is because he has full control over his machine. The only way the consumer can be prevented is if his control over his machine is restricted, but that is not possible with the PC as we know it. The important thing to note here is the balance between the consumer's power over his machine and the provider's power over his content. If you increase one, you must limit the other, you can't work around this.

So, the solutions for this issue today are as I suggested before: rely on the law and additionaly maybe try to obfuscate your content somehow to discourage attempts at retrieving the content you ship. As far as SQL Server is concerned, you can either attempt to use encryption to obfuscate your data or you can implement your own obfuscation scheme.

Thanks
Laurentiu

Answer 7

I want to expand a previous statement I made:

This will only work if you're going to be the manually installing the application on customer machines.

This should be read as:

This will only work if you're going to be manually installing the application on customer machines - for your own use, not for the use of the customers.

Thanks
Laurentiu

Answer 8

Hi,

If you want to secure your SP and Functions in database then it can be encrypted in DLL form so no one can cahnge it. This feature is only available with SQL Server 2005.

It is possible but right now I don't have link for the reference as soon as I found it I will post it

Answer 9

I don't fully agree. Reverse engineering is possible over the application also, but this doesn't mean that we must provide source code and rely only on law protection.

Some time ago i tried to create an installer for this process, and was not so difficult. The only problem is that sometimes 2 restarts are needed. (Depends if SQL starts after install, because sometimes an extra restart is needed).

Complicating the way password is stored inside application is also easy.

But i was affraid from another point of view. PERFORMANCE.

I already read some testings, and i think in busy databases this is not a good idea.

Check here http://www.microsoft.com/australia/resources/aprilsig.ppt

Microsoft claims 5% impact on performance. Anyway my advice is: don't use this method on busy servers.

Answer 10

Just to clarify: I am not saying that you should only rely on law protection, I am just saying that additional protections through various applications, products, schemes, tricks, etc. are not impossible to break. My first post was made in response to Alexander's request:

"I want to make impossible to retrieve data from tables inside deployed MS SQL database in different way than through my .NET client application."

(The highlight of "impossible" is mine)

This request cannot be satisfied today. If we replace "impossible" with "hard", there are various solutions that can be implemented to discourage someone from retrieving protected content. We have locks on doors not because they're unbreakable, but because they would discourage most attempts of entering our homes.

Thanks
Laurentiu

 

Answer 11

As far as I started to deal with software protection only a few weeks ago, I just discovered the most efficient way to protect the data inside a SQL Server 2005 database. It can be as following:

1. Database has to have DMK (database master key) with password and affected by DROP ENCRYPTION BY SERVICE MASTER KEY clause to overrride Automatic Key Management, and, at least, Certificates with expiration dates also have to be in place.

2. Application roles (Alazel mentioned about them in this Thread) have to be granted to use those Certificates (or symmetric or asymmetric keys) to encrypt/decrypt some pieces of data (not all of them, because encrypted data can be stored only in variations of string or binary format)

3. Passwords for Certificates have to be something like Hash-functions derived from some interesting values like information about application roles' passwords from system tables

In this case the only weak chain in the whole database driven software product is a client windows application - in the part where it calculates application roles' passwords based on "passwords" inputed by user.  Any other passwords for Certificates and Keys can be calculated automatically, using system information inside the database.

Finally, to protect client application, it is nessecary to use some tricks inside the client application and, of course, the best obfuscation software.

What do you think folks?

Answer 12

Laurentiu Cristofor wrote:

The problem with this solution is that you have to implement it in the setup program for your application. The solution will break at step 1: what password do you specify for that user? The password will have to be hardcoded in your application, which you ship, which means that whoever runs setup to install it can also look to see what password you've set. Then the whole scheme unravels. You can use this scheme if you're an administrator and want to protect your data against the other users or administrators. But you can't use this as a solution for protecting data that you ship with your applications, which I thought is what you are trying to accomplish. This will only work if you're going to be the manually installing the application on customer machines. Thanks Laurentiu

I also do not prefer such "solutions", because they look "non technological". And also: http://www.elcomsoft.com/aefsdr.html

I just wanted to emphasize, that such solution can be effective enough only with administrator in addition

Answer 13

If you want to use encryption, consider the following:

(a) You can protect symmetric keys directly using a password, so it is not necessary to use certificates or remove the service master key protection for the database master key.

(b) Expiration dates for certificates play no role for encryption. Service Broker is currently the only component that checks for certificate expiration.

(c) The data that you ship with your application will have to be already encrypted, which means you must have already generated an encryption key.

I think your proposal has not considered (c). I mentioned (a) and (b) to point out that the scheme can be simplified as far as the number of encryption keys involved - only the symmetric keys for encryption should be considered. Additional encryptions leading to a password encryption don't improve the strength of the protection scheme.

Here's my analysis of a straightforware encryption based protection scheme: You can use a single encryption key to protect all your data. Because the key is stored in the database, it will have to have some password protection, so the password will have to be generated before you create the distribution CD. This password will have to be obfuscated in memory. When your application runs, it will have to reconstruct the password, connect to the database, and open the symmetric key for use.

Weaknesses of this solution are that all your CDs will ship with the same hardcoded password and that someone can debug your application to find out what password you use to open the key. You'll need to attempt to make your application hard to debug.

You can reset the password for the encryption key at installation time, but on the CD you'll still have a database containing a symmetric key encrypted with the factory password. To prevent attacks on the CD database, you may want to use an additional obfuscation algorithm with its own obfuscation key, so that you provide attackers with an additional problem.

These are some thoughts. The main idea is that you can try to pile as many problems as you feel comfortable that they would discourage a potential attacker.

Thanks
Laurentiu

Answer 14

Laurentiu Cristofor wrote:

(b) Expiration dates for certificates play no role for encryption. Service Broker is currently the only component that checks for certificate expiration

... which is really sad.

Now I see a small note at http://msdn2.microsoft.com/en-us/library/ms187798.aspx

Note:
Built-in functions for encryption and signing do not check the expiration dates of certificates. Users of these functions must decide when to check certificate expiration. So, in a such situation like I am now I need to protect my T-SQL code from being debuged by someone. How it is possible if I'll deploy my database to someone's computer?

Answer 15

I assumed that your application is compiled, not just a set of T-SQL procedures. If you only ship a set of T-SQL procedures within a database, I don't think you can do much to protect your code and data. You should describe in more detail what are the characteristics of your application.

As for the certificate expiration, I'm not sure how that would impact your solution. We don't check certificate expiration in SQL Server because certificates are currently used as encryption key containers. If they would be used for authentication, it would make sense to check for their expiration.

Thanks
Laurentiu

Answer 16

Dear Laurentiu Cristofor,

My solution consists of two main parts: database itself and client shell for viewing that database. This solution also has a feature: customer can not connect to the internet at all. So, I need to deploy the database as well.

I'm talking about database separately because I also need to protect it. As soon as it become available on customers site, it can be affected by third parties through a direct access to the data. Hence, I need to avoid such situation as much as possible.

About certificate expiration - I was thinking about it like about very convenient automatic feature. My client part will has a special mechamism to regulate accessability of it limited by date and users' credentials. And I want almost the same abilities inside the database. As far as I understood, now I need to perform certificate expiration checks inside T-SQL stored procedures? How do I do it?

Answer 17

If you have a client shell, you can put the code to obfuscate the encryption password in it. Or is your client shell just a set of TSQL procedures? I'm not sure I understood your concern about debugging TSQL procedures. Regarding code obfuscation, whether or not TSQL procedures can be debugged is less important than the fact that the code of the procedures can be read by an attacker.

For verifying certificate expiration, you can check the sys.certificates catalog. The expiry_date field is the one you want to look at. Note that this should be interpreted as a GMT date.

Thanks
Laurentiu

Answer 18

Laurentiu Cristofor wrote:

If you have a client shell, you can put the code to obfuscate the encryption password in it. Or is your client shell just a set of TSQL procedures? I'm not sure I understood your concern about debugging TSQL procedures. Regarding code obfuscation, whether or not TSQL procedures can be debugged is less important than the fact that the code of the procedures can be read by an attacker.

Client shell is written in C#. This is Windows application.

About debugging ... As far as I know, an attacker (which could be system administrator or DBA in my case) can attach debugger to SQL Server process to see what happens even inside  encrypted stored procedures in run-time. So, if I will call stored procedure or UDF with password as a parameter from the obfuscated client windows application, how can I hide passwords that I need to use to decrypt the data inside that stored procedures or UDF?

Answer 19

Yes, a debugger can be attached either to the C# application or to the SQL Server process, to attempt to retrieve the password. There is no way to prevent this, so you can just rely on the fact that few customers would know how to do this. This is why these solutions are not impossible to break.

Thanks
Laurentiu

Answer 20

Thank you!

I appreciated your help. :)

Answer 21

Hi guys,

Did you get a solution for this? I mean is still impossible to secure a database at client? Is there any third party who does this. Please advice

Regards,

Answer 22

A year later... any body has found a solution?

Answer 23

I would recommend reading the thread I include below where we discussed this topic in detail.

  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=775364&SiteID=1

 

  I hope this information helps.

 

  -Raul Garcia

   SDE/T

   SQL Server Engine