Deploying MVC 4 CodeFirst to Azure

Question

I'm trying a 90 day free trial of azure. It is super easy to deploy/publish the website and create the database, but for some reason my tables are not being created in the database. I'm using entity framework 4.4(i believe) and code first migrations. I've read that azure uses its own connection string, but I went ahead and changed my connection strings as well. I Spent hours on this and I can't figure out what is wrong and why my tables are not being created. I deploy website, enable-migrations, add-migration, update-database, then publish with checking the checkbox for code-first. Maybe my connection string is wrong? Any help is greatly appreciated.

In 'MyProject.Web' web.config:

<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=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=tcp:n98my***.database.windows.net,1433;Initial Catalog=slutips_db;User Id=****;Password=****;" />
</connectionStrings>

Then in 'MyProject.Data' app.config, where my datacontext.cs is held:

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<contexts>
  <context type="SeluCmpsTutorials.Data.DataContext">
    <databaseInitializer type="System.Data.Entity.MigrateDatabaseToLatestVersion">
      <parameters>
        <parameter value="slutips_db"/>
      </parameters>
    </databaseInitializer>
  </context>
</contexts>
</entityFramework>

• Also I noticed even though all my connection strings are changed, when I add-migration and update-database, it still using the ./sqlexpress local database, how is that possible?

Answer 1

I had the exact same problem and it was linked to my password containing '%' character. I checked the resulting web.config on Azure connecting with ftp and the part of my password containing '95%a' turned to '95ª' ... automagically. That might be a bug in the web deployment feature of azure, because the password was correct in my publish profile locally.

If indeed the resulting password to the DB is corrupted in the resulting connection string, you have to reset it :

• From Windows Azure Management, in the SQL DATABASES tab, select the SERVERS list (not the DATABASE list) and click on your server
• In the Dashboard, there is a link to reset the admin password
• In visual studio, update the password in your publish profile's connectionString (Settings, Databases)
• Make a change to the web.config (a real one, not a comment), or it won't be re-published
• Publish, and on first use of the database, the migrations will be applied

Answer 2

After seeing this question for a few days, a couple of thoughts. First, is the name of your context is infact DefaultConnection. Entity Framework (in order to be used with the parameterless constructor) likes the connection string name to match the name of the context.

For instance, if your declare a new context variable as such:

var context = new MyEntities();

Then your connection string should be called MyEntities

<connectionStrings>
<add name="MyEntities" connectionString="Data Source=tcp:n98my***.database.windows.net,1433;Initial Catalog=slutips_db;User Id=****;Password=****;" />
</connectionStrings>

Second, are you 100% sure that there are no web.config transformation files and/or that you also properly updated the connection strings in those files?

Third, remember that upon deployment, any configuration file other than the web.config is essentially ignored. The executing assembly is the web application, not the data project or any others. Any configurations relating to EF setup and deployment should be located in the web.config file (like the snippet that you have in your data app.config)

Answer 3

what i did was go to my azure management portal. clicked on databases. went view connection strings and then copied those directly into my relevant config. in my case its "release" config.

make sure that the web config transformations are enabled and working correctly.

also make sure you've enabled firewall access to each of the db's... you have to click "enable connection through firewall" or something similar. its in the db options on the portal.

Answer 4

Try adding encryption to your connection string...

<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=tcp:n98my***.database.windows.net,1433;Initial Catalog=slutips_db;User Id=****;Password=****;Integrated Security=False;Persist Security Info=True;Encrypt=True" />
</connectionStrings>

Answer 5

If you follow this tutorial: https://www.windowsazure.com/en-us/develop/net/tutorials/web-site-with-sql-database/ It'll show you how to connect Visual Studio (2012 in the example, not sure if works for any others) with Azure - basically you want to log into the portal, and download the publishing credentials. This will contain your database connection string, which you can then use to push code first migrations up to Azure.

What it looks like you've done is manually added in properties to the app.config yourself. The way the publish works, it defines a local version in your .config file like thus:

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="v11.0" />
  </parameters>
</defaultConnectionFactory>

Then, when you publish, VS will auto-magically insert the required stuff into your .config - if I go and FTP to my web site and look at the generated .config file, you see this:

<contexts>
  <context type="Web_App.Models.TrackSafeDb, Web App">
    <databaseInitializer type="System.Data.Entity.MigrateDatabaseToLatestVersion`2[[Web_App.Models.TrackSafeDb, Web App], [Web_App.Migrations.Configuration, Web App]], EntityFramework, PublicKeyToken=*******************">
      <parameters>
        <parameter value="Web_App.Models.TrackSafeDb_DatabasePublish" />
      </parameters>
    </databaseInitializer>
  </context>
</contexts>

So I'd try removing the extraneous stuff from your local app.config and re-publishing

Answer 6

key gotcha to be aware of regarding sql azure connections: for userid it actually usually needs to be user id @ server name - do would read youruserid@n98my*** in the connectionstring.

Also, not sure if you need the "tcp:" (not using it) and you may also want to add providerName="System.Data.SqlClient"

Answer 7

Does your db user have necessary permissions for database modification ? How did you set up your user ?

Initially you should create login for Azure sql server, sometihing like this: (execute following sql on your server master database, replace angle-brackets values with actual values)

CREATE LOGIN [<SomeServerLogin>] WITH PASSWORD=N'<somepassword>'

Then connect to your slutips_db database as admin and execute following sql

CREATE USER [<slutips_db_user>] FROM LOGIN [<SomeServerLogin>];
GO;
EXEC sp_addrolemember 'dbmanager', '<slutips_db_user>';
GO;
EXEC sp_addrolemember 'loginmanager', '<slutips_db_user>';
GO;

'dbmanager' role allows table creation\management.

'loginmanager' role enables creation of another users in current database (your slutips_db_user will be allowed to execute CREATE USER <slutips_db_user1> FROM LOGIN <SomeServerLogin1> clause

Edit1: Also - ensure that your connection string user have user@n98my*** (user@server) format.