Use Windows Authentication for Connection strings.
  • Questions to ask about database connection strings:
    • What connection string will internal web applications use to connect to SQL Server?
    • What connection string will public web applications with unauthenticated users use to connect to SQL Server?
    • What connection string will web services, such as REST use to connect to SQL Server?
    • What connection string will desktop applications use to connect to SQL Server?
    • Do you develop desktop or web applications for resale? What connection strings will you expect your customers to configure?
    • What connection string will users of adhoc query tools use to connect to SQL Server?
  • Here are the options for authenticating users:
    • Use Windows Authentication
      • This is usually most secure and easiest to implement.
      • You don't have to store passwords stored in files or the registry or elsewhere where they may be discovered.
      • More authentication options (OS Base) - Retinal scans, complexity policies, etc.
    • SQL Server Authentication
    • Application Roles. Application Roles are intended to allow users to have a different set of permissions when connecting to SQL Server through a specific application than they would in general. For an excellent review of the pros and cons of application roles, please read this article:
    • OpenId. OpenId or OAuth and other such providers are useful for applications with simple security requirements, such as, "You need to confirm your identify before posting a comment on this blog." When used, web sites generally give the same permissions to all users that successfully authenticate with OpenId.
  • The four IIS Authentication approaches for web applications listed are:
    • IIS - Anonymous. IIS is configured to not authenticate the user. This means that all users have the same privilege and there is no logon process. Access to the database, if there is any, will be the same for all users.
    • IIS - Integrated Windows Authentication. IIS is configured to allow users in the web application that have a valid logon for the operating system that allows the access to the web server. The user logon resides in Active Directory. This is the recommended option for maximum security for internal web sites. IIS and ASP.Net can use the credentials of the user's Active Directory account to connect to SQL Server and use the permissions assigned for that users. This is not a good option for public web sites as the user logon must be created in Active Directory prior to use.
    • IIS - Forms Authentication, prompting for a SQL Logon and password. This approach requires each user to know their SQL Server Logon and password, which is not the same as their operating system Logon and password (though they will usually be kept very similar).
    • IIS - Forms Authentication, prompting for an application Logon and password. This approach requires that the application maintain Logons and passwords separate from both the SQL Server Logons and Active Directory. Doing this securely with strong security may be challenging, but the Microsoft Membership provider tools usually provide a good approach. In this approach, the user's password will be stored in the database, and developers need to insure that good encryption and hashing techniques are employed. An important thing to realize in this approach is that the database connection must be made using either a know/hard-coded SQL Server Logon, or windows authentication. Technically, the user's permissions to SQL Server are controlled by what the this underlying database connection allows.
Windows Authentication SQL Server LogonID Application Logon
External web applications
IIS - Anonymous
Runs as the Service Account:
Example: IISAppServiceAcct
It is not recommended to grant SQL Server access to an account that is used by a service to log on to the operating system. However, if the access is read only and the data is not sensitive, this is acceptable. This is a recommended technique. The web application will read the logon and password to use for SQL Server from an encrypted web.config file. This is inferior to SQL Server Logon in this case.
IIS - Integrated Windows Authentication
Example: MyDomain\MyLogon
This is not an option. If a user can authenticate with the OS, then by definition this is an internal web application. <--see note <--see note
IIS - Forms Authentication
Authenticate Logon and password is not a SQL Logon/password.
This scenario usually requires the web application to first connect to a database in order to validate the user. That database connection will use the credentials of the IIS Service if Windows Authentication is used. This is not recommended. This is a recommended technique. The web application will read the logon and password to use for SQL Server from an encrypted web.config file. This is inferior to SQL Server Logon in this case.
IIS - Forms Authentication
Authenticate Logon and password is a SQL Logon/password.
This does not make sense. If the web application is going to prompt for a SQL Logon, why would it then use Windows Authentication? This is a bad choice. The connection string to the database will be constructed using the logon and password provided to Forms Authentication. This is not secure for an external web application. This is not secure for an external web application.
Internal web applications
IIS - Anonymous
Runs as the Service Account:
Example: IISAppServiceAcct
It is not recommended to grant SQL Server access to an account that is used by a service to log on to the operating system. This is acceptable but not as secure as Windows Authentication because a logon and password must be stored accessible to the IIS server. This is inferior to SQL Server Logon in this case.
IIS - Integrated Windows Authentication
Example: MyDomain\MyLogon
This is recommended. This is acceptable but not as secure as Windows Authentication because a logon and password must be stored accessible to the IIS server. Application Logon is relevant in this scenario.
IIS - Forms Authentication
Authenticate Logon and password is not a SQL Logon/password.
This scenario usually requires the web application to first connect to a database in order to validate the user. That database connection will use the credentials of the IIS Service if Windows Authentication is used. This is not recommended. This is acceptable but not as secure as Windows Authentication because a logon and password must be stored accessible to the IIS server. Application Logon is relevant in this scenario.
IIS - Forms Authentication
Authenticate Logon and password is a SQL Logon/password.
This does not make sense. If the web application is going to prompt for a SQL Logon, why would it then use Windows Authentication? This is a bad choice. The connection string to the database will be constructed using the logon and password provided to Forms Authentication. This is acceptable but usually harder to manage than Windows Authentication. Application Logon is relevant in this scenario.
Web services See above based on internal or external usage.
Desktop applications This is recommended. 1) Build the connection string based on Logon and password entered by the user. This is acceptable but requires users to know their SQL Server Logon and password. Application Logon is relevant in this scenario.
2) Use the same hard-coded SQL Logon for all users. This is common, but not ideal. See notes below. Application Logon is relevant in this scenario.
AdHoc Query tools like Microsoft Access or Report Writers This is recommended. This is acceptable but requires users to know their SQL Server Logon and password. Not an option.

Notes:

  • Why do so many applications hard-code a SQL Server logon and password for all users to use?

    • I believe there are several reasons this occurs. One is that it is often easiest for development to start out using a SQL Server logon that has all permissions. Another reason is that some users of SQL Server connect from operating systems that do not support Windows authentication. A third reason is simply the habit of the application developers. A fourth reason is that for third-party developers, that are developing a SQL Server based application for resale, they may find it easier to tell their clients they need to create a specific SQL Server account and given it specific permissions rather than encourage the client to assign users to groups in Active Directory; especially in environments without windows domains or knowledge IT Admins.

  • What about ASP.Net Impersonation or Delegation?

    • Another option within IIS is to use ASP.Net Impersonation or Delegation. This technique allows the .Net code to specify a windows account to impersonate by providing the windows account logon and password. Using ASP.Net Impersonation or Delegation of an Authenticated User is not recommended. It requires hard-coding an OS username/password or entries in the web.config. For more info: http://msdn.microsoft.com/en-us/library/aa292118(VS.71).aspx

Authorization

The section above primarily focuses on authentication, which is simply validating that the person attempting to connect to SQL Server is allowed to do so. Once a user is authenticated and allowed to use the application, the actions they can take within the application are controlled by authorization. Authentication and authorization are related and cannot always be totally untangled.

The actions a user can take are limited to the authorizations granted to the SQL Server connection based on the choice of SQL Server connection authentication. Many application developers choose to allow the SQL Server connection to have all CRUD permissions on every table, but they use data stored in application tables to determine what the application will allow specific users to do. Developing your own tables to store allowed actions within the applications often makes sense when the application needs to support thousands of fine-grained permission checks, or when the application requires complicated permission checking. For example, it is difficult to use SQL Server's permission features to specify that Rob can delete Orders from the Orders table, but only if the Orders are for less than $100. Permissions such as this are better handled by application control over the action.

Another challenge with relying on the permissions defined directly to a SQL Server logon in the database is providing good feedback to the user about what they can do. Most users would prefer that a delete button be disabled if they don't have delete permission. That would be much better than allowing the user to click on the button, and returning an error message to the user when the database responds that the user does not have delete permission.

In the real world

Most of the applications I have developed are applications for resale. These applications often have internal web interfaces, external web interfaces, desktop interfaces, web service interfaces, and adhoc query interfaces. Thus, we need solutions that handle many situations. Our implementations usually are:

  1. Use windows authentication for the desktop applications. All users belong to a single active directory group that is mapped to a single SQL Server group with all the necessary permissions for the application.

  2. The SQL Server group has select permission on all application tables, but it does not have Insert, Update, or Delete on any tables. Instead, we use stored procedures for all Inserts, Updates, and Deletes. The SQL Server group has execute permission for all the stored procedures. Thus, when a user runs our application, the account they used to connect to SQL Server has permission to query any table, but the application security we provide restricts what the user can actually do.

  3. Our applications contain their own set of permission tables that determine what each user can do to the data through our applications. Administrative users of the application use a program we provide to configure individual user permissions.

  4. Our applications support both a windows authentication option and an application logon and password option. If a user uses the windows authentication option, we lookup the application logon to use in our database in a table that maps windows logons to application logons.

  5. Our internal web application uses a SQL Server Logon and password that are stored in an encrypted section of the web.config file to connection to the database. This SQL Server Logon is part of the same group in SQL Server as all other users.

  6. We have two internal web applications. One uses IIS Integrated Windows Authentication to obtain the user's application logon, then it redirects the user to the Forms based web application. Thus, users can choose to authenticate using windows integrated security, or by providing their application logon and password. Both web applications use the same SQL Server Logon to connect to the databases.

  7. Some users have adhoc query tools such as Microsoft Access. They use windows authentication to connect to SQL Server, this allows them read access to the tables, but not write access.

  8. There are at least two weaknesses in our solutions. These are acceptable for our software, but may not be acceptable for software requiring more security.

    1. Users could call stored procedures from tools such as Microsoft Access if the user has the technical skills to do so.

    2. Users of adhoc tools can query any data, even when some of our application rules block some users from accessing some data based on their application permissions.