Configuring IIS, ASP.NET, and SQL Server

Introduction

First things first: this article is written from my own experience. It should be used as a guideline only and it really should not be used for production machines. You can use this article to guide you through the basic steps required to get up and running.

For demonstration purposes, I'll be using Microsoft Windows 10 Pro, Microsoft IIS and Microsoft SQL Server 2014. If you are using the Express edition of SQL Server, these steps should still apply however I cannot guarantee that everything in this article will be available for you.

I do apologise for not adding any screenshots, but I will add them in due course. :) Please keep an eye on this page.

IIS Express vs IIS

Since Visual Studio 2012, Microsoft has introduced a lightweight version of IIS called IIS Express. Normally, while you are developing an application or website, you can use the debugger (F5) to run your solution and then catch errors. While this is a very convenient way of testing your solution, I like to "simulate" real life situations during my development cycle. This ultimately allows me to encounter and experience errors in the same fashion a normal user would. So, I opt to use the full hosted version of IIS as opposed to the Express edition.

System Requirements

This article requires that you:

• Have installed Microsoft SQL Server and all related services.
• Have installed Microsoft IIS:
  • For client editions of Windows: Open Control Panel -> Programs and Features -> Turn Windows features on or off.
  • For server editions of Windows: Open Server Manager -> Add Roles and Features (the IIS Hostable Core is not needed for development purposes).

Configuring IIS

Remember, these settings are for development/inhouse use and not for production use. You can use it as a guideline but you must substitute where needed depending on your setup/requirements.

1. Open the IIS management console (shortcut: Start -> Run -> inetmgr).
2. Select Application Pools on the left and locate the application pool that your website/application is running under in the middle. From there, right-click and select 'Advanced Settings'.
   1. Ensure that the Managed Pipeline Mode is set to Integrated. We don't use the classic pipeline anymore and it shouldn't be used unless you know what you are doing!
   2. Scroll down in the list until you see the setting Identity. Click the ... button and select the account you want to use. For the purpose of this article, we will be making use of the Network Service account that will allow all the permissions you need without exposing your system too much. Please do some reading on the Network Service account and other accounts that are available to you. If your system is connected to AD, you might need to consult your System Administrator for assistance. In my experience, I've worked with AD machines before that do not allow the Network Service account to be available.
   3. Now click OK and then OK again. You've now configured the application pool.
3. Expand Sites on the left and select the website/application you need to configure.
   1. Double-click on the 'Authentication' tile. Make sure that:
      1. Anonymous Authentication is Enabled and the rest Disabled. This is also based on your requirements so it may not be the same as here.
      2. Right-click on Anonymous Authentication and select Edit.
      3. Change the identity to 'Application Pool Identity'.

The above should put your application/website in a workable state for IIS. You may not be able to access it yet so continue reading the rest of the article.

Directory Permissions

As standard practice, all our websites/applications (collectively "sites" from hereon forward) are placed in the default IIS hosting directory, i.e. C:\wwwroot\inetpub.

1. Go to the hosting directory using Windows Explorer.
2. Right-click the folder of your site and select Properties.
3. In the Security tab, grant the following permissions to the account you have selected in IIS (NetworkService for me). If the account is not in the list at the top, make sure that you add it first.
   • Modify
   • Read & execute
   • List folder contents
   • Read
   • Write

Note: You may want to consider using the advanced security editor to enable propagation of security objects to all child items. This will just ensure that any new file or directory created within the root directory will inherit their permissions from their parent object. It really just makes everything easier - for this article, we don't really need it so we won't be discussing it. Feel free to leave a question and I'll get back to you.

Configuring SQL Server

From here, you will see that your site cannot access or communicate with the database engine. Why? Because it does not have the permissions required to do so. Now there are two methods of doing this, each comes with its own set of advantages and disadvantages; so in short:

• Database-only Access: You can configure the system account (NETWORK SERVICE) to access specified databases only. In this scenario, the account will only be allowed to communicate with the database(s) as specified. I suppose this provides a little bit more piece of mind in terms of security. This is normally the method I prefer. I love restricting people and software from doing things they're not supposed to.
• Server-wide Access: You can configure the system account (NETWORK SERVICE) to access the entire database server including all databases attached to that instance/engine. This method is not as secure as the above mentioned one and I strongly don't recommend using this.

Database-only Access

If you opt to use this method, you need to take into consideration that everytime you add a new database, you will need to go and configure the permissions for that database. It can become quite a tedious process if you need to configure permissions for databases on a weekly basis.

1. Open SQL Server Management Studio (shortcut: Start -> Run -> ssms)
2. Connect when prompted.
3. Expand Security and then expand Logins.
4. If you don't have the network service listed (should be NT AUTHORITY\NETWORK SERVICE):
   1. Right-click on the Logins folder and select New Login.
   2. At the top, in the 'Login Name' field, enter NETWORK SERVICE. If it refuses to accept that, try entering NT AUTHORITY\NETWORK SERVICE.
   3. Now select the Server Roles tab on the left.
   4. You can tick any role you like, but for me I will give it 'public' access.
   5. Now select the User Mapping tab on the left.
   6. Tick all the databases you want to allow this service account to access.
   7. In the Schema column for each selected database, set the value to dbo (or whatever schema you are using in your database).
   8. Then, select one database row at a time and set the following permissions for it:
      1. db_datareader
      2. db_datawriter
      3. public
   9. Now click OK.
5. If you do have the network service account listed, edit that login entry and then follow steps (5 - 9) above.

That's it for the engine-level. You still need to add the service account to each and every database that you have selected initially as in step 6 above. So:

1. Expand Databases on the left.
2. Expand the Security folder and then expand Users.
3. The service account should be listed there. Right-click and Properties on the service account (for us, NT AUTHORITY\NETWORK SERVICE).
4. Select the Securables tab on the left.
5. Click on the Search button.
   1. Select 'Specific Objects' and click OK.
   2. Now click the Object Types button.
   3. Scroll down and tick Schemas. Click OK.
   4. In the textbox below, enter the schema you are giving access for (the same as Step 7 above). In our case, it will be dbo. Click OK.
   5. At the bottom, select all the permissions you want to give for that database. In my case, I need quite extensive access to my database so I will be selecting these permissions:
      1. Alter
      2. Control
      3. Create Sequence
      4. Delete
      5. Execute
      6. Insert
      7. References
      8. Select
      9. Update
6. Now click OK.

Repeat the above steps for each and every database that you have selected.

Server-wide Access

1. Open SQL Server Management Studio (shortcut: Start -> Run -> ssms)
2. Connect when prompted.
3. Expand Security and then expand Logins.
4. If you don't have the network service listed (should be NT AUTHORITY\NETWORK SERVICE):
   1. Right-click on the Logins folder and select New Login.
   2. At the top, in the 'Login Name' field, enter NETWORK SERVICE. If it refuses to accept that, try entering NT AUTHORITY\NETWORK SERVICE.
   3. Now select the Server Roles tab on the left.
   4. You can tick any role you like, but for me I will give it 'public' access.
5. Click OK.

That should be it for the engine level.

History

• 28/10/2013: Better, effective and more detailed instructions on configuring IIS. Included IIS 8.0 and 8.5
• 21/10/2015: Improved article