Define the correct connectionString for web publishing for an ASP.NET-MVC application

Question

During the development of the ASP.NET-MVC application on the local computer, I used connectionString without any problems:

 <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 the support database database control panel, I can get the connection string to my MSSQL (I selected the SQL Server 2012 database, but I can choose 2014 if this helps). They say the connection string is:

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

Information 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 is what I 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):

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 the 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 for the last 3 days, which I don’t remember. I read many MSDN articles and no luck. If I can provide more information about the database or application, tell me that I will send a message soon.

Question:

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

---

Additional Information:

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 for the 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 Configuration/Migrations.cs :

 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 my DbContext :

 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 can’t start earning up to 2 days by asking a question, but if this helps, I offer 500 reputation points for the working connection string (I will reward it when it will be possible to get bonuses). This is too complicated for me, and I have tried countless things for 3 days.

Answer 1

The connection string in attempt 2 is incorrect, as the error says. The connection string in attempt 3 indicates a local one. However, attempts 1 and 4 look completely correct.

Have you tried publishing a website using right click on the project-->Publish instead of switching to project properties-->Package/Publish SQL ?

Note that when using project properties-->Package/Publish SQL it does not update web.config for the destination and requires a web configuration conversion. If you did not use the conversion, the connection string will point to the one that was in your local one.

Using right click on the project-->Publish , you can specify the recipient’s connection string, test it, and then even do this web.config update during deployment. Make sure Use this connection string at runtime (update destination web.config) .

UPDATE:

As for your question - “why have my previous attempts failed?”

As above, attempts 1 and 4 look like you have a valid connection string, but still getting an error

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

It seems that this error can occur for a number of reasons - since the similar questions asked here and here are different solutions. But most likely this is the wrong connection string.

The only way to make sure is to check what is in your web.config after deploying / publishing to the hosting. If you are sure that web.config after deployment had the same connection string as in attempt 1 and 4, then this is really strange.

Also, in attempt 3, the Connection string for destination database is different from other connection strings and is this checked? This and the fact that the Internet publishing method, as I mentioned above, worked instead of using Package/Publish SQL , makes me think that the database may not have been deployed in your previous attempts. Why not repeat the same steps, but deploy it to the place where you are accessing web.config, and also check if db is successfully deployed?

Now, on - "how does a section or Web.config look exactly in my case to do work with the database after publishing the web application on the server without adding anything to the" Publish web settings "tab?"

Without installing anything in the Database section of the Settings tab in the Publish Web tool, your database will not be deployed. I'm not sure if you want to do this, given that you are using migrations.

But if you plan to deploy your database for any reason, then you need to apply the transformations to your web.config to automatically change the connection string when publishing.

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

The following pages are a great place to understand publishing on the Internet and in the database.

• MSDN - covers a complete deployment overview, including the first code migrations.
• ASP.NET site - focusing on deploying and deploying hosting services in different environments

Hope this helps.

Answer 2

This is not quite a fix, but will allow you to test the connection string abstracted outside of all this code to verify that at least your connection string itself or not works. It should also give you a way to test it faster. Post my findings and I will see if I can make an additional contribution.

On a server using LinqPad, or technically, you can create a test page in your application with a text box so that you can insert a connection string for testing and then run it using the following code.

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

This is the minimum code needed if you can do this work with your current connection string, you know that something else is happening, otherwise it gives you a quick way to pop it until it works.