Thursday, January 14, 2016

Deploy ASP.NET MVC 5 application to IIS using SQL Server 2014 LocalDb

Although not used in production scenarios, it is sometimes desirable to use a localdb database server with ASP.NET MVC 5 hosted by IIS. This post will help you setup this environment.

Download and install localdb 2014 and SQL Server 2014 Express

1) Download SQL Server Express 2014 from http://www.microsoft.com/en-us/download/details.aspx?id=42299
2) Choose the version that includes SQL Server Management Studio: ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
image
3) After you download SQL Server 2014 Express, extract launch the .exe file.
image
image
image
image
NOTE: If you do not see “LocalDB” the the features list, then after completing the current installation, return back to the download page and install “LocalDB 64BIT\SqlLocalDB.msi”.
image
image
image
I entered P@$$w0rd for the sa account. You can, of course, enter any other password. Make sure, though, that you remember this password as it is the admin account for SQL Server authentication.
Finally:
image
4) It is recommended that you pin the following applications to your OS taskbar:
  • SQL Server Management Studio
  • Internet Information Services (IIS) Manager

Deploy ASP.NET application with LocalDB to Full IIS

The following assumptions are being made here:
  • we are deploying a web application to a physical directory at C:\inetpub\wwwroot\MyWebApp
  • the website name in IIS is MyWebApp
  • we will access the site internally (on the server) using http://localhost:8055
  • we will access the site externally using host header asp.acme.com. This means that the website’s external URL is http://asp.acme.com.
1) Create a directory at C:\inetpub\wwwroot\MyWebApp
2) In IIS, create a new web site named MyWebApp and set the physical IIS path to the above directory.
  • To start with, use a port number like 8055 and no host header. This helps you trouble-shoot problems because detailed errors are displayed when accessing the website using localhost on any port number.
  • The name of the web site (in this case MyWebApp) determines the name of the application pool.
3) Configure Application Pool
  • In IIS, identify the application pool account by clicking on the “Application pools” node
  • Right click on the application pool account and select “Advanced Settings
image
  • Set “Load User Profile” to True.
image
  • Click OK
4) Publish site from Visual Studio
  • In VS.NET, open the solution. Right click the web project and choose Publish…
image
  • Choose Custom then give it a profile name.
image
  • On the next dialog, choose File System then set the “Target location” to a physical location somewhere on your computer’s file system
image
  • Click Publish:
5) Ensure that data files are published: Inspect the publish target directory. If the App_Data directory does not contain any files, do the following:
  • Return to Visual Studio and make sure that the database files in the App_Data directory are included in the project
  • In Visual Studio, select the App_Data folder. Right click and select “Publish App_Data”. This will copy over the database files to C:\inetpub\wwwroot\MyWebApp
6) Copy all the files in your publish target directory.
image
7) Paste the  files into the website directory on the IIS server. In the above example this would be: C:\inetpub\wwwroot\MyWebApp
image
8) Give C:\inetpub\wwwroot\MyWebApp\App_Data the appropriate access privileges:
  • In File Explorer, navigate to C:\inetpub\wwwroot\MyWebApp.
  • Right-click App_Data.
  • Select Properties >> Security tab
  • Click Edit
  • Click Add… on the “Permissions for App_Data” dialog
  • In the “Enter the object names to select” field, enter “iis apppool\MyWebApp”. Note that MyWebApp is the name of the application pool in question. This represents the “ApplicationPoolIdentity” assigned to our application pool.
  • Click on Check Names to verify that the account exists
image
  • Click OK
  • Select all permissions except “Full Control”
  • Click OK twice
5) If you are using LocalDB, ensure that the signature for the data source is “(LocalDb)\MSSQLLocalDB”’. Here’s a typical connection string that works with SQL Server Express 2014 LocalDB:
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB; Initial Catalog=my-catalog; Integrated Security=SSPI; AttachDBFilename=|DataDirectory|my-data-file.mdf;" providerName="System.Data.SqlClient" />
6) Testing that site works as expected.
  • Return to IIS
  • Select MyWebApp website
  • On the right-hand side click on Browse *:8055
  • When the website opens in a browser, carry out a task that involves database access (like registering a user). If all goes well and there are no errors then you will know that all is good. Otherwise, a detailed error message will display since we are using localhost.
7) Enabling external access to the website:
  • In IIS, click on the website
  • Click on Bindings on the right-hand side
  • Click on the Add button
  • Enter asp.acme.com for host name
image
  • Click OK
image
  • Click Close