When granting a user access to a database there are a few considerations to be made with advantages and disadvantages in terms of usability and security. Here we have two options for authenticating and granting permission to users. The first is by giving everyone the sa (systems admin) account access and then restricting the permissions manually by retaining a list of the users in which you can grant or deny permissions as needed. This is also known as the SQL authentication method. There are major security flaws in this method, as listed below. The second and better option is to have the Active Directory (AD) handle all the necessary authentication and authorization, also known as Windows authentication. Once the user logs in to their computer the application will connect to the database using those Windows login credentials on the operating system.
The major security issue with using the SQL option is that it violates the principle of least privilege (POLP) which is to only give the user the absolutely necessary permissions they need and no more. By using the sa account you present serious security flaws. The POLP is violated because when the application uses the sa account they have access to the entire database server. Windows authentication on the other hand follows the POLP by only granting access to one database on the server.
The second issue is that there is no need for every instance of the application to have the admin password. This means any application is a potential attack point for the entire server. Windows only uses the Windows credentials to login to the SQL Server. The Windows passwords are stored in a repository as opposed to the SQL database instance itself and the authentication takes place internally within Windows without having to store sa passwords on the application.
A third security issue arises by using the SQL method involves passwords. As presented on the Microsoft website and various security forums, the SQL method doesn’t’ enforce password changing or encryption, rather they are sent as clear text over the network. And the SQL method doesn’t lockout after failing attempts thus allowing a prolonged attempt to break in. Active Directory however, uses Kerberos protocol to encrypt passwords while employing as well a password change system and lockout after failing attempts.
There are efficiency disadvantages as well. Since you will be requiring the user to enter the credentials every time they want to access the database users may forget their credentials.
If a user being removed you would have to remove his credentials from every instance of the application. If you have to update the sa admin password you would have to update every instance of the SQL server. This is time consuming and unsafe, it leaves open the possibility of a dismissed user retaining access to the SQL Server. With the Windows method none of these concerns arise. Everything is centralized and handled by the AD.
The only advantages of using the SQL method lie in its flexibility. You are able to access it from any operating system and network, even remotely. Some older legacy systems as well as some web-based applications may only support sa access.
The AD method also provides time-saving tools such as groups to make it easier to add and remove users, and user tracking ability.
Even if you manage to correct these security flaws in the SQL method, you would be reinventing the wheel. When considering the security advantages provided by Windows authentication, including password policies and following the POLP, it is a much better choice over the SQL authentication. Therefore it is highly recommended to use the Windows authentication option.