When connecting to 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.
This second manner of authentication, while considered the best approach, does come with a significant drawback. When a user connects to SQL Server and is authenticated by their Windows credentials, SQL Server doesn’t 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. For example, Loan Servicing Manager John Smith connects to SQL Server using an application that has the ability to update rows on a table. What happens when John Smith connects to 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 Roles. An Application Role is a user defined role defined 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 start up. The Windows User defined in SQL Server would contain limited connect privilege and would inherit all privileges defined in the Application Role at application startup.
In the Nortridge Loan System (NLS) 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.
