We have a webapp running on IIS that is using SQL authentication for accessing it's database. We want to change this to Integrated Windows Authentication.

When working with Integrated Windows Authentication we want to create a service account that is excluded from your normal password policy:

• Create a service account called sa_appname
• Set a description
• Set a password

Usually when working with Integrated Windows Authentication the vendor of your application will know exactly which permissions to assign to the service account used to access the database. In case you don't have this information or you're still testing the whole setup you could usually assign db_owner role to the user.

To do so, in SQL Management Studio follow these steps (notice, that these steps might change per SQL server version) :

• In Object Explorer, expand the server in which you want to edit a fixed server role.
• Expand the Security folder.
• Expand the Server Roles folder
• Right-click the role you want to edit and select Properties.
• In the Server Role Properties -server_role_name dialog box, on the Members page, click Add.
• In the Select Server Login or Role dialog box, under Enter the object names to select (examples), enter the login or server role to add to this server role. Alternately, click Browse… and select any or all of the available objects in the Browse for Objects dialog box. Click OK to return to the Server Role Properties -server_role_name dialog box.
• Click OK.

It should normally not be required to add the serviceaccount to the local admin group on the IIS server, but I've had cases in which this was required, so in case you're testing and get error you might want to try this:

In Server Manager, follow these steps:

• Click Tools → Click Computer Management
• Navigate to Local User and Groups → Groups → Open the Administrators group
• Click Add to add the sa_appname to the local admin group

The connection strings are setup in the web.config file of the webapp on IIS. So in the IIS server, follow these steps to change the config:

• First determine the webapp root on the filesystem. This is usually somewhere like C:\inetpub\wwwroot\appname but you check the exact setting by going into IIS Manager, selecting the website and clicking “Basic Settings”. This will show you (a.o.) the physical path of the website.
• Once you located the web.config, open up Notepad (or your favorite text editor) as an Administrator and open the web.config file. Note that you need to set the document type to “All File (*.*)” within notepad to be able to see and open the file

In the file the connection strings are usually located at the top, and for DB Connections they ususally look like this:

<add name="DBConnection" connectionString="Data Source=WSQL-P01.getshifting.com\SQL01;Initial Catalog=Shifthub;Persist Security Info=True;User=shifthub;Password=EasyPass01!"/>

Notice that the password for the sql user shifthub is in plain text and visible to anyone with read access. Change the connection string in: <add name=“DBConnection” connectionString=“Server=WSQL-P01.getshifting.com\SQL01;Database=Shifthub;User Id=sa_appname;Trusted_Connection=True”/>

Save the file when you're done.

You have a few configuration steps to take in IIS Manager to set this up completely. In IIS Manager, follow these steps:

• In IIS Manager go to Sites → Select the correct site
• Under the IIS section in the main pane, doubleclick on Authentication
• Click on Windows Authentication and click on Enable under Actions

• In IIS Manager go to the IIS Server (identifiable by it's system name)
• Under the IIS section in the main pane, doubleclick on Authentication
• Click on Windows Authentication and click on Enable under Actions

• In IIS Manager go to Application Pools → Select the correct Application Pool
• Click on Advanced Settings under the Actions pane
• Scroll down and select the Identity setting under Process Model and click the small box with three dots next to it
• Select Custom Account and click set
• Enter the service account name in domain format, so for example: shift\sa_appname and provide the password
• Save your changes

• In IIS Manager go to Sites → Select the correct site
• Click “Restart” under the Actions Pane

You can test the configuration in IIS Manager:

• In IIS Manager go to Sites → Select and expand the correct site
• Select the home object (identifiable by the globe icon with a document in front of it)
• Click on Basic Settings under the actions pane
• Click on “Test Settings”

Now a test for the authentication (service account credentials) and authorization (file access) will be done.

• SQL Roles: https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/
• Assign SQL Roles: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/join-a-role?view=sql-server-2017
• IIS Windows Authentication: https://docs.microsoft.com/en-us/iis/configuration/system.webserver/security/authentication/windowsauthentication/