How to secure database by looking database objects.

Question

0.00/5 (No votes)

See more:

I have one application which is created in WPF. In that application it is require to setup database in that PC.

So I have created setup such that it check at time of installation MS SQL install or not. If installed then create database and execute table and store procedure script so it will setup database.

now what my concern is about security, How we can secure my code of database script and table. because it is on client PC so I don't want anybody can see database logic.

so can anybody help to work around this stuff.

Thanks in advance.

Posted 23-Apr-13 3:26am

npdev13

Updated 23-Apr-13 5:17am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2013-04-23T03:56:00

Solution 2

You can not really hide data, and structure if you deploy database to the client. SQL Server has encryption features[^] from Enterprise edition on, but I doubt you will deploy such thing to the users. With any other edition, the database file is transportable, thus attaching it to an other instance where the user has administrator privileges will grant full access. But in general even this is unnecessary.
You can however encrypt database of SQL CE on file system level using EFS[^] (see: http://blogs.msdn.com/b/stevelasker/archive/2008/05/14/security-and-encryption-for-sql-server-compact.aspx[^]) or the new built-in features: http://msdn.microsoft.com/en-us/library/ms171955.aspx[^].

All approaches have their limitation.

This is also a really useful, but complex literature in this topic: http://msdn.microsoft.com/en-us/library/cc837966(v=sql.100).aspx[^]

Posted 23-Apr-13 3:56am

Zoltán Zörgő

Comments

npdev13 23-Apr-13 10:54am

Thanks Zoltan for taking time.

one more thing want to add the database will be large so I can't use the SQL CE and other database tool like Access which provide the database file. On the database very complex logic written in it is so storeprocedure is the best way to do this. That's why I have choose SQL Server database tool.

Is compact edition provide large data management ?

Zoltán Zörgő 23-Apr-13 13:24pm

If you require both large database files (above 4GB and 10GB respectively)and stored procedures neither CE or Express is good for you (see: http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx).
There are other (free) RDBMS implementations that can be used as local database, that do not have such size limits, have some sort of encryption and provide you .net support - but probably without stored procedures. Please note, that in such cases stored procedures will bring you little or no advantage at all - you can easily replace them with Linq to Entities based managed methods in you Business Logic layer. Why? Besides the fact, that you can make a real managed BL - because your stored procedures would run on the same machine (maybe even without any compilation). So you consume the same resources, not like with real client/server situations when your sp runs on the server.
The best practice you can currently follow is to choose a proper (embedded, lightweight) RDBMS, use the database only as data store, build an EF model on top of it and a complete BL on top of the model.
I suggest you look around here: https://en.wikipedia.org/wiki/Entity_Framework#Entity_Framework_ADO.NET_providers and find a proper embedded edition database management system with EF support, and probably some chyper too. Please not, that the filesystem (EFS) based encryption will work with most of them.

npdev13 24-Apr-13 0:47am

Thanks for valuable comment.I will give 5 start for this comment because it us useful to me.

but I am thinking If database size not increase more then 10 GB then can I use the local DB (”Denali”)?

IF we use localdb as database file then In the localDB can we prevent the database file by some password to connect it so to local db we can connect only through application not from external appliation.

so does this make sense what I am thinking?

Thanks,

Zoltán Zörgő 24-Apr-13 2:13am

LocalDB is stand-alone application, thus it is not bound to your application. The newly introduced self-contained security model can help you. Please see here: http://www.sqlserver-training.com/what-is-contained-database-in-sql-server/-

StianSandberg · Answer 2 · 2013-04-23T03:33:00

Solution 1

You can encrypt your procedures using WITH ENCRYPTION:

SQL

CREATE PROCEDURE MyProcedure
WITH ENCRYPTION
AS

.....

This is NOT 100% secure, but it's better than nothing..

Posted 23-Apr-13 3:33am

StianSandberg

Comments

npdev13 23-Apr-13 9:57am

Thanks for you quick reply. Yes I have this idea in my mind to secure the storeprocedure this way. but I have doubt to secure other objects like table and functions.