EF Code First How To Configure Database Credentials Correctly
I have an MVC4 developer web app + EF Code First + SQL Server 2008. Uploaded it to prod server using IIS7. New credentials have been created PC
. Added empty database PCDB
on SQL Server and assigned to PC user with owner rights. When I run the web application I get the error
Model compatibility cannot be verified because the database does not contain model metadata. Model compatibility can only be checked for databases created using the first codes of the code or the first transfer of the code.
My connection string
data source=174.xx.x.x;initial catalog=pcdb;user id=pc;password=xxxxx;
The exception is clear, I can drop the database PCDB
and let EFCode create it on its own first. But what about credentials PC
? I don't want the user to be PC
an administrator, but without it, EF Code First won't be able to create a new database on SQL Server.
How to solve a problem?
source to share
Overall design starts with form validation or Windows validation on the WebsiteAp.NET/IIS website. and ends with required / required application and database authentication. Application authorization is another topic. I will not discuss this here.
The desired authentication model is not actually specified. So I'll start with a disclaimer. This is the suggestion I would use in a production site. But this is not the ultimate end game, and it is not the only short term solution you might consider.
This is a solution that one person's work can show . And keep working safely and without undue administrator effort.
Use Windows Auth Login to SQL Server
BUT you don't need to add every user to SQL Server.
There is also the possibility of impersonation. But it can get tricky and this explanation is not impersonating NOT. This is another approach.
first make sure the website is using Windows Authentication
install IIS to use Windows Authentication:
Now the APP pool is behind the website in IIS that you configured ..
I'm going to offer a Psuedo service user in the APP pool as a good way to get started . those. WEBAPPLICATION_X_USER. You can have a separate user in the APP pool. Each user can only access his own database. This way you get application separation. Enter your password and password here. IIS will encrypt and decrypt as needed. (better than scheduling text in Web.config)
This user must have an auth shorthand on the server itself. NOT a domain ADMIN user or even a local administrator. Enough for it to be able to use Sql Server to create the DB. Create a regular Windows user
Let ASP.Net enter the DB. Let ASP.net encrypt and decrypt the password.
So now the situation is on Windows AUTH on IIS. IIS has an application pool with a dedicated Windows user that can connect to the SQL server. You have added this user to your SQL Server instance and allocated this service user to create the database. Don't give the user access to all Dbs :-) Only the one he creates. Plus open access (via EF).
Check user credentials in your web app. See [System.Security.Principal.WindowsIdentity] . This should show a Windows authenticated end user.
System.Environment.UserName must have the service user ID that you placed in the IIS APP BAP.
Now when an EF is sent to create or access data on an instance of SQL Server, it will bind to System.Environment.UserName if the WEB.CONFIG entry is set to use Windows Integrated Security
<connectionStrings>
<add name="DbContextName" connectionString="Data Source=Your SQL server Instance;Initial Catalog=The DBNAME;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
And you KNOW the authenticated user . httpContext will provide it for you as well as the current current thread . HttpContext.User maps to {System.Security.Principal.WindowsPrincipal} by default
So you can do application level validation. The same approach should work with forms authentication as well .
WARNING: If you have a Windows WPF approach (i.e. you are not using IIS and therefore no APP pooling) then this approach MUST be changed and more complex and no longer the best place to start.
Hope this helps you get started
source to share