Moving from IIS Express to IIS and Sql Server

Moving from IIS Express to IIS was a natural decision for many reasons like not having to launch IIS Express for the Web Service every time we wanted to run the web site. This way, the Web API Service can always run until modification is required. It has not been as a simple switch to IIS by creation new website under IIS. This require to have some change. First, we were using localdb. We could continue to use localdb but it would have require us to change the credential of the running Application Pool to use my credential. The reason is that localdb is under the user profile. The best solution at this point is to run the database inside a real Sql Server.

Cannot get a local application data path. Most probably a user profile is not loaded. If LocalDB is executed under IIS, make sure that profile loading is enabled for the current user.

Next warning that you will get is that the Application Pool does not have access to the database.

Process information:
Process ID: 7732
Process name: w3wp.exe
Account name: IIS APPPOOL\BourseVirtuelle

Exception information:
Exception type: SqlException
Exception message: The SELECT permission was denied on the object ‘AspNetUserLogins’, database ‘BourseVirtuelle’, schema ‘dbo’.
at System.Data.SqlClient.SqlCommand.b__24(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.Execute()

This can be done by going into Microsoft Sql Server Manager, under Security, Logins and add entries for all your Application Pool. For example, here is the application pool that I created.

As you can see, the Identity is kept to Application Pool. This is because I did not wanted to create a full new user in my Windows machine. In Sql Server Manager, I can now create a login and associate it to this Application Pool.

You need to have the user name with the IIS APPPOOl\ in front of your Application Pool name. You have also to keep Windows Authentification checked.

The last step is to give the user access to the Database. You have give the account enough role to be able to run you application.

The last error that you can have is something about the creation of table. I had this error when migrating this website with the Identity framework when trying to get the user in the authentication window.

Exception information:
Exception type: SqlException
Exception message: CREATE TABLE permission denied in database ‘BourseVirtuelle’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

The solution was to set the default initializer to null

public MainDbContext(IRunningContext runningContext) : this()
 Database.SetInitializer<MainDbContext>(null); //No initialization

More about Patrick Desjardins

Leave a Reply

Your email address will not be published. Required fields are marked *