SQL Express 2005, Secure Database File

Question

Hi, I have built an application that uses SQL Express 2005 and I want to deploy it, but the problem is that anyone who has SQL Management Studio(or something like that) installed on his computer, can connect to my database and change it. Is there anyway to prevent end-users from seeing or editing the content of my database, even those with the Administrator privileges?

Answer 1

I suppose this is not the answer you hoped for but it is unavoidable that anyone with administrative rights will be able to browse and edit information in the database.

You cannot change this.

Perhaps you could consider WHY you want to prevent this kind of access?

I expect you want to prevent tampering or accidental corruption of the data?

If that is the reason then you can go a long way to prevent corruption by adding constraints to your database. Add foreign key constraints, uniqueness constraints, everything that will help prevent data corruption.

Then an administrator would need to deliberately undo these constraints before they tamper, which would prevent most accidental corruption but of course not prevent malicious vandalism.

If you are concerned about protection of your intellectual property, you could encrypt the programmatic components (stored procedures and views and functions). This kind of encryption is not very strong but prevents casual inspection.

Encryption is added by using the

WITH ENCRYPTION

clause in your CREATE or ALTER statements.

Answer 2

I guess you're referring to the fact that by default SQL Server installs allow Windows and SQL Server Authentication. If you remove the Windows Authentication mode then users will have to connect via SQL Server authentication.

This from http://support.microsoft.com/kb/247931

SQL Server authentication

SQL Server authentication relies on the internal user list maintained by the SQL Server computer. This list does not include Windows NT users, and is specific to the SQL Server computer. Users are created and configured using the SQL Server Enterprise Manager. To use this authentication method, perform with the following steps:

• If you connect through Open Database Connectivity (ODBC), in the ODBC Administrator, choose SQL Server authentication when you configure the data source.

•In the ActiveX Data Objects (ADO) connection string, include the parameters "UID" and "PWD" when you use ODBC, and "User ID" and "Password" when you use the SQLOLEDB provider.