Frequently Asked Questions

Version 2 - Reviewed: September 6, 2017

Using Windows Authentication between TariffShark Application Server and Database

Objective

The objective of the article is to guide an IT administrator through the processing of setting up TariffShark Application Server (ASP.NET) to use Windows authentication when connecting to the TariffShark Database (Microsoft SQL Server).

Overview

When you use Windows authentication to connect to SQL Server, you use your application's process identity or thread identity (if your application is using impersonation) to connect. You should use Windows authentication to connect to SQL Server when possible for the following reasons:

  • No credentials are passed over the network during authentication and you do not need to embed user names and passwords in the database connection string. This means that malicious users or attackers cannot obtain the credentials either by monitoring the network or by viewing connection strings inside your configuration files.
  • You benefit from centralized account management. User accounts are subject to normal account management security policies such as password expiration periods, minimum lengths, and account lockout after multiple invalid logon requests

Back to the top

Configuration Steps

Step 1. Configure a Connection String

<configuration>
  <appSettings>
    <add key="Main.ConnectionString" value="data source=$SERVER$;initial catalog=$DATABASE$;Integrated Security=SSPI;"/>
  </appSettings> 
</configuration> 

Step 2. Configure SQL Server Security

You need to create a SQL Server login for your application's service account, and grant DBO permissions to access the TariffShark database.

USE [master]
GO
CREATE LOGIN [DOMAIN\testuser] FROM WINDOWS WITH DEFAULT_DATABASE=[TariffSharkTestData]
GO
USE [TariffSharkTestData]
GO
CREATE USER [DOMAIN\testuser] FOR LOGIN [DOMAIN\testuser]
GO
USE [TariffSharkTestData]
GO
EXEC sp_addrolemember N'db_owner', N'DOMAIN\testuser'
GO 
  1. Create a Windows domain account.
  2. In an elevated CMD prompt, navigate to C:\Windows\Microsoft.NET\Framework\ and run the following Aspnet_regiis.exe command to assign the relevant ASP.NET permissions to the account:

    aspnet_regiis.exe -ga domainNameOrMachineName\userName

    On Windows, running the Aspnet_regiis.exe -ga command adds the account to the IIS_IUSRS group. Membership in the IIS_IUSRS group grants the account the Log on as a batch job permission and ensures that the necessary file system and IIS metabase permissions are granted. If for some reason this is not done, please add the account to the IIS_IUSRS group.

  3. Use the Local Security Policy tool to grant the Windows account the Deny logon locally user right.
  4. Use IIS Manager to create an application pool running under the new account's identity and assign the TariffShark Application Server's ASP.NET application to the pool.

Create a SQL Server Login for a Windows Group

Rather than create a SQL Server login for the network service account or your custom service account directly, add this account to a Windows group on the database server and then create a SQL Server login for the Windows group. This is better from an administration perspective and shields you from potential changes to the application's account identity.

Back to the top

More Information

APPLIES TO

  • ASP.NET
  • Microsoft SQL Server
  • Microsoft Windows Server operating system

MORE INFORMATION

Back to the top