Working with SQL Server CE under vista (UAC)

[allmhuran]

I have a .net 3.5 project written with visual studio 2008. I also have
a setup project for this application.

The project includes a SQL Server CE database, and needs to read from
and write to this database.

The database file (sdf) is included in the application project as type
"content".

The setup project sends "content files" to the users application
folder.

This works under XP, but not under vista. When the program is run, an
error occurs, saying that permission is denied on the database file.

What is the solution to this for vista? Do I need to change the way
the setup project is configured, or do I need to write extra code in
the application? Do I need to do yet another different configuration
for windows 7?

I only have an XP machine on which to develop and test, so this is
very frustrating.

T.I.A.

 

[Alberto Poblacion]

[...]
The project includes a SQL Server CE database, and needs to read from
and write to this database.
The database file (sdf) is included in the application project as type
"content".
The setup project sends "content files" to the users application
folder.

This works under XP, but not under vista. When the program is run, an
error occurs, saying that permission is denied on the database file.

This is not a good idea. Ordinary users should not have write access to
the folder where the program executable is located. Even if it does work
under XP (probably because you are an administrator of that computer) it is
still not a good security practice even under that operating system. And in
Windows Vista, as you have already found, it will not work even if you are
an Administrator, because UAC "downgrades" you to the permissions of an
ordinary user.
If you really are going to always run the program as an administrator,
you can add a "requireAdministrator" parameter to your application manifest
so that it elevates privileges when it is started. But if your program is
intended to be used by non-administrative users, this is not the way to go.

What is the solution to this for vista? Do I need to change the way
the setup project is configured, or do I need to write extra code in
the application? Do I need to do yet another different configuration
for windows 7?

Ideally, you would install the database file in a folder that is
adequate for the user data, such as in a subfolder under
SpecialFolders.MyDocuments. Not only for Vista, but also for XP.
This means that your setup program would deploy the "model" .sdf to the
program folder (as you are currently doing). Every time your program is
started for the first time by a new user, the program copies the model sdf
from the program folder to the Documents folder of that user. Then the
program uses that copy for read/write access.

Don't worry about Windows 7; if you make it work for Vista, the same
configuration will also work for Windows 7.

I only have an XP machine on which to develop and test, so this is
very frustrating.

A suggestion: test it under XP with a non-administrative user. This
non-administrative user should NOT have write access to the Program Files
folder, so it will behave the same as on Vista.

 

[allmhuran]

Thanks for the reply!

     This is not a good idea. Ordinary users should not have write access to
the folder where the program executable is located.

Fair enough. I have no expertise with this kind of thing, I'm a
database designer/admin, not so much an application developer anymore,
so I just took the defaults in the visual studio setup project. About
90% of the programs I have on my own computer work this way too,
reading from and writing to configuration files underneath the program
directory. In fact, as a user I always hate it when a program goes and
hides config data under the ridiculously long application data path

    This means that your setup program would deploy the "model" .sdf to the
program folder (as you are currently doing). Every time your program is
started for the first time by a new user, the program copies the model sdf
from the program folder to the Documents folder of that user. Then the
program uses that copy for read/write access.

The database structure itself is written to support multiple users
within the one sdf. Is there a correct "special folder" to install to
such that all users of the machine have access to the same database
file? That is to say, I do not want to create a copy of the sdf for
each user, because the sdf contains shared data.

 

[allmhuran]

I've looked through the available options in a visual studio setup
project for the defaultLocation property of a directory on the target
machine. I can specify a "User's Application Data Folder", but this is
on a per user basis. I don't seem to be able to find an "all users"
folder option. The visual studio documentation also makes no reference
to such an option. But I know such an option exists in XP, for
instance, the "C:\Documents and Settings\All Users\Application Data"
path, and I can only assume a similar path exists in Vista and in
Windows 7.

So I guess my real question is this: How do I tell an installer
project in visual studio to output the .sdf to the special system
folder for all users application data?

 

[Family Tree Mike]

I've looked through the available options in a visual studio setup
project for the defaultLocation property of a directory on the target
machine. I can specify a "User's Application Data Folder", but this is
on a per user basis. I don't seem to be able to find an "all users"
folder option. The visual studio documentation also makes no reference
to such an option. But I know such an option exists in XP, for
instance, the "C:\Documents and Settings\All Users\Application Data"
path, and I can only assume a similar path exists in Vista and in
Windows 7.

So I guess my real question is this: How do I tell an installer
project in visual studio to output the .sdf to the special system
folder for all users application data?

You need to add a custom folder to the "File View" in the setup project.
Set the DefaultLocation property of the custom folder to
[COMMONAPPDATAFOLDER]. You should add a folder name specific to your
app under that folder however.

 

[allmhuran]

Thanks Mike, but I read here:

http://cdonner.com/setup-projects-w...-ce-databases.htm/comment-page-1#comment-5750

that the common app data folder is no longer writeable under windows
7. Can you confirm or deny that?

 

[allmhuran]

I actually just tried this on my XP machine with a limited rights
user.

In the application, I load the datacontext thus:

db = new
FDDB.FDDB(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)
+ "\\" + Application.ProductName + "\\FDDB.sdf")

In the setup project I created a custom folder on the target machine
with the following properties:

DefaultLocation = [CommonAppDataFolder]\[ProductName]
Property = COMMONAPPDATAFOLDER

I then attempted to run my program on my XP machine with a user who is
only a member of the "users" group.

The application fails when database access is attempted. The error is
"access to the database is not allowed".

So the common application data folder does not seem to work either.