tag:help.appveyor.com,2012-11-13:/discussions/questions/943-my-localdb-connection-string-isnt-letting-me-access-my-mdf-fileAppVeyor: Discussion 2019-06-27T05:18:09Ztag:help.appveyor.com,2012-11-13:Comment/359659502015-02-05T20:20:28Z2015-02-05T20:20:28ZMy LocalDB connection string isn't letting me access my mdf file<div><p>There are three different SQL Server engines on build worker:
<a href=
"http://www.appveyor.com/docs/services-databases">http://www.appveyor.com/docs/services-databases</a></p>
<p>Why wouldn't you just try to attach that MDF to one of them
without using LocalBD?</p></div>Feodor Fitsnertag:help.appveyor.com,2012-11-13:Comment/359659502015-02-09T09:38:31Z2015-02-09T09:38:31ZMy LocalDB connection string isn't letting me access my mdf file<div><p>@Feodor 1) Because that would be harder<br>
2) Because then I'd have to do something special just to get the
build working on AppVeyor<br>
3) Because I really want to use LocalDB since most devs who have a
recent version of Sql Server will have LocalDB by default</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-02-09T22:11:15Z2015-02-09T22:11:15ZMy LocalDB connection string isn't letting me access my mdf file<div><p>OK, then we should investigate further to understand what's
wrong with LocalDB on AppVeyor environment! :)</p>
<p>I played with icacls a little bit, but it didn't help. Do you
have any other ideas?</p></div>Feodor Fitsnertag:help.appveyor.com,2012-11-13:Comment/359659502015-02-10T01:25:36Z2015-02-10T01:25:36ZMy LocalDB connection string isn't letting me access my mdf file<div><p>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.</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-02-10T04:37:19Z2015-02-10T04:37:19ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Yeah, will take a look.</p></div>Feodor Fitsnertag:help.appveyor.com,2012-11-13:Comment/359659502015-02-12T15:00:13Z2015-02-12T15:00:13ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Hey @Feodor,</p>
<p>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 <code>SQL Server 2014 Express</code> in the
Environment settings and this is my script:</p>
<pre>
<code># 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'"</code>
</pre>
<p>For the last line I get this error <a href=
"https://ci.appveyor.com/project/cottsak/controllertests/build/0.1.9-beta">
https://ci.appveyor.com/project/cottsak/controllertests/build/0.1.9...</a></p>
<pre>
<code>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.</code>
</pre>
<p>This all worked locally so I know the script is ok. Any ideas
what I'm doing wrong?</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-02-12T15:00:49Z2015-02-12T15:00:49ZMy LocalDB connection string isn't letting me access my mdf file<div><p>I meant to say "I'm now trying your suggestion.."</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-02-12T15:13:00Z2015-02-12T15:13:00ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Could it be that SQL Server instance is running under NETWORK
SERVICE that doesn't have access to that <code>.mdf</code>
file?</p></div>Feodor Fitsnertag:help.appveyor.com,2012-11-13:Comment/359659502015-02-13T00:05:21Z2015-02-13T00:05:21ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Possibly. Do you have any suggestions?</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-02-13T01:08:53Z2015-02-13T01:08:53ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Got it working with this:</p>
<pre>
<code>$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"</code>
</pre>
<p>I suspect there's a compatibility problem with the 2014
instance.</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-02-13T01:39:37Z2015-02-13T01:39:37ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Thanks for posting the solution here! I think it's worth adding
to AppVeyor docs.</p>
<p>So, what version of SQL Server engine was used to create that
.mdf?</p></div>Feodor Fitsnertag:help.appveyor.com,2012-11-13:Comment/359659502015-05-28T09:25:23Z2015-05-28T09:25:23ZMy LocalDB connection string isn't letting me access my mdf file<div><p>The file is here <a href="https://github.com/cottsak/ControllerTests/blob/master/ControllerTests.Tests/store.mdf">
https://github.com/cottsak/ControllerTests/blob/master/ControllerTe...</a><br>
Can you pull the version from it?</p></div>cottsaktag:help.appveyor.com,2012-11-13:Comment/359659502015-05-28T17:42:08Z2015-05-28T17:42:08ZMy LocalDB connection string isn't letting me access my mdf file<div><p>Could you please elaborate what you mean?</p></div>Feodor Fitsnertag:help.appveyor.com,2012-11-13:Comment/359659502018-06-07T21:57:35Z2018-06-07T21:57:39ZMy LocalDB connection string isn't letting me access my mdf file<div><p>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"</p></div>Juan Manuel