3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
LearningCSharp
  • 87
  • 1
  • 2
  • 12
  • An `.mdf` file is a **SQL Server** master data file. SQL Server - as the name implies - is really a **server-based** system. I would **stop fiddling** around with free-floating files - if you want to use SQL Server, create your database **on the server** using Management Studio, and connect to it using the logical database name and let SQL Server deal with the all of the file issues.... – marc_s Jul 30 '14 at 04:52

6 Answers6

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)

Af'faq
  • 501
  • 3
  • 13
  • 28
0

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

Community
  • 1
  • 1
Loathing
  • 5,109
  • 3
  • 24
  • 35
  • 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. – LearningCSharp Jul 30 '14 at 02:54
  • Normally you would choose the AppData folder, e.g. Directory.GetParent(System.Windows.Forms.Application.UserAppDataPath); 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 Jul 30 '14 at 03:01
  • Refer to this thread on setting the data directory: http://stackoverflow.com/questions/1409358/ado-net-datadirectory-where-is-this-documented – Loathing Jul 30 '14 at 03:07
  • Loathing, I got it to deploy my database to C:\ProgramData folder. However the MDFs are still read only for USER. – LearningCSharp Jul 30 '14 at 15:27
  • 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. – Loathing Jul 30 '14 at 15:44
  • 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 – LearningCSharp Jul 30 '14 at 15:57
  • Can you set the security using code? E.g. http://stackoverflow.com/questions/7804800/setting-security-on-a-single-file – Loathing Jul 30 '14 at 16:25
  • 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. – LearningCSharp Jul 30 '14 at 17:16
  • @LearningCSharp, did you get any solution for this? – S. B. Aug 10 '22 at 14:06
0

First stop the SQLEXPRESS service from local-Services menu and then try to move and connect the database again by attaching database option in SQLEXPRESS. it should work. it works for me. :)

Kashif Ijaz
  • 45
  • 11
0

I solved the same problem as follows:

While creating "Setup", I manually added my database files database.mdf and database_log.ldf into the Application Folder file. But after adding these files, make sure that the ReadOnly option in the Properties section is False.

Also "connectionString" I use:

connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;Connect Timeout=30"
Yunus Emre Söğüt
  • 37
  • 1
  • 2
  • 8
  • Hi, I am facing the same issue and I checked the ReadOnly property for the database is set to False only. – S. B. Aug 10 '22 at 14:00
0

just remove the .mdf file location to c:\ or desktop or anywhere but not in %program file directory

hamed algazaly
  • 3
  • 1
  • 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](/help/how-to-ask). – Community Sep 13 '21 at 12:35
-2

if your database file on the C volume, try to move the .mfd file to D volume

Rayan Albouni
  • 169
  • 1
  • 5
  • 2
    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!! – cramopy Feb 05 '16 at 17:34