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

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. #1 Posted by Feodor Fitsner Support staff 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?

  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

  3. #3 Posted by Feodor Fitsner Support staff 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?

  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.

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

    Yeah, will take a look.

  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?

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

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

  8. #8 Posted by Feodor Fitsner Support staff 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?

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

    Possibly. Do you have any suggestions?

  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.

  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. #11 Posted by Feodor Fitsner Support staff 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?

  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?

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

    Could you please elaborate what you mean?

  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"

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

Comments are currently closed for this discussion. You can start a new one.

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

Recent Discussions