Unable to update database .MDF is Read Only

Question

I have a local DB that I have in an application. When I install it on another machine I get the error

Unable to update database.. .mdf is read only.

I was able to get around it by editing my permissions on the .mdf and log file themselves. I did some research and noticed that I may want to install the database to a shared folder. However, I am not sure how to do that and the answers I did come across did not make a whole lot of sense to be.

 //My connection string
 SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|AssetDatabase.mdf;Integrated Security=True");

Any guidance would be greatly appreciated. I am learning.

Solution 1

The MDF file would be readonly either because the readonly file attribute flag is set, in which case you have to unset it, or another program has the MDF file locked as readonly. Are you running a version of SQL server that is using that file?

I've never seen a connection to the MDF file directly, usually it's done via the server, and the server will manage all the IO for the MDF file.

E.g. a typical connection string for SQL Server:

Database=<dbname>;Server=<servername>;MultipleActiveResultSets=True;Connection Timeout=10;User Id=<username>;Password=<password>;

Additional links:

1) Failed to update .mdf database because the database is read-only (Windows application)

2) Failed to update database because it is read-only

3) Failed to update database "*.mdf" because read only EntityFramework

4) http://www.codeproject.com/Questions/183758/Failed-to-update-mdf-database-because-the-database

Solution 2

1. Just go to the program files and find the folder of the installed program.
2. Just right click the .mdf file in the folder and click PROPERTIES.
3. In PROPERTIES TAB -> SECURITY -> you'll see Group or Usernames.
4. In that select the User to which u want to give Access to the file.
5. If for that user the PERMISSION is not set to FULL CONTROL.
6. TO CHANGE PERMISSIONS -> CLICK EDIT.
7. Now the Group or Username box will open.
8. In that Select The User -> Select FULL CONTROL in Permission Box.
9. Now follow the steps for the log file too. IF Needed.

OR

Just install your application in different folder other than ProgramFile(x86)

Comments

[LearningCSharp]

Nothing else is using the file. Its a local db, not sure if that matters. The database is used to store items for a personal inventory. I created an installer package for it and installed it on my computer and when I ran it after install and tried to add a new item to the DB it kicked back that error. It was because the file permissions in the directory it installed the MDF to. Program Files along with the .exe. I changed the permissions in windows and it works great. I cant work out how to deploy to actual MDF to a folder that does not require elevated permissions to write to.

[Loathing]

Normally you would choose the AppData folder, e.g. Directory.GetParent(System.Windows.Forms.Application.UserApp‌​DataPath); Use the parent otherwise the directory includes the version number. You could check for the existence of the file, and if its not there then create it or copy it there.

[Loathing]

Refer to this thread on setting the data directory: stackoverflow.com/questions/1409358/…

[LearningCSharp]

Loathing, I got it to deploy my database to C:\ProgramData folder. However the MDFs are still read only for USER.

[Loathing]

I added some additional links. Reading through those, the recommendation seems to go to the security tab on file properties, and make sure the user has full control to the MDF file.

[LearningCSharp]

Thanks. I managed to manually change the permissions through the security tab. But don't really want people to have to do that when they install this

[Loathing]

Can you set the security using code? E.g. stackoverflow.com/questions/7804800/…

[LearningCSharp]

Not sure where to set that. Its a little above my level of expertise. I have been thinking, perhaps ProgramData isnt the best place as the db file is inheriting the permissions from that folder. Since I do not know how to set permissions on install, I need to find a place to deploy the database file that allows USERS (user group) write access I think.

[cramopy]

This is more a comment then an answer - please integrate into the SO community, get your 50 reputation and then add comments. Please don't add such "i-don-t-have-enough-rep-to-comment" answers!!

[Admin]

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

[Engr. Khuram Shahzad]

Thank you so much, it help me alot