Deploying EF Code First Apps to Production Database

Question

I wrote a simple .net mvc 3 app on VS2010 using code first approach to generate the schema. When i deployed it to a shared production server i got the following error: [SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.]

Here is my model code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace Bonappetit.Models
{
public class MenuItem
{
    public int ID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}

public class MenuItemDBContext : DbContext
{
    public DbSet<MenuItem> MenuItems { get; set; }
}
}

here is my controller code:

 namespace Bonappetit.Controllers
{ 
public class MenuItemsController : Controller
{
    private MenuItemDBContext db = new MenuItemDBContext();

    //
    // GET: /MenuItems/

    public ViewResult Index()
    {
        return View(db.MenuItems.ToList());
    }

    public ActionResult generateJSON()
    {
        return Json(db.MenuItems.ToList(), JsonRequestBehavior.AllowGet);

    }

and here is my connection string for the production database

 <connectionStrings>
  <add name="MenuItemDBContext" 
     connectionString="Data Source=localhost\sqlexpress;Initial Catalog=ptafhksz_bonappetit;User ID=ptafhksz_jalal;Password=345d654654Dfdgdfg" 
     providerName="System.Data.SqlClient" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
</connectionStrings>

I am new to asp.net mvc - please advice me how run my app against a a production based DB where I cannot run sql create or drop.

Thanks!

Answer 1

In a shared environment I think it is correct to make each application only have access to its own database and not be able to create a database. Check with the supplier of the shared environment how you do to get a new database up and running. An easy option to get it properly initialized is to send a backup of the db to the server's administrator and ask for the backup to be restored.

EF Code First / Migrations can be made to not auto-create or auto-migrate databases when run in a production environment through a custom db initializer strategy.

public class ValidateDatabase<TContext> : IDatabaseInitializer<TContext>
  where TContext : DbContext
{
  public void InitializeDatabase(TContext context)
  {
    if (!context.Database.Exists())
    {
      throw new ConfigurationException(
        "Database does not exist");
    }
    else
    {
      if (!context.Database.CompatibleWithModel(true))
      {
        throw new InvalidOperationException(
          "The database is not compatible with the entity model.");
      }
    }
  }
}

Enable it with a static constructor in your DbContext:

static MyDbContext()
{
    Database.SetInitializer(new ValidateDatabase<CarsContext>());
}

Answer 2

You don't have admin privileges on your hosting company's SQL server space. The very reason why you may be able to drop but will fail when EF attempts the CREATE DATABASE step. Database initializations are best not done on production apps.

1. If you have access to and done your dev on a full scale SQL Server (not express). Simply make a backup, upload it and restore on your hosting SQL server space.

2. If you have used SQLExpress, you have to generate the SQL script of every object on your data and run it on the hosting SQL server space. Optionally, if you can recreate your database on a full scale SQL Server that would save you the task of generating and executing scripts.

3. Comment out database initialization parts of your code.

4. Deploy your source code to your hosting space.
5. Modify the connection string to point to the database copy at your hosting's SQL server.
6. Pray that everything runs smoothly. :) If not, consider the thought that once you get the app running you just earned yourself a whitebelt on WebAPI/EF project deployment.

Answer 3

The problem here doesn't necessarily appear to be your code.

CREATE DATABASE permission denied in database 'master'

The error message above suggests the problem is your connecting to the database with credentials that don't have the ability to create new items. Try changing the credentials for the initial create or upgrading the capabilities of the account you are connecting with