Web Deploy / Publish is adding a unknown connection string?

Question

Previously publishing my web api app was working perfectly. Today i converted my app from being "any cpu" to x86. Now when i'm publishing its adding a connection string to the web config which is causing my app to fail. At first i though i maybe accidently adding something to the web.config transform file but nope i haven't. Here is my transform file:

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <!--
    In the example below, the "SetAttributes" transform will change the value of
    "connectionString" to use "ReleaseSQLServer" only when the "Match" locator
    finds an attribute "name" that has a value of "MyDB".

    <connectionStrings>
      <add name="MyDB"
        connectionString="Data Source=ReleaseSQLServer;Initial Catalog=MyReleaseDB;Integrated Security=True"
        xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
    </connectionStrings>
  -->
  <entityFramework xdt:Transform="Replace">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Server=MyWebServer;Database=RunLog;Trusted_Connection=True; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

  <system.web>
    <compilation xdt:Transform="RemoveAttributes(debug)" />
    <!--
      In the example below, the "Replace" transform will replace the entire
      <customErrors> section of your web.config file.
      Note that because there is only one customErrors section under the
      <system.web> node, there is no need to use the "xdt:Locator" attribute.

      <customErrors defaultRedirect="GenericError.htm"
        mode="RemoteOnly" xdt:Transform="Replace">
        <error statusCode="500" redirect="InternalError.htm"/>
      </customErrors>
    -->
  </system.web>
</configuration>

Here is my web config file:

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
  <system.webServer>
    <handlers>
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=(localdb)\v11.0; Integrated Security=True; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
</configuration>

Finally after i publish this is what my web config file looks like:

<?xml version="1.0" encoding="utf-8"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <system.web>
    <compilation targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
  <system.webServer>
    <handlers>
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Server=MyWebServer;Database=RunLog;Trusted_Connection=True; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
  <connectionStrings>
    <add name="RunLog" connectionString="RunLog_ConnectionString" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

If you look above you will see a connection string was added.

After some research i found that you can add a connection string durinig publish. Well i've been publishing with the same profile so i deleted the profile multiple times and created a new one and made sure to uncheck the box to add a connection string:

<?xml version="1.0" encoding="utf-8"?>
<!--
This file is used by the publish/package process of your Web project. You can customize the behavior of this process
by editing this MSBuild file. In order to learn more about this please visit http://go.microsoft.com/fwlink/?LinkID=208121. 
-->
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <WebPublishMethod>MSDeploy</WebPublishMethod>
    <SiteUrlToLaunchAfterPublish />
    <MSDeployServiceURL>http://MyWebServer</MSDeployServiceURL>
    <DeployIisAppPath>My Web Site/Go</DeployIisAppPath>
    <RemoteSitePhysicalPath />
    <SkipExtraFilesOnServer>False</SkipExtraFilesOnServer>
    <MSDeployPublishMethod>RemoteAgent</MSDeployPublishMethod>
    <UserName>JohnBob</UserName>
    <_SavePWD>False</_SavePWD>
    <PublishDatabaseSettings>
      <Objects xmlns="">
        <ObjectGroup Name="RunLog" Order="1" Enabled="False">
          <Destination Path="" />
          <Object Type="DbCodeFirst">
            <Source Path="DBContext" DbContext="RunLog.DL.RunLogContext, RunLog.DL" Origin="Convention" />
          </Object>
        </ObjectGroup>
      </Objects>
    </PublishDatabaseSettings>
  </PropertyGroup>
  <ItemGroup>
    <MSDeployParameterValue Include="$(DeployParameterPrefix)RunLog-Web.config Connection String">
      <UpdateDestWebConfig>False</UpdateDestWebConfig>
    </MSDeployParameterValue>
  </ItemGroup>
  <ItemGroup>
    <_ConnectionStringsToInsert Include="RunLog" />
  </ItemGroup>
</Project>

As you can see there is connection stuff in there but i specifically unchecked "use this connection string at runtime (update destination web config). If i delete the connection string that was added my site works perfectly.

Does anyone know whats going on?

Edit:
I thought maybe i was doing doing something wrong but after working through it tonight i'm thinking it my be a bug. I decided to rollback all my code and see if i could reproduce the problem. Previously in the "Package/Publish Web" tab in properties the "Include all database configured in the Package/Publish SQL Tab: is selected. In the "Package/Publish SQL" Tab there is one connection string defined in "database entries" but its blank. If i leave it as is and try to publish everything works perfectly. If i delete the connection string and re-add it things get messed up again. In the dialog you see above if the database section is blank everything works correctly when your creating a profile but if it has remote connection string box then things wont' work correctly

I have the same problem, the publish wizard automatically adds the connection string to the published web.config while I want to avoid that. — Etienne, Dec 14 '12 at 03:13
Can you add the content of your Web.Release.config file as there may be a transform in there that is adding the connection string — MarkG, Dec 31 '12 at 07:29
Hi user127954, did you get to resolving this issue? i am experiencing the same. The dummy connectionstring gets added for a context eventhough i explicitly tell Web Deploy not to. I am using a defaultConnectionFactory that creates a connectionstring based on the subdomain a user is logged into. But due to the creation of the dummy connectionstring, the defaultConnectionFactory is rendered useless. — user3042320, Jan 04 '14 at 15:26
Hi @user3042320, I have the exact same problem. Did you manage to find the solution? — To Ka, Aug 31 '15 at 11:46
@ToKa unfortuantely no. It was a little side project at home and unfortunately it no longer exist. But years after we just recently developed a new MVC 5 app and hit the same exact problem. It caused our site to fail because we were encrypting the connection string. We had to do some hackery to fix it but it definitely still is an issue. — coding4fun, Feb 02 '16 at 14:10
Unbelievable that this has not been addressed AT ALL, and it is Oct 27, 2019. Visual Studio 2019 still does this. Click edit settings, uncheck every one of those "Use this connection string at runtime" options, save it, and it adds them anyway, and when you go back into the options, they're all checked again. This is gross behavior that just overrides whatever connection settings you have in the web config and forcefully injects it's own connection string just because you use the settings page. So basically, you can never ever edit the settings or use the settings editor, because it's broke. — Triynko, Oct 28 '19 at 02:10

score 8 · Answer 1 · edited May 23 '17 at 12:34

8

Add this project property to the .csproj file by editing the XML:

<Project>
  <PropertyGroup>

    <AutoParameterizationWebConfigConnectionStrings>false</AutoParameterizationWebConfigConnectionStrings>

    ...
  </PropertyGroup>
  ...
</Project>

Alternatively, use a Web Publishing Pipeline targets file as described in this related StackOverflow question.

edited May 23 '17 at 12:34

Community

1
1

answered Feb 07 '13 at 21:16

umbyersw

585
5
12

10

This doesn't seem to work for me. Even with this attribute in there it still puts the connection strings into the deployed web.config file every time. – Brannon Apr 25 '13 at 04:20
Could you explain this solution a bit more? – Ross Brasseaux Mar 22 '17 at 14:46

score 2 · Answer 2 · answered Jan 11 '19 at 15:19

2

Edit project .csproj file and add this:

<PropertyGroup>
<InsertAdditionalWebCofigConnectionStrings>false</InsertAdditionalWebCofigConnectionStrings>

It worked for me.

answered Jan 11 '19 at 15:19

MelOS

595
5
10

score 1 · Answer 3 · edited May 23 '17 at 10:29

1

The solution that worked for me, although it's more of a hack, was using the web deploy transformations to remove rogue connection string. See user2395249 answer:

One-click publish in vs 2012: how to remove _ConnectionStringsToInsert?

edited May 23 '17 at 10:29

Community

1
1

answered Feb 08 '16 at 22:08

Crob

599
4
26

score 0 · Answer 4 · answered Feb 08 '17 at 09:23

In the .pubxml file there where some extra connectionstrings added for some reason. Remove all of them and it should work fine, the default connectionstring will still be added.

I have a hunch that this has been added to the .pubxml when I fiddled a little with my publishing information but it wasn't removed when I switched back to my regular publish setup.

<ItemGroup>
    <MSDeployParameterValue Include="$(DeployParameterPrefix)DefaultConnection-Web.config Connection String" />
    <MSDeployParameterValue Include="$(DeployParameterPrefix)MyProject-Web.config Connection String" />
    <MSDeployParameterValue Include="$(DeployParameterPrefix)MyProject_dev-Web.config Connection String" />
  </ItemGroup>
  <ItemGroup>
    <_ConnectionStringsToInsert Include="MyProject" />
    <_ConnectionStringsToInsert Include="Myproject_dev" />
  </ItemGroup>

Web Deploy / Publish is adding a unknown connection string?

4 Answers4

Linked