deploy SQL Server Database with a Winforms app

Question

I have created a SQL DB-based winforms application, and I want to deploy it on a client machine. The program is a single user desktop application.

Opinions in this post suggest it's better to use SQLite or SQL Server Compact Edition in such scenarios. However, I prefer to use stored procedures, which are not supported in those products.

My question is about security concerns:

• If I use an Access database, I can set a password for the database so at least non experienced users cannot view database directly.
• How can I deploy a Winforms app to install SQL Server 2005 Express Edition and set password for my .mdf database file?
• What installers support this request? clickonce, windows installer, installshield, etc?

Answer 1

You may want to browse this technet article discussing SQL Server 2005 Express deployment with custom applications.

Answer 2

The .mdf isn't executing as an .mdb is. In other words, you won't need to configure your .mdf file, with a password, as SQL Server will handle the security for authentication and authorization.

Access won't help much with your desire to stick with stored procedures, unfortunately.

Suggest that there are too many potential issues with deploying SQL Server Express alongside your app. It may be the case that the value of stored procs, in this case, may not be enough to warrant the hassle of installing SQL Server Express.

Evaluate the work being done inside those stored procs. Are they CRUD only, or much more complex. What is your reason for stored procs in this single-user app?

Consider re-architecting your application to:

• use parameterized queries. Move the body of the stored procs into a constant string.
• SQL Server Compact Edition

Balance the time-to-refactor with the potential customer-support issues!

Please consider the blogpost by Steve Lasker (Stored Procedures & SQL Server Compact-The Great Debate). It's got some great insight on assessing whether stored procs are the right tool for the job in regards to client applications.

Some Updates:

It sounds like you are trying to avoid that .mdf getting out of your hands. You CAN take some measures to lock down the data within - encryption of the data, but there isn't a 100% solution to avoid your .mdf being taken elsewhere. As you comment, once it's in the customer's hands, it's out of yours. :)