Determine correct connectionString for web publishing for ASP.NET-MVC application

Question

During development of ASP.NET-MVC application on local machine I used this connectionStringwith no problems whatsoever:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\AppDb.mdf;Initial Catalog=AppDb;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
</connectionStrings>

In hosting service database control panel I can get connection string to my MSSQL(I selected SQL Server 2012 database, but I can choose 2014 if it helps). They say connection string is:

"Data Source=SQL5013.myASP.NET;Initial Catalog=DB_9B42A0_baza;User Id=DB_9B42A0_baza_admin;Password=YOUR_DB_PASSWORD;"

Info about my MSSQL database:

Server name :   SQL5013
Server version :    Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
May 14 2014 18:34:29 
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
Database name:DB_9B42A0_baza    
Server URL:SQL5013.myASP.NET    
Login name:DB_9B42A0_baza_admin

My application is ASP.NET-MVC5.1 with Entity Framework 6.

Here it is what I have tried:

Attempt 1

Added this connection definition between <connectionStrings> </connectionStrings>

 <add name="DefaultConnection" connectionString="Data Source=SQL5013.myASP.NET;Initial Catalog=DB_9B42A0_baza;User Id=DB_9B42A0_baza_admin;Password=12345678;" providerName="System.Data.SqlClient" />

Result when accessing my website:

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Attempt 2

<add name="DefaultConnection" connectionString="Provider=sqloledb;Data Source=SQL5013,1433;Initial Catalog=DB_9B42A0_baza;User Id=DB_9B42A0_baza_admin;Password=12345678;" providerName="System.Data.SqlClient" />

Result when accessing my website:

Exception Details: System.ArgumentException: Keyword not supported: 'provider'.

Attempt 3

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\AppDb.mdf;Initial Catalog=AppDb;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Plus in WebApplication2(this is the name of my ASP.NET-MVC application) properties:

Result when accessing my website:

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Attempt 4 (based on first example from http://www.connectionstrings.com/sqlconnection/ )

<add name="DefaultConnection" connectionString="Server=SQL5013.myASP.NET;Database=DB_9B42A0_baza;User Id=DB_9B42A0_baza_admin;Password=12345678;" providerName="System.Data.SqlClient" />

Result when accessing my website:

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Summary:

I tried many more things to make it work during last 3 days which I don't remember. I read lot of MSDN articles and no luck. If I can supply any more information about the database or application please tell I will update post shortly.

Question:

What should I write in <connectionStrings> </connectionStrings> seciton to make the database connection work after I publish it?

---

Additional info:

My full Web.config file is:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>   
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\AppDb.mdf;Initial Catalog=AppDb;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <customErrors mode="Off"/>
  </system.web>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web>
    <authentication mode="None" />
    <compilation debug="true" targetFramework="4.5.1" />
    <httpRuntime targetFramework="4.5.1" />
  </system.web>
  <system.webServer>
    <modules>
      <remove name="FormsAuthenticationModule" />
    </modules>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.1.0.0" newVersion="5.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Net.Http.Primitives" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-4.0.10.0" newVersion="4.0.10.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

Update -> code resposible for database

Application_Start in Global.asax

namespace WebApplication2 {
    public class MvcApplication : System.Web.HttpApplication {
        protected void Application_Start() {
            System.Diagnostics.Debug.WriteLine("Application_Start");
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationDbContext, Configuration>());
            new ApplicationDbContext().Database.Initialize(true);
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
        }
    }
}

and the Configuration/Migrations.cs file:

namespace WebApplication2.Migrations {
    using Microsoft.AspNet.Identity;
    using Microsoft.AspNet.Identity.EntityFramework;
    using System;
    using System.Collections.Generic;
    using System.Data.Entity.Migrations;
    using System.Data.Entity.Validation;
    using System.Linq;
    using WebApplication2.Models;

    internal sealed class Configuration : DbMigrationsConfiguration<WebApplication2.Models.ApplicationDbContext> {
        public Configuration() {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
            ContextKey = "WebApplication2.Models.ApplicationDbContext";
        }

        protected override void Seed(WebApplication2.Models.ApplicationDbContext context) {
            System.Diagnostics.Debug.WriteLine("SEED STARTED");

        }
    }
}

and Models/IdentityModels.cs where is my DbContext defined:

using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNet.Identity;
using Microsoft.AspNet.Identity.EntityFramework;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System;
using System.Collections.Generic;

namespace WebApplication2.Models {
    // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://go.microsoft.com/fwlink/?LinkID=317594 to learn more.
    public class ApplicationUser : IdentityUser {

        USER PROPERTIES HERE

        public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager) {
            // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
            var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
            // Add custom user claims here
            return userIdentity;
        }
    }

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser> {


        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false) {
            System.Diagnostics.Debug.WriteLine("CONSTRUCTOR");
            Configuration.LazyLoadingEnabled = true;
            Configuration.ProxyCreationEnabled = true;
        }

       DBSETS HERE

        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Person>().HasMany(p => p.Answers).WithMany(a => a.Persons);
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

        public static ApplicationDbContext Create() {

            return new ApplicationDbContext();

        }


    }
}

Foot note

I am not eligible to start bounty before 2 days from asking a question but if this helps I offer 500 reputation points for working connection string (I will award it when the bounty will be possible). It is too hard to me and I tried countless things for 3 days.

Solution 1

Connection string in Attempt 2 is incorrect as the error says. Connection string in Attempt 3 is pointing to local. However Attempt 1 & 4 looks perfectly valid.

Have you tried publishing the web site using right click on the project-->Publish instead of going to project properties-->Package/Publish SQL?

Please note when using Project properties-->Package/Publish SQL, it doesn't update the web.config on the destination and will require a web config transform. If you have not used a transform, the connection string will be pointing to the one you had in your local.

Using right click on the project-->Publish you can provide the destination connection string, test it and then can even make this update the web.config during deployment. Ensure that the Use this connection string at runtime (update destination web.config) is checked.

UPDATE:

With regards to your question - "why my previous attempts failed?"

As above, attempt 1 & 4 looks like you have got a valid connection string but still getting the error

System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

It appears this error can occur for variety of reasons - as similar questions asked here and here have different solutions. But most possibly it is down to an incorrect connection string.

The only way to make sure is, to check what is there in your web.config after deploy/publish to hosting service. If you are sure that the web.config after deployment had the same connection string as attempt 1 & 4 then it is strange indeed.

Also, in attempt 3, the Connection string for destination database looks different to your other connection strings and is this tested? This and the fact that web publish method as I mentioned above worked instead of using Package/Publish SQL makes me think that the database might have not been deployed in your previous attempts. Why not repeat the same steps but deploy it to a location where you access the web.config and also check whether the db is deployed successfully?

Now, on - "how would section or Web.config should exactly look like in my case to make database work after publishing web application on the server without adding anything to Publish Web Settings tab?"

By not setting anything in Database section of the Settings tab in Publish Web tool - your database will not be deployed. I am not sure you want to do that considering you are using migrations.

But if you are planning to deploy your database separately for whatever reasons - then you need to apply transformations to your web.config to make it automatically change the connection string while publishing.

More information on how to do web.config transformation can be found here.

The following pages are great place to understand web and database publish

• MSDN - covers complete deployment overview including code first migrations
• ASP.NET site - focusing on hosting services deployment and deployments to different environments

Hope this helps.

Solution 2

This is not exactly a fix but will allow you to test the connection string abstracted outside of all that code to validate that at least your connection string itself is or is not working. This should also give you a way to test it quicker.. post back your findings and I will see if I can contribute more.

On the server using LinqPad, or technically you can create a test page in your app with a textbox for you to paste your connection string to test and then run it against the following code.

using(var conn = new SqlConnection("Connection String Here"))
conn.Open();

This is the minimal code needed, if you can make this work with your current connection string you know something else is going on, otherwise it gives you a fast way to poke at it until it works.