none
EF 6.0 Code Frist & MS SQL2012 RRS feed

  • Question

  • Today, I upgraded from .net 4.0 and EF 4.3 to .net 4.5 and EF 6.0 RC. I dropped the existing database and compiled successfully.

    I am using mvc4, C#, VS 2012 and MSSQL 2012 standard edition on my development computer.  

    I am getting the following error message:

    Directory lookup for the file "xxxx.mdf" failed with the operating system error 5(Access is denied.).
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    On: db.Database.Initialize(force: false); 

    my web.config contains:

      <configSections>
        <section name="facebookSettings" type="Facebook.FacebookConfigurationSection" />
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
      </configSections>

    .

    .

      <connectionStrings>
       
        <add name="MyContext1" connectionString="Data Source=My-PC\SQL2012;Initial Catalog=Mydb1;Integrated Security=SSPI;MultipleActiveResultSets=True;AttachDBFilename=|DataDirectory|Mydb1.mdf;User ID =sa;Password=mypassword" providerName="System.Data.SqlClient" />
        <add name="MyContext2" connectionString="Data Source=My-PC\SQL2012;Initial Catalog=Mydb2;Integrated Security=SSPI;MultipleActiveResultSets=True;AttachDBFilename=|DataDirectory|Mydb2.mdf;User ID =sa;Password=mypassword" providerName="System.Data.SqlClient" />
      </connectionStrings>
    .

    .

        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="Data Source=My-PC\SQL2012; Integrated Security=True; MultipleActiveResultSets=True;" />
          </parameters>
        </defaultConnectionFactory>
        <providers>
          <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        </providers>

    I had no issues initializing the database with .net 4.0 and ef 4.3.  I am looking forward to take advantage of the new features of EF 6.0 and .net 4.5 but this is stopping me from progress.  Your help is truly appreciated,  thank you.


    • Moved by CoolDadTx Tuesday, August 27, 2013 7:32 PM EF related
    Sunday, August 25, 2013 10:18 PM

All replies

  • This error has nothing to do with EF.  Your connection string has AttachDbFilename, which instructs your SQL Server instance to mount that file as a database when you connect.  When SQL Server does this it needs NTFS permissions to the folder to be able to read and write to the database file and create the log file.  

    The permissions should be granted to the principal NT Service\MSSQL$SQL2012, which is the per-service SID/Virtual Account for your SQL 2012 named instance.

    Also since this is an MVC applicaiton, you can just copy the database file to the SQL Server data directory and mount it permanently unless you are planning to deploy to a web server with SQL Server installed on it.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Sunday, August 25, 2013 10:23 PM
  • Hi Dave, thanks for your replay. 

     there is no .mdf file generated to mount, in addition, it will not work well if I need to enhance the database model, which I do frequently as the requirement evolves.

    Yes I understand the message and I realize it is generated from the database; however, it is in respond to Code First EF/ADO/MSSQL.

    The connection string in web.config file  is valid and works with .Net 4.0 and EF 4.3. ,

    I had deleted the database and I was  depending on EF Code First to generate the database as I frequently had previously done with EF 4.3 - Code First.   I was planning to use migration as a second step to enhance the database.

    As I previously mentioned,  I made to changes to the project on my personal computer 1) upgraded .net 4.0 to 4.5 and EF. 4.3 to 6.0. 

    For the last 2 years,  I've had no issues with deleting  the database and relying on Code First to creating database whenever I enhanced POCO Classes. 

    The problem resides between database provider that translates from Code Firs EF when creating the database or when initializing the database.

    I had copied and renamed the .net 4.0/EF 4.3 project on the same PC, I dropped the database and ran the application, the database was generated without any problem.  I would appreciate it if you would reconsider your respond.  Thank you, Zaia
    Monday, August 26, 2013 3:12 AM
  • I just built a simple MVC 4 + EF6 app.

    I created a simple DbContext model:

    namespace MvcApplication1.Models
    {
      public class CarContext: DbContext
      {
        public CarContext() : base() {}
        public CarContext(string cn) : base(cn) { }
    
        public DbSet<Car> Cars { get; set; }
      }
      public class Car
      {
        public int ID { get; set; }
        public string Name { get; set; }
      }
    }

    My web config has a connection string entry like this:

      <connectionStrings>
        <add name="DefaultConnection" providerName="System.Data.SqlClient" 
             connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-MvcApplication1-20130826092422;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-MvcApplication1-20130826092422.mdf" />
      </connectionStrings>

    On run SQL Server creates an .mdf and .ldf in my App_Data folder, as instructed by the connection string.  If you use a connectionString without AttachDbFileName, then SQL Server will create the database in its data directory.

    Either deployment model works with EF Code-First.  EF with Code-First Migrations can create, delete and update a database deployed in your App_Data folder or a database deployed in SQL Server's data directory.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, August 26, 2013 2:47 PM
  • Hi Dave,

    Please note in my first posting,  connectionString contains AttachDbFileName.

    I thank you for your help, I have resolved the problem. 

    As you initially suggested I opened security tab  of project folder properties; added NT Service\MSSQL$SQL2012  to "Groups user Name" and granted it all the privileges.

    The error message I first posted

    "Directory lookup for the file "xxxx.mdf" failed with the operating system error 5(Access is denied."

    disappeared.

    A second error message, the real one bubbled up.  Foreign Key Required Field violation.  I discovered that I had added a [Required] attribute to a property a POCO  class which contained no data when seeding the database at initialization phase of database creation.  I fix this and reran the application without any problem.

    Just as a background, the application has over 90 POCO classes within  dbContex class which also calls other classes that seeds the database with large amount of data at creation time.

    However, I was curious, for this was not the first time I had ran into Required Field Violation while developing in .Net 4.0 and EF 4.3.

    I went back and removed NT Service\MSSQL$SQL2012 and the privileges from the security tab of the project folder properties and reran the application; it ran without any problem.

    I am under the impression that there is a problem with how error messages are prioritized and it is misleading.  Had I got the second error message first I would have fixed the problem in a second.  I can reproduce the problem if someone is interested to fix the error reporting issue.

    Thank you, Zaia
    Tuesday, August 27, 2013 7:09 PM