Microsoft SQL Server Application Role
When connecting to a Microsoft SQL Server, there are two ways of authenticating the user: Database Authentication and Windows Authentication. Database Authentication uses a login and password stored by the database whereas Windows Authentication uses the current logged in Windows credentials from Active Directory to authenticate the user.
Windows Authentication, while considered the best approach, does come with a significant drawback; when a user connects to an SQL server and is authenticated by their Windows credentials, the SQL server does not understand if they are connecting from an application, a reporting tool, or Microsoft Excel. This is an often overlooked security issue when using Windows Authentication.
Example
Loan Servicing Manager, John Smith, connects to an SQL server using an application that has the ability to update rows on a table. What happens when John Smith connects to the SQL server using Microsoft Excel? The connection will contain the privilege to update rows on that table.The solution to this issue is Microsoft SQL Server Application Role. An application role is a user defined role configured by an administrator that contains specific security privileges. The application role is defined with a password that is granted to a user by an application at startup. The Windows user defined in the SQL server would contain limited connect privilege and would inherit all privileges defined in the application role at application startup.
In Nortridge Loan System this functionality is enabled within System Defaults by setting a role name and password. If a role name and password are defined, NLS will enable the application role for the user after the initial connection at the startup of the application. This functionality greatly helps security administrators keep authentication simple while still having complete control of database security privileges.
