Internal Error using read only database file

Question

Hi

 

We have created an application for distributing searchable data on a CD/DVD

 

We use a SQL Compact 3.5 database file on CD / DVD. The application runs off the CD and opens the database file on the CD with FileMode=Read Only and Temp Folder set to a valid folder on the local hard drive

 

Everything works fine except when we issue certain SELECT nested statements, it fails with an "Internal Error: Invalid Security Descriptor was specified" error. At this stage it has created a zero byte tmp file in the temp folder on the local hard drive

 

Copying all the data to the hard drive and running it from there works fine, in this case when the SELECT statement is issued it properly creates a tmp file in the temporary folder. It even works if we set the database file attribute to Read Only, only does not work if it is on a physical read only media such as a CD or DVD

 

Applicaiton is written in VB.NET and uses the System.Data.SqlServerCe namespace for accessing sql ce

 

Any suggestions ?

 

Thanks

 

 

Answer 1

On which OS is the file created and on which OS do you get the problem? - Maybe you are facing this: http://www.pluralsight.com/community/blogs/jimw/archive/2008/01/23/50027.aspx

 

Answer 2

 

Thanks for your reply. I don't think it is that issue because

 

1. database is created and viewed on the same platform ( Windows XP SP2 )

 

2. it works if I copy the database file off the read only media onto a hard drive

 

It is some issue to do with it being on read only media

 

 

 

 

 

Answer 3

So only some queries fail? If so, could you share a sample of the failing queries, including schema and sample data (or a sample sdf file) and your connection string. Then I can try to reproduce, to see if you have encountered a bug.

Answer 4

Presumably your local hard drive is formatted with NTFS (which supports security), so can you try a HD partition formatted with FAT32 or FAT (which don't support security)? It may help nail down the problem. I imagine the file system used for the CD/DVD doesn't support security (CDFS/UDF?)

Rgds

Martin

Answer 5

Hi Martin

 

Good thinking, you are absolutely right. I copied my data to a FAT formatted USB drive and got exactly the same error

 

Any suggestions on how to overcome the issue ? We need it to run from any device ( FAT USB/ NTFS Hard Disk/ FAT/FAT32 CDRom etc)

 

Thanks

 

Nilesh

 

 

 

 

Answer 6

It would be best if someone from Microsoft could comment on the "Internal Error: Invalid Security Descriptor was specified" error. It sounds like a bug to me.

 

But as far as workarounds are concerned, it does then look like the file system has something to do with it. Could it be that the problem occurs when the TEMP file is created on a different file system type to the SDF file? When it fails on the FAT USB drive, have you tried both possible locations for the TEMP file, the local NTFS drive and the FAT drive? Does it fail in both situations?

 

If it is related to the TEMP file, have you thought about avoiding it's generation? It think some cursor types require the TEMP file (scrollable) while some do not (forward-only). That's according to the docs for the OLE DB provider, anyway.

 

Rgds

Martin Connell

Answer 7

 

Changing the TEMP file location makes no difference, it fails regardless of whether the TEMP file is on an NTFS or a FAT volume

 

I also tried creating the sdf file on a FAT volume, but that also does not seem to make any difference, it still fails to run from a FAT volume

 

In conclusion, regardless of where the sdf is created, and regardless of where the temp folder is, it always works if I copy the sdf file to an NTFS volume and always fails if I copy the sdf file to a FAT volume

 

I think you are probably right that this is a bug an needs to be escalated to Microsoft

 

We don't have the option of avoiding the temp file because we use a nested select in our searches and I think it needs to store intermidiate results in a temp table ....

 

SELECT * FROM table WHERE field IN (SELECT field FROM another_table WHERE some_condition)

 

 

Thanks very much for all your help

 

 

 

Answer 8

 

Hi Erik,

 

Thanks for your offer to try reproducing the problem. Did you see the discussion I had with Martin in the mean time ? It looks like it has something to do with whether the sdf file is on a FAT or NTFS volume

 

I have created a test application which reproduces the problem and I can send it to you if you like

 

Regards

 

Nilesh

Answer 9

based on your recent testing, it sounds like a bug, and I suugest you contact the SQL Compact team, so they can have a look at your repro. http://blogs.msdn.com/sqlservercompact/contact.aspx

Answer 10

 

Microsoft have confirmed that this is a bug and they will be releasing a hotfix for it soon

 

 

 

Answer 11

Does anyone know where this hotfix can be found?

Answer 12

The hotfix is: http://support.microsoft.com/kb/959697

I maintain a list of all downloadable hotfixes here: http://erikej.blogspot.com/2009/05/sql-compact-35-sp1-downloadable-hotfix.html

---

http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

Answer 13

requested the hotfix...   was sent a link but the link is not accessible "Firefox can't establish a connection to the server at hotfixv4.microsoft.com"
any other ideas how i can get this hotfix?

Answer 14

Finally got it; that worked...  Thx!