• 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:
    • http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/
  • 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.

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.