SQL Server Authentication

With SQL Server authentication, the IIS application pool will run as a low-credentialed system user, typically the 'NETWORK SERVICE' account. This lets the application pool access the local system resources only:

When you want to connect to a database on a remote server, the application doesn't use this identify, instead it uses the login and password that is configured in the application's Web.config file:

	<connectionStrings>
		<add name="SpiraTestEntities" connectionString="metadata=res://DataModel/SpiraDataModel.csdl|res://DataModel/SpiraDataModel.ssdl|res://DataModel/SpiraDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Password=xxxxxxxxxx;User ID=SpiraPlan;Initial Catalog=SpiraPlan;Data Source=databaseserver;&quot;" providerName="System.Data.EntityClient" />
	</connectionStrings>

However this means you have a hard-coded login and password in the configuration file. Instead, many companies prefer to use Windows Authenticated Security.

Windows Authentication

With Windows authentication, the IIS application pool will run as an Active Directory domain user instead. This lets the application pool access the local system resources and also any granted resources on external computers, for example the database server.

In this case, you will need to change the IIS application pool to run as a Active Directory user instead:

When you want to connect to a database on a remote server, the application will use this identify to make the connection, instead of using an identity hard-coded in the  Web.config file. That file would now look like:

	<connectionStrings>
		<add name="SpiraTestEntities"
			connectionString="metadata=res://DataModel/SpiraDataModel.csdl|res://DataModel/SpiraDataModel.ssdl|res://DataModel/SpiraDataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=databaseserver;initial catalog=SpiraPlan;integrated security=True;multipleactiveresultsets=True;App=EntityFramework;Connection Timeout=30&quot;"
			providerName="System.Data.EntityClient" />
	</connectionStrings>

This means that the domain user (e.g. DOMAIN\myuser) needs to have a login inside SQL Server that has appropriate permissions to the Spira / KronoDesk website.

This would look something like this:

Typically that user will have db_owner permissions of the database in question:

Also this user will need to have the appropriate permissions on the local webserver to match what NETWORK SERVICE normally has access to:

  • Read + Execute permissions on the Spira/KronoDesk program files
  • Full Control permissions over the folder where attachments are stored.
  • Run as Service permissions to be able to used as a service account.