SQL Server Security

SQL Server accounts and roles play a critical part in Microsoft AppFabric 1.1 for Windows Server security. AppFabric uses these SQL Server entities to control access to stores and tables that contain data from monitoring operations, as well as state control data related to workflow persistence. AppFabric does not provide tooling to assist in database security management. To create accounts and roles, and to view, manipulate, and assign the proper permissions to database objects, use the tooling support that comes with your database installation. For SQL Server, use the SQL Server Management Studio.

AppFabric uses SQL Server logins and roles to manage access to assets such as the persistence and monitoring stores and stored procedures. Security policies are applied through permissions on tables and stored procedures that determine who can read, write, and perform administrative operations on the persistence and monitoring schemas. Each schema is secured with its own set of security policies.

SQL Server Authentication Modes and AppFabric

SQL Server provides two methods of securing authentication to its AppFabric database servers:

• Windows authentication. This is provided by the default Windows Authentication mode. It is the most secure authentication mode for SQL Server. When Windows Authentication mode is configured, SQL Server uses Windows security to validate the account and password of the requesting user account with the Windows operating system.

• SQL Server authentication. This is provided by SQL Server Authentication mode. It exists only for backward compatibility with applications and users that need to access SQL Server by using an explicit user account and password. This is the least secure of the modes.

While AppFabric will work with SQL Server authentication, passing explicit user account names and passwords embedded in a connection string from a configuration file is not a recommended security best practice. We strongly recommend that you configure SQL Server to use Windows Authentication mode and avoid SQL Server Authentication mode.

If you must use SQL Server Authentication mode, or if you are using a non-SQL Server provider that requires storing passwords in the connection string, we recommend that you use encrypted connection strings. AppFabric cannot process encrypted sections of a configuration file, so you cannot view or add an encrypted connection string by using the AppFabric tools.

If you need to secure the configuration files on an AppFabric computer by encrypting parts of a configuration file, use the ASP.NET IIS Registration Tool (Aspnet_regiis.exe). You can use this tool to encrypt (-pe option) any security-sensitive sections of a configuration file outside of the AppFabric user interface. If you later need to view or modify those sections, you can decrypt them by using the –pd option. Decrypting the sections allows you to view them in the AppFabric tools, make changes, save the changes to the configuration file, and encrypt the sections again by using the tool outside of AppFabric. For more information about how to use the ASP.NET IIS Registration Tool, see ASP.NET IIS Registration Tool (Aspnet_regiis.exe) (https://go.microsoft.com/fwlink/?LinkId=169163).

SQL Server Logins

A SQL Server login requires a user account and password to log on to the SQL Server computer. You can view this as the right of access, or authentication in Windows security terminology. SQL Server uses Windows Integrated Authentication for identifying the security principals attempting to access or manage the AppFabric database resources. To connect to SQL Server by using Windows Integrated authentication, you must provide the Windows identity under which your application is running. You must also be sure that the identity has been granted proper access to the SQL Server database.

To perform any configuration or operational actions on the database schemas or data, the login account must be mapped to a SQL Server role that has the appropriate permissions. A SQL Server role works like a Windows group. Membership of a login account within a SQL Server role dictates how much control over administrative activities and database operations that login account has. A login account can be a member of more than one database role.

These SQL Server logins are created when you install AppFabric.

Login Name	Windows Account	Database Role Membership
AS_Administrators	LOCALHOST\AS_Administrators	ASMonitoringDbAdmin ASMonitoringDbReader ASMonitoringDbWriter public Microsoft.ApplicationServer.DurableInstancing.WorkflowAdministrators Microsoft.ApplicationServer.DurableInstancing.WorkflowManagementServiceUsers System.Activities.DurableInstancing.InstanceStoreObservers System.Activities.DurableInstancing.WorkflowActivationUsers
AS_Observers	LOCALHOST\AS_Observers	ASMonitoringDbReader public System.Activities.DurableInstancing.InstanceStoreObservers
IIS_IUSRS	BUILTIN\IIS_IUSRS	public System.Activities.DurableInstancing.InstanceStoreUsers

SQL Server Database Roles

SQL Server has three types of database roles: server, application, and database. For completeness we will briefly discuss them here. AppFabric exclusively leverages the database role model for most of its SQL Server security.

• SQL Server “server” role. A SQL Server “server” role is defined outside of any stores at the server level. SQL Server roles are predefined and thus cannot be modified in number or in content. An example of a common server role is the sysadmin role. Membership in this role allows the login account complete control over all database operations, and the ability to perform any operation on the SQL Server data in any store. AppFabric does not explicitly use any server roles in its security model.

• SQL Server “application” role. Application roles support the more complex and custom security needs of a particular application. A store may be used by more than one application with a common need to enforce security for its data when either application accesses it. AppFabric does not explicitly use any application roles in its security model.

• SQL Server “database” role. AppFabric makes extensive use of the database role. There are three types of database roles: public, user-defined, and fixed. For completeness we will discuss them here. AppFabric exclusively leverages the user-defined database role model for a large part of its SQL Server security.

  There are three types of SQL Server database roles:

  • Public. The public database role contains default access permissions for all database users. Thus every login account created by AppFabric is a member of this role.

  • Fixed. Like SQL Server “server” roles (for example, sysadmin), fixed database roles cannot be modified. Unlike server roles, which exist at the server level, database roles exist at the database level for each store. An example of a fixed database role is db_owner. You can add or remove SQL Server login user accounts to a fixed database role.

  • User-defined. AppFabric creates empty specific user-defined database roles during installation. The AppFabric installation program does not explicitly insert any Windows account or SQL Server login account into those user-defined database roles. You must explicitly add accounts by using the SQL Server management tools.

AppFabric uses SQL Server database roles to control access to its monitoring and persistence data stores. When you initialize a new AppFabric monitoring or persistence data store, several user-defined database security roles are created during installation. The following table shows how these roles are mapped to the SQL Server logins described in the previous section.

SQL Server User-Defined Role	Schema	Mapped Logins	Rights
ASMonitoringDbAdmin	Monitoring	AS_Administrators	Write to staging table, read from event views, and invoke purge and archive stored procedures
ASMonitoringDbReader	Monitoring	AS_Administrators and AS_Observers	Read from event views
ASMonitoringDbWriter	Monitoring	AS_Administrators	Write to staging table and invoke import procedure
Microsoft.ApplicationServer.DurableInstancing.WorkflowAdministrators	Persistence	AS_Administrators	Enqueue control commands into the store's command queue
System.Activities.DurableInstancing.InstanceStoreObservers	Persistence	AS_Administrators and AS_Observers	Read from instance store views
System.Activities.DurableInstancing.InstanceStoreUsers	Persistence	BUILTIN\IIS_IUSRS	Invoke stored procedures that pertain to persistence
Microsoft.ApplicationServer.DurableInstancing.WorkflowManagementServiceUsers	Persistence	AS_Administrators	Dequeue control commands from the store's command queue
System.Activities.DurableInstancing.WorkflowActivationUsers	Persistence	AS_Administrators	Query instance store for workflow instances that can be activated

If you are using Active Directory, we strongly recommend that you design your AppFabric security roles using domain accounts to simplify security across multiple computers. As an AppFabric administrator, you can explicitly create two custom group accounts through Active Directory for the administrators and observers roles. For example, you could call them “DOMAIN\MyAppFabricAdmins” and “DOMAIN\MyAppFabricObservers”.   You can then grant the appropriate permissions to both groups on each computer by manually adding the “DOMAIN\MyAppFabricAdmins” group to the LOCALHOST\AS_Administrators group, and the “DOMAIN\MyAppFabricObservers” group to the LOCALHOST\AS_Observers group. The Event Collection service and the Workflow Management service need to run under domain accounts that are members of “DOMAIN\MyAppFabricAdmins” group.

Security Note
AppFabric cmdlets that use SQL Server rely upon these SQL Server database roles to authenticate their identities during execution.

Non-SQL Server Database Storage

The SQL Server database roles are specific to SQL Server. However, if you do not use the default SQL Server provider and choose to write your own custom provider, you can map the functionality of these roles to their functional equivalent in your non-SQL Server store.

For non-SQL Server stores, you need to include user ID and password in a connection string to secure access to a store. While it is permissible to pass user IDs and passwords in connection strings to a store, such as with SQL Server authentication, we do not recommend this. If passing a user ID and password in the connection string is your only choice, be sure to follow proper .NET Framework security practices to ensure that connection strings are encrypted.

Note

When you use encrypted connection strings, the associated IIS applications run correctly. However, the related IIS Manager tools do not work when connection strings are encrypted. To use the tools, you must decrypt the connection strings, make changes to the configuration by using the IIS tools, and then encrypt the connection strings again.

  2012-09-12