Hi
I wanted to see if Microsoft was going to address this problem
We as developers want to hide our database design (SQL Express DB)
We dont want to rely on the user rights settings to secure the design
Most of our clients have admin access which means that they can see the db design
We are happy for the design to be available on the server as change control should be given only on the server
I am perplexed how this hasnt been addressed
Your reply is appreciated
T
Hi T,
It may be perplexing to you, but this is a complex problem that was not part of the original design goals for SQL Server, or any server based DBMS really. Clearly the need you describe is becoming more important to many customers, and it is something that is being investigated for future versions of SQL Server.
In the mean time, you might want to consider SQL Server Compact Edition for your applications that require local data storage. SQL CE uses a password to protect the file directly. This allows you to embed the user name and password directly into your compiled application so user access to both the data and meta-data of your database are only allowed through your application.
SQL CE has some limitations: It only supports a subset of data types, it does not have support any programability (SProcs), it supports a subset of the standard T-SQL syntax and it only has the one user name and password, so everyone has the same level of access. SQL CE is also not suitable for multi-user applications. Many of these limitations can be overcome by using code logic in your application.
It is a trade-off, but SQL CE is a great light weight database that may be the right choice for you. You can find more information on MSDN and you should check out the SQL CE forum as well.
Mike
|||Hi Mike
Thanks for that great answer
As long as we can setup replication as in SQL Express and manipulate the database
with a similar Management tool then it is a good solution and answer
How long do you think it will be before SQL Express is modified
Thanks
Touraj
|||I don't really have a timeframe, sorry.
As far as replication, yes, SQL CE supports synchronizing data with a central SQL Server the same way SQL Express does. Starting with SP2 you will be able to manage SQL CE database using SQL Management Studio, the same tool used for SQL Server.
Mike
|||Hello Touraj,
perhaps the Application Role is an option to do that? Then users have no permissions in the database. They only can log on. Only the application role has permissions to read and write the tables. (I don't know, perhaps it must be an own SQL Server instance for your application.)
But this does not prevent the users to copy the .mdf file and attach it to another SQL Server. So they can see all again. Or they can even open the .mdf file with notepad and see the table definitions with column names and the table data.
I asked for an optional obfuscation feature for SQL Server Express databases here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1094887&SiteID=1
When this feature would be implemented in such a way that an obfuscated database only can be attached when the encryption key of the obfuscation is provided in the attach command, then his attach by anyone could be prevented.
Regards Markus
