Configuring Database Authentication for Microsoft SQL Server Applications

.NET applications like those built with Iron Speed Designer must provide authentication credentials when connecting to Microsoft SQL Server databases. .NET offers these access control (user authentication) options:

Windows Authentication. Use the Microsoft Windows user credentials specified by the virtual directory security settings.
- Windows Authentication using the default local ASPNET account.
- Windows Authentication using a domain user account and password configured in the machine.config file.
- Windows Authentication using a domain user account and password configured using .NET impersonation.
Microsoft SQL Server Authentication. Use the name and password of a specific Microsoft SQL Server login account.

Each of these models has advantages and disadvantages for your Microsoft SQL database access. Note that changing the Authentication mode might affect your application security behavior. Refer to “Customizing Security and Authentication” for more details.

Windows Authentication with local ASPNET Account

Pros

Easy for users since no separate application sign-in is required.

Cons

Does not support accessing a remote database, which rules it out for web applications that run on machines separate from their database servers, a common practice.

Does not allow configuring each web application with its own application account that has access to only the data that Web application needs.

Windows Authentication with Domain User Account

Pros

Easy for users since no separate application sign-in is required.

Cons

Violates several security “best practices”, including (a) passwords should not be stored in plain text, such as in a Web.config or Machine.config file and (b) an application should only be able to access data that it needs and no other data. As an example, if you have an application with access to highly sensitive data (e.g., payroll data), this approach would unnecessarily give all other applications (e.g., an order entry application) access to this sensitive data, because it uses the same account to access data for all applications running on that system.

Windows Authentication with .NET Impersonation

Pros

Allows accessing remote databases on separate servers.

Windows Authentication doesn’t require a separate database sign-in, which is easier for most users.

Cons

More difficult to set up.

Microsoft SQL Server Authentication

Pros	Generally more secure since users have their own login credentials for the Microsoft SQL Server database.
Cons	Users must use separate login credentials for Microsoft SQL Server, which is more difficult for many.

ASP.NET Security Model Information

The following link from Microsoft discusses various topics about security, authentication, and impersonation for Microsoft .NET applications. Please note that Iron Speed Designer created applications are standard ASP.NET applications and anything that applies to them will apply to your applications as well.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxoriDistributedApplicationSecurityRecommendations.asp

ASP.NET Impersonation:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxconimpersonation.asp

identity impersonate="false"

Other security related materials

Understanding the Microsoft Windows, Microsoft SQL Server and ASP.NET security models is useful for contemporary application development. We recommend reviewing these materials:

Microsoft Windows security model:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sharepnt/proddocs/admindoc/owsa04.asp

Microsoft SQL Server security model:

http://www.microsoft.com/sql/techinfo/administration/2000/securityWP.asp

http://vyaskn.tripod.com/sql_server_security_best_practices.htm

ASP.NET security model:

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/windowsserver2003/proddocs/server/aaconhowaspnetsecurityworks.asp