locked
Permission Denied when calling Open on SqlCeConnection RRS feed

  • Question

  • I'm having a problem where I am getting a "Permission denied" exception when I call Open on a SqlCeConnection object using SqlCE version 3.5.  It does this when mode is set to Read Only in the connection string.  Furthermore it does this on XP, but not Vista.  Here is the connection string I'm building:

     

    string connectionString = "Data Source='" + fileName + "';password=" + DBPassword + ";mode='Read Only';Temp File Directory='" + Path.GetTempPath() + "'";

     

     A prompt response with a resolution to this would be greatly appreciated.

     

    Ron

    Tuesday, December 4, 2007 7:06 PM

Answers

  • Rono4,

     

    Here is the scoop on this. When we move a SDF file from XP to Vista, because of a change in the NLS Version we attempt to open the file in read-write mode to rewrite the indexes if any exist. This happens only on the first open on the move from XP to Vista. So a simple work around for you is to temporarily change the file to read-write mode open it on Vista and revert it back to read-only.

     

    Hopfully you switch back to using SQLCE from XML, We are more searcheable

     

    Saturday, December 29, 2007 1:26 AM

All replies

  • Please show us the contents of connectionstring after setting all these values...

     

    Tuesday, December 4, 2007 8:23 PM
  • The connection string it built is this:

     

    Data Source='C:\Documents and Settings\User\My Documents\AIAHandbook.wdf';password=NotTheRealPassword;mode='Read Only';Temp File Directory='C:\Documents and Settings\User\Local Settings\Temp\'
    Tuesday, December 4, 2007 11:23 PM
  • I have tested your scenario, and connot reproduce with SQL Compact 3.5. There must be something wrong with one of your paths, or some permission issue. In order to troubleshoot this, I would recommend that you use the Sysinternals  Filemon tool to monitor your app for permission denied errors. Download from: http://www.microsoft.com/technet/sysinternals/utilities/filemon.mspx

     

    Thursday, December 6, 2007 9:35 AM
  • Erik is right. You could be missing something simple out here.

     

    Verify the following things:

    1. The paths to the Database File exist on the xp machine

    2. You have the permission to create a temporary file in the given directory

    3. You are supplying the right password. (Try opening the file through Visual Studio 2008 Server Explorer)

    4. You are not running another program that has opened the same file in an exclusive mode.

    Friday, December 7, 2007 5:00 AM
  • I confirmed that it's not any of the items you listed.  If I remove just the Read Only and Temp Folder sections of the connection string, it works fine.  I also tried a few different temp folder locations and it still failed.  Perhaps it's worth noting that I created the data file on an Vista machine where it works fine.

     

    Ron

    Monday, December 17, 2007 4:03 PM
  •  

    Doing some web searching, I found someone else who is having the same problem, which I was able to reproduce.  Check out this link for the details:

    http://groups.google.com/group/microsoft.public.sqlserver.ce/browse_thread/thread/e8507cc805042d9b#c95b93d52eeb101e

     

    I switched back to SqlCe v3.0 for other reasons.  But the problem looks very similar to the one I got in SqlCe 3.5.  What this link says is that the when, in XP, you first open a data file that was created in Vista, (or vice-versa) it tries to edit the SDF file.  If you have the read only option on it chokes and produces an exception.  But if you let it edit it, you can thereafter connect to it read-only.  I'd rather not have an XP and a Vista version of the SDF file.  If someone has a better work-around, I'd be eager to hear it.

    Tuesday, December 18, 2007 9:03 PM
  •  

    This seems to be a strange behaviour.

    Can you provide some more details to help repro the issue?

    Are you able to repro it for other databases also or is it specific to the particular db file?

     

    Wednesday, December 19, 2007 5:31 AM
    1. Create a blank SQL Compact database on an XP machine using SQL Server Management Studio
    2. On a Vista machine create a project using SqlCe 3.1 SqlCeConnection.  I'm using Visual Studio 2008.
    3. build a connection string that look like this:
      Data Source=C:\projects\Datafile.sdf;mode='Read Only';Temp File Directory=c:\projects\
      and run this code:
      SqlCeConnection connection = new SqlCeConnection(connectionString);
      connection.Open();   //should fail here with an attempted to read or write protected memory exception
    Wednesday, December 19, 2007 2:03 PM
  • The workaround I settled on was to use an XML file to store my data instead.

    Wednesday, December 26, 2007 1:48 PM
  • Rono4,

     

    Here is the scoop on this. When we move a SDF file from XP to Vista, because of a change in the NLS Version we attempt to open the file in read-write mode to rewrite the indexes if any exist. This happens only on the first open on the move from XP to Vista. So a simple work around for you is to temporarily change the file to read-write mode open it on Vista and revert it back to read-only.

     

    Hopfully you switch back to using SQLCE from XML, We are more searcheable

     

    Saturday, December 29, 2007 1:26 AM
  • Any news on this 'Permission Denied' message when switching between Vista and XP? I'm now having the exact same problem as described by Seshu Adunuthula. I am working on a redistributable app so I really need this to be working reliably.

    Regards,

    Stephen
    Monday, June 22, 2009 4:20 PM
  • Hi Stephen,

    the only workaround is the one described above - or having 2 sdf files, one for XP/Server 2003 and one for Vista/Server 2008/Win7


    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Monday, June 22, 2009 4:57 PM
  • OK. Well, as I've said this is due to run from CD so the first option is a no-go as this will always be read-only. The second option is also a no-go as the data in the redistributable database is being built dynamically by a server application that will always run on Windows Server 2003. The database is then written straight to CD and being distributed to various recipients who are either on XP or Vista. Unless I've missed something it looks like we may need to rewrite our app to work with XML :(
    Monday, June 22, 2009 6:25 PM
  • Stephen, don't give up!

    I would not be too hard to add the following 3 steps to the automatic process:

    Step 1: Copy the just created file to a Vista workstation/VPC.

    Step 2: Run SqlCeConnection.Open from a console app on the Vista machine. (This will rebuild the indexes)

    Step 3: Copy the Vista file and the XP file to the CD.

    Your app can use System.Environment.OSVersion.Version.Major to determine which file to use.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Tuesday, June 23, 2009 5:34 AM
  • Stephen, don't give up!

    I would not be too hard to add the following 3 steps to the automatic process:

    Step 1: Copy the just created file to a Vista workstation/VPC.

    Step 2: Run SqlCeConnection.Open from a console app on the Vista machine. (This will rebuild the indexes)

    Step 3: Copy the Vista file and the XP file to the CD.

    Your app can use System.Environment.OSVersion.Version.Major to determine which file to use.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.

    Erik, the problem there is that we would need to have these steps carried out by a user everytime they generate the database for distribution, which they will be doing hundreds of times, and I think that may be a bit much too much to ask.
    Tuesday, June 23, 2009 8:23 AM
  • Hi Stephen,

    The proposed process can be automated, I does not require user intervention.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    Tuesday, June 23, 2009 8:25 AM
  • Well, we decided to go for an MS Access mdb file for the redistributable data source. It only took a few modifications to the table adapters and now its doing exactly what we need.

    Thank you for all of your help. It is greatly appreciated

    Regards,

    Stephen
    Wednesday, June 24, 2009 2:23 PM
  • Does this apply when an SDF file is moved from a WinCE 6 device to a Windows 7 PC using SQLCE 3.5?

    I believe I'm seeing this behaviour. And its a really big problem, because it prevents us from ever being able to demonstrate to the FDA that medical data gathered on the CE device has not been tampered with when it is displayed on a PC.

    Thursday, May 23, 2013 6:34 PM
  • i would imagine so, yes.

    Please mark as answer, if this was it. Visit my SQL Server Compact blog Windows 8 Apps Showcase

    Thursday, May 23, 2013 7:32 PM
  • I know this is old, but I'll add the following answer for others, who like me, may have a similar problem.

    My software relies on a SQLCE 3.5 db supplied by our partner. (hence I can't modify the db or recreate it as described above)  They are able to use the db fine, but our program could not access it (Permission Denied).  Like Rono4 above, I have verified many times that the paths, permissions, and all those other things are set correctly.

    I have found two workarounds:

    1. remove the "Mode=Read Only" from the connection string.  This now allows my program to work reliably every time regardless of the platform (Windows 10, and XP).
    2. if you can't modify the Connection String, download a software called "CompactView".  Use it to open the DB and then close it again. I'm not sure what this modifies in either the db or in windows, but it allows me to reopen the file with no issues without having to modify my code.

    I hope this helps that late night coder fighting to get this Permission Denied issue resolved at crunch time.


    • Edited by efhilton Wednesday, June 13, 2018 9:03 PM
    Wednesday, June 13, 2018 9:03 PM