Secure Microsoft SQL Database file with password

[abdulladarwish]

Hello.

I want to ask for a question.

I have a software which uses MS SQL database for storing data, i have to deploy this software different machines for different clients.

The question is how can i protect my database so that no one even the administrator of the server or the machine administrator can view my database structure or the data.

The only one can view database a connect to it is my software, so can i protect my database file with some technique to achieve this issue, also i want my database to be protected from being reattached to different instance other than my installed instance.

can i protect the database file like Microsoft Access Database file ? Please help me

Thanks

[Rabbit]

Encrypt your database file. Here is Microsoft's documentation on encryption in SQL Server: http://technet.microsoft.com/en-us/l.../bb510663.aspx.

On a side note, Access is one of the least secure databases out there. You should not want to make SQL Server as secure as Access, that would be taking steps backwards.

[NeoPa]

Abdulla:
can i protect the database file like Microsoft Access Database file ?

Many of the approaches Access uses are also available in SQL Server, but SQL Server has much more scope than Access, and where security is implemented it is both strong and reliable. You need to ask the question more clearly if you don't want to be misunderstood. If there's a particular aspect of Access security you're interested in then you need to include that in your question.

Rabbit has already provided a link to a page that tells you how to encrypt the database. This is logically equivalent to a password-protected Access database.

[abdulladarwish]

@Rabbit Thanks for reply .. but i want to ask you if the TDE feature available in SQL Server 2008 Express Edition ?

[Rabbit]

SQL Server 2008 Express does not have TDE. But it does have traditional encrytpion.

[NeoPa]

The OP has asked the same question again, but adjusted specifically for SQL Server Express Edition (which I've deleted as it's just a clarification of this question).

@Rabbit,
From your last post I would guess there is nothing available similar to TDE, but that standard encryption is available. Would you mind posting to clarify the position for the OP. I imagine that while it could protect the data, it might still allow schema data to be visible to anyone who has access to the server directly (so, even without the encryption password), but I'm rusty with SQL Server.

[Rabbit]

@NeoPa, your assessment is correct.

Technically, you can use any encryption method you want to encrypt the entire file and decrypt when it needs to be used.

The problem with that method and with TDE is that since you are distributing the database with the program, the key must be stored in or with the program.