Receiving an error doing code first MVC web deployment

Question

I have set up a remote server running SQl Server Web edition and full IIS 7.5. I am able to deploy my MVC 4 application to the remote box without issue, but when I try to run the website I receive the following error:

Login failed for user 'IIS APPPOOL\backoffice.mysite.com'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'IIS APPPOOL\backoffice.mysite.com'.

The Error Console says:

    Exception information: 
    Exception type: InvalidOperationException 
    Exception message: The ASP.NET Simple Membership database could not be initialized. For more information, please see http://go.microsoft.com/fwlink/?LinkId=256588
   at MyModels.Filters.InitializeSimpleMembershipAttribute.SimpleMembershipInitializer..ctor() in c:\Users\Administrator\Documents\MySite\MyModels\Filters\InitializeSimpleMembershipAttribute.cs:line 47

An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

It is very possible that my connection string is incorrect as the error log suggests. My connection running locally in Visual Studio is:

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

I have set up a transformation for the connection on the remote server as:

<add name="MyDB" connectionString="Data Source=(local);Initial Catalog=MyDb;Integrated Security=True" providerName="System.Data.SqlClient" />

1. Normally I use SQL login so I am confused here. What should the connection string look for SQL Server Web Edition using Windows Authentication?

2. It looks like the IIS APPPOOL\backoffice.mysite.com is the trying to create the DB. Is this correct? If so do I not need to create a login and user for this user in SQL Management Studio?

3. Do I need to create the DB in SQL Management Studio before running the deployment?

Thank so much!

Answer 1

you are running using Windows Authentintication for SQL Server. The IIS App is running under the user context of 'IIS APPPOOL\backoffice.mysite.com'; I would strongly suggest using either standard sql server credentials or creating a new user for that particular website that has sql credentials, probably better with regular credentials though. Integrated Security=SSPI in the connection string means Windows Authentication. So just use standard SQL Server credentials and set your database to mixed mode auth and set the User ID and Password in your connection string and then you will be up and running no problem.