4

I have developed a C# 4.0 desktop application that uses a SQL Server Express 2005 database. I have built a Setup and Deployment (msi) package that installs the application and all it's depenedencies, including the mdb database file, into a working folder under the Program Files directory.

The connection string is like this:

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|MyDB.mdf;Database=MyDB;integrated security=true;user instance=true;

The first time a database call is made, the database gets connected to the local SQL Server Express instance.

The installer works as expected on XP computers, however, when tested on Windows 7 machines an exception is thrown the first time a database call is made, which states that there are insufficient permissions on the folder containing the mdb file.

Between Windows XP and Windows 7, windows seems to have locked down permissions on Program Files subfolders. I can solve the problem by setting full permissions to the installation directory, but that seems like cheating.

So my question is this: How should I be configuring the setup and deployment package for this application? Am I doing it right? Do I just need to grant full permissions to all users on the applications directory? If so, how do I achieve this with a VS2010 setup and deployment package? Or should I place the mdb file somehwere else? Or am I totally going about things the wrong way?

Merlin
  • 157
  • 2
  • 12
  • check out: http://stackoverflow.com/questions/8322341/how-to-genenerate-database-backend-during-software-installation/8322599#8322599 – bryanmac Jun 21 '12 at 03:45
  • Thanks for the suggestion, bryanmac. I agree that would be a good solution in most cases. In my case the database I'm deploying is preloaded with hundreds of thousands of records, so I'd rather deploy it prefabricated. – Merlin Jun 25 '12 at 07:07

2 Answers2

3

Here is the complete solution I ended up implementing.

  • In the Setup and Deployment project, in the File System section, I added the SQL Server database file (MDF) to the Common Application Data folder. This is not one of the selectable options from the [Right Click]File System On Target Machine\Add Special Folder menu (for some reason), so I had to select Custom Folder from that menu then in the Default Location property, put "[CommonAppDataFolder][Manufacturer][ProductName]". This made a folder in the Common Application Data Folder, wherever it may be on the target machine (C:\Program Data\ for Win 7) for my product.
  • I needed all users to be able to have full access to this folder for the SQL Server database file to successfully attach and work properly, so the next step was to change permissions on this folder.
  • The only way I could find to do that was to write a Custom Action for the Setup and Deployment project. This link gives a good rundown of how to create and add a Custom Action: http://thedotnetway.blogspot.com.au/2008/10/creating-setup-project-in-vs-2008-w.html
  • This link provides the code that needs to be put into the Custom Actions overridden Install method: C# - Set Directory Permissions for All Users in Windows 7
  • I used System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonApplicationData) in the Custom Action code to specify the Common Application Data folder.

Hopefully that makes life easier for any developers in the future with similar requirements.

Community
  • 1
  • 1
Merlin
  • 157
  • 2
  • 12
2

There are several options to remedy this situation.

  1. If your application is only perform reading files from the program files. You would just need to run the setup package under Administrator right ( Right click on it , and then select Run as Administrator). Your Db will be written to the expected location.

  2. If you need to perform IO operation (read/write) on files, then you need to put all your files to either Program Data folder

    Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)

or the current user's data folder

**Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)**

Cheers.

Toan Nguyen
  • 11,263
  • 5
  • 43
  • 59
  • Thanks! I have moved the location of the database to the Common Applications Data folder. It works fine for Win7 users with Admin privileges, though each time the program is launched Windows pops up a User Account Control window asking if it's ok for the program to make changes to the computer. When a non-admin user tries to launch it, a similar form pops up, but they need to provide an admin account password to run it. Is there a way to have it so all users can run the application? – Merlin Jun 22 '12 at 05:42
  • If you have moved all of your files to the Common application data folder, then you don't need to run your program under the Administrator's role anymore. In order to let non-admin users run your program, you have to grant permissions to anyone who access the program data folder when installing the application. Since at that moment you would expect the administration rights to the software. The following link explains how to grant permissions to all users http://stackoverflow.com/questions/8944765/c-sharp-set-directory-permissions-for-all-users-in-windows-7 ( see the accepted answer) – Toan Nguyen Jun 22 '12 at 06:50
  • OK, so I need to grant permissions to all users on the folder I created in the Common Applications Data folder so that any user will be able to run my program. Do you happen to know if the Visual Studio (2010) Setup and Deployment Package project will allow me to easily set those permissions? Or will I need to create a Custom Actions module that features the code you referred me to above? Thank you for taking the time to help. – Merlin Jun 25 '12 at 03:22
  • Unfortunately, you have to create a custom installer yourself in order to grant permissions to users. – Toan Nguyen Jun 25 '12 at 03:58
  • Done. Tested. Works! Cheers DrakeVN! – Merlin Jun 25 '12 at 07:02