Home → Questions →

My LocalDB connection string isn't letting me access my mdf file

• Edit

cottsak

05 Feb, 2015 03:31 AM

My build is failing here on an integration test that uses LocalDB: https://ci.appveyor.com/project/cottsak/controllertests/build/0.1.2...

My connection string is Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|store.mdf;Integrated Security=True;Connect Timeout=30 and I get a message like this from the log:

 -------- System.Data.SqlClient.SqlException : Database 'C:\PROJECTS\CONTROLLERTESTS\CONTROLLERTESTS.TESTS\BIN\DEBUG\STORE.MDF' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.
   An attempt to attach an auto-named database for file C:\projects\controllertests\ControllerTests.Tests\bin\Debug\store.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm not sure what this means but my research suggests that maybe LocalDB won't run in the elevated level that the build script is using (Admin). Can you guys help me out?

1. Support Staff 1 Posted by Feodor Fitsner on 05 Feb, 2015 08:20 PM

   

   There are three different SQL Server engines on build worker: http://www.appveyor.com/docs/services-databases

   Why wouldn't you just try to attach that MDF to one of them without using LocalBD?

   • Edit

2. 2 Posted by cottsak on 09 Feb, 2015 09:38 AM

   

   @Feodor 1) Because that would be harder
   2) Because then I'd have to do something special just to get the build working on AppVeyor
   3) Because I really want to use LocalDB since most devs who have a recent version of Sql Server will have LocalDB by default

   • Edit

3. Support Staff 3 Posted by Feodor Fitsner on 09 Feb, 2015 10:11 PM

   

   OK, then we should investigate further to understand what's wrong with LocalDB on AppVeyor environment! :)

   I played with icacls a little bit, but it didn't help. Do you have any other ideas?

   • Edit

4. 4 Posted by cottsak on 10 Feb, 2015 01:25 AM

   

   Can you RDP into a build environment and try loading the solution in there manually and see if the test runner can wind up the tests using LocalDB? That might prove whether it's really a permissions thing or not.

   • Edit

5. Support Staff 5 Posted by Feodor Fitsner on 10 Feb, 2015 04:37 AM

   

   Yeah, will take a look.

   • Edit

6. 6 Posted by cottsak on 12 Feb, 2015 03:00 PM

   

   Hey @Feodor,

   I'm not trying your suggestion of just plain attaching the db files to the instance on the build box. I can't get it work tho. I've enabled SQL Server 2014 Express in the Environment settings and this is my script:

   # replace the db connection with the local instance 
   $startPath = "C:\projects\controllertests\ControllerTests.Tests\bin\Debug\"
   $config = join-path $startPath "ControllerTests.Tests.dll.config"
   $doc = (gc $config) -as [xml]
   $doc.SelectSingleNode('//connectionStrings/add[@name="store"]').connectionString = 'Server=.\sqlexpress; Database=ControllerTests.Web; Trusted_connection=true'
   $doc.Save($config)
   
   # attach mdf to local instance
   $mdfFile = join-path $startPath "store.mdf"
   $ldfFile = join-path $startPath "store_log.ldf"
   sqlcmd -S .\SQL2014 -U sa -P Password12! -Q "sp_attach_db 'ControllerTests.Web', '$mdfFile', '$ldfFile'"
   

   For the last line I get this error https://ci.appveyor.com/project/cottsak/controllertests/build/0.1.9...

   Msg 3415, Level 16, State 2, Server APPVYR-WIN20122\SQL2014, Line 1
   Database 'ControllerTests.Web' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.
   

   This all worked locally so I know the script is ok. Any ideas what I'm doing wrong?

   • Edit

7. 7 Posted by cottsak on 12 Feb, 2015 03:00 PM

   

   I meant to say "I'm now trying your suggestion.."

   • Edit

8. Support Staff 8 Posted by Feodor Fitsner on 12 Feb, 2015 03:13 PM

   

   Could it be that SQL Server instance is running under NETWORK SERVICE that doesn't have access to that .mdf file?

   • Edit

9. 9 Posted by cottsak on 13 Feb, 2015 12:05 AM

   

   Possibly. Do you have any suggestions?

   • Edit

10. 10 Posted by cottsak on 13 Feb, 2015 01:08 AM

    

    Got it working with this:

    $startPath = "C:\projects\controllertests\ControllerTests.Tests\bin\Debug\"
    $sqlInstance = "(local)\SQL2012SP1"
    $dbName = "ControllerTests.Web"
    
    # replace the db connection with the local instance 
    
    $config = join-path $startPath "ControllerTests.Tests.dll.config"
    $doc = (gc $config) -as [xml]
    $doc.SelectSingleNode('//connectionStrings/add[@name="store"]').connectionString = "Server=$sqlInstance; Database=$dbName; Trusted_connection=true"
    $doc.Save($config)
    
    # attach mdf to local instance
    $mdfFile = join-path $startPath "store.mdf"
    $ldfFile = join-path $startPath "store_log.ldf"
    #sqlcmd -S "$sqlInstance" -U sa -P Password12! -Q "sp_attach_db '$dbName', '$mdfFile', '$ldfFile'"
    sqlcmd -S "$sqlInstance" -Q "Use [master]; CREATE DATABASE [$dbName] ON (FILENAME = '$mdfFile'),(FILENAME = '$ldfFile') for ATTACH"
    

    I suspect there's a compatibility problem with the 2014 instance.

    • Edit

11. cottsak closed this discussion on 13 Feb, 2015 01:11 AM.

12. Feodor Fitsner re-opened this discussion on 13 Feb, 2015 01:39 AM

13. Support Staff 11 Posted by Feodor Fitsner on 13 Feb, 2015 01:39 AM

    

    Thanks for posting the solution here! I think it's worth adding to AppVeyor docs.

    So, what version of SQL Server engine was used to create that .mdf?

    • Edit

14. 12 Posted by cottsak on 28 May, 2015 09:25 AM

    

    The file is here https://github.com/cottsak/ControllerTests/blob/master/ControllerTe...
    Can you pull the version from it?

    • Edit

15. Support Staff 13 Posted by Feodor Fitsner on 28 May, 2015 05:42 PM

    

    Could you please elaborate what you mean?

    • Edit

16. 14 Posted by Juan Manuel on 07 Jun, 2018 09:57 PM

    

    The problem is only File .mdf and .log permissions over the user or application ejecution. only give the security permission read and write in the properties file and the connection string for example "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\WinApps17\Cash\Invoices\Data\VFindx.mdf;Integrated Security=True;Connect Timeout=30"

    • Edit

17. Owen McDonnell closed this discussion on 27 Jun, 2019 05:18 AM.