Frequently Asked Questions

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

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.

Back to the top

APPLIES TO

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

MORE INFORMATION

• How To: Connect to SQL Server Using Windows Authentication in ASP.NET

Back to the top