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

cottsak's Avatar

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

    Feodor Fitsner's Avatar

    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?

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

    cottsak's Avatar

    @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

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

    Feodor Fitsner's Avatar

    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?

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

    cottsak's Avatar

    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.

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

    Feodor Fitsner's Avatar

    Yeah, will take a look.

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

    cottsak's Avatar

    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?

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

    cottsak's Avatar

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

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

    Feodor Fitsner's Avatar

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

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

    cottsak's Avatar

    Possibly. Do you have any suggestions?

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

    cottsak's Avatar

    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.

  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

    Feodor Fitsner's Avatar

    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?

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

    cottsak's Avatar

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

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

    Feodor Fitsner's Avatar

    Could you please elaborate what you mean?

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

    Juan Manuel's Avatar

    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"

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac