AD User vs SQL User for SQL Server Authentication

Question

My company has multiple web applications that we deploy to customer sites. Often the customer has the final say in deployment options in which often it shocks me.

Many of these customers are deploying the web application to point to the deployed database using the same Active Directory credentials as a server administrator. If the application was to be compromised for any reason, the attacker has also earnt themselves rights to the server and potentially network.

What is the correct purpose of using Active Directory for SQL Server. The only healthy reason I could think of using it, is if you had web farms with multiple SQL Servers in which you would have isolated accounts/groups for accessing all of those servers with correct permissions and even then I would have concerns in particular environments. Is my assumption correct that these users and groups should remain isolated in permissions from other permissions such as machine/server Admin accounts aka an account should never be able to log into a server and log into a database?

Or is it just lazy security in which you should never use AD for SQL Server?

Thanks,

Answer 1

Ideally, the user or application accessing SQL Server should be using the set of credentials that identifies them correctly, and that has been assigned the appropriate level of access to the SQL Server and/or database(s) as needed to perform the actions they need to perform.

SQL Authentication is a legacy authentication mechanism that in a properly configured environment should not be enabled at all. Microsoft doesn't recommend it and the disassociation between the identity of the user or application from the authentication context is only bad, from a security point of view.

That settled, you are certainly correct that an application should not be accessing a database in the context of a server or domain administrator account. The web application should be run under an account that has the necessary permissions to execute the functions of the web application (including the appropriate access to the database(s) it requires to function) and no more.

Ultimately, this is not a question of AD auth vs SQL auth, but about the use (or lack thereof) of limited use accounts, or the violation of the principle of least privilege. The fact that they're not using SQL auth is not a problem, and is in fact the correct design. The fact that the AD accounts have excessive privileges on the other hand, is as you've noticed, a serious problem and one that should be addressed.

Answer 2

Using Active Directory for SQL Server has a number of advantages, which makes it the recommended approach. SQL DBAs will often want to have the database in Windows Integrated Authentication (WIA) mode only (instead of "Mixed mode" where SQL Authentication is also support) because of it:

• When using AD, account authentication is centralized. You have one overview of all accounts that exist in the environment. If an account is compromised, you only need to revoke it once: in AD. With SQL Authentication, you'll have to log in to every SQL Server and remove it.
• When using AD, password rotation (if necessary) is centralized. With SQL Authentication, you'll have to log on and change the password on every target (which often means you're not going to do it).
• When using AD, the passwords are stored in a single central repository. With SQL Authentication, they are stored in the SQL database itself. Hardening AD is usually much simpler than hardening SQL Server as the attack vector towards your SQL Servers is generally larger (yes, this is case specific).
• When using AD, authentication is done more securely (using Kerberos). This makes it harder for any adversary to try and capture the password, and it is much less prone to Man In The Middle (MITM) attacks. Also unlike with passwords, Kerberos tickets cannot be reused against other services: each ticket is specific to a service (in this case, for a single SQL Server service). Authenticating against another service requires obtaining a new ticket.
• When using WIA, managing accounts to access a SQL Server generally only requires granting the permissions to a runtime (service) account (the account under which an application server is running). There is no need to create additional accounts like you would with SQL Authentication. This also centralizes impact analysis: if an IIS server is compromised, only that IIS service account needs to be revoked in order to mitigate the risk. With SQL Authentication, you'll need to look up in your configuration what SQL Authentication account is used by that IIS server.
• When using WIA, your application servers do not need to store the user id and password for their connections. Again, this reduces the impact of a compromised system.
• When using WIA, it is possible to disable remote logins. This is not possible with SQL Authenticated accounts.

Answer 3

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.