Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: database security
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 4:26am
tgeek0012.4K
Edited 23-Apr-13 6:17am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Comments
TechnoGeek001 at 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ő at 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.
TechnoGeek001 at 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ő at 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/-
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can encrypt your procedures using WITH ENCRYPTION:
 
CREATE PROCEDURE MyProcedure
WITH ENCRYPTION
AS
 
.....
This is NOT 100% secure, but it's better than nothing..
  Permalink  
Comments
TechnoGeek001 at 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.

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

  Print Answers RSS
0 OriginalGriff 7,800
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 23 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100