One of the features of EF CodeFirst is the ability to automatically drop and recreate a database if the model changes or if the database does not exist, which is pretty useful when just doing development work.
In a web app this is done in the Global.asax file within "protected void Application_Start()" as per this post from Scott Guthrie - http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx. An example would be:
However when using the Database.SetInitializer and trying to rebuild the database, the following exception may be encountered, especially if using SQL Server Express:
Getting around this 'master' database exception is fairly straightforward - add the Persist Security Info property to the connection string and set the value to true.
e.g.
Credit: Thanks to Kevin McLean from http://www.northgatesystems.co.uk/ for pointing this fix out in the comments on the blog post mentioned above.
Addendum 05/04/2011
Persist Security Info=True should not be set in a production environment. As the above is really only a "fix/hack" with a view to the development phase or getting going with EF Code First, and taking advantage of EF CodeFirst dropping and rebuilding the database. In a production environment Persist Security Info should be set to FALSE.
From MSDN Security Guidelines (http://msdn.microsoft.com/en-us/library/ff647552.aspx)
In a web app this is done in the Global.asax file within "protected void Application_Start()" as per this post from Scott Guthrie - http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx. An example would be:
protected void Application_Start()
{
Database.SetInitializer<yourdbcontex>(new DropCreateDatabaseIfModelChanges<yourdbcontext>());
AreaRegistration.RegisterAllAreas();
RegisterGlobalFilters(GlobalFilters.Filters);
RegisterRoutes(RouteTable.Routes);
}
However when using the Database.SetInitializer and trying to rebuild the database, the following exception may be encountered, especially if using SQL Server Express:
"This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection."The application is trying to access the master database to read the structure of the database used by the application, but the connection string used will not allow it.
Getting around this 'master' database exception is fairly straightforward - add the Persist Security Info property to the connection string and set the value to true.
e.g.
Server=YourServerName\SQLEXPRESS;Database=yourDatabase;User ID=yourDBUser;Password=yourDBPassword;Trusted_Connection=False;Persist Security Info=TrueCredit: Thanks to Kevin McLean from http://www.northgatesystems.co.uk/ for pointing this fix out in the comments on the blog post mentioned above.
Addendum 05/04/2011
Persist Security Info=True should not be set in a production environment. As the above is really only a "fix/hack" with a view to the development phase or getting going with EF Code First, and taking advantage of EF CodeFirst dropping and rebuilding the database. In a production environment Persist Security Info should be set to FALSE.
From MSDN Security Guidelines (http://msdn.microsoft.com/en-us/library/ff647552.aspx)
If you must supply a user ID and password when making a connection, you should make sure that this information is discarded after it is used to open the connection. This occurs when Persist Security Info is set to "false" or "no".
Setting the Persist Security Info keyword to "true" or "yes" in a connection string allows security-sensitive information, including the user ID and password; to be obtained from the connection after the connection has been opened.
Keeping Persist Security Info as "false" helps to make sure that the untrusted source does not have access to the security-sensitive information for your connection and also helps to make sure that no security-sensitive information is saved to disk with your connection string information. Persist Security Info is set to "false" by default.
Thank you sir..just ran into this.
ReplyDeleteYep, Cheers for the heads up. Connection management in Ef 4.1 is a PITA.
ReplyDeleteAny idea why this happens when using SQL CE4?
ReplyDeleteHi Shawn,
ReplyDeleteEF Code First uses the master database to determine if the database for your model exists.
This is a basic version and as far as I understand it:
The check of the state of the database compared to the model, happens when a call to the database is made (e.g. SELECT, INSERT, etc).
So the query is built and executed, THEN a check to see if the database needs to be updated if the model has changed. This check includes querying the master database.
As the query has already been made for the data, the same connection can not be used to make a second query about the state of the model.
Therefore either the database needs to be checked and initialised if necessary, beforehand using another methodolgy, or the security information needs to be persisted for all queries within an open connection.
Hope that waffle makes some sense and helps explain a little bit!