Configuring Security

MTS provides a flexible security model. As with most software products, flexibility can lead to complexity. This exploration of MTS security includes setting permissions on components and packages along with the relationship between MTS security and SQL Server 2000 security.

MTS security relies on the configuration of users in the NT domain. Users and groups within an NT domain can be assigned to roles in MTS. These roles are in turn granted permissions on MTS objects.

Package Security Options

Only a couple of steps are necessary to implement security within a package. If you look back at Figure 43.8, the Bank.Account component shows that this component has Authorization Checking enabled, and it was noted that this component didn't have a role set up for it. What this means is that the component will use the security set up for the package.

Creating Roles

Roles are mechanisms in MTS to which permissions are assigned. Roles consist of groups or users from an NT domain. Roles are defined on a per-package basis. In other words, each package might have a different set of roles with which to work. The following example creates a new role called Execs for the Sample Bank package:

  1. Select the Roles folder under the Sample Bank Package from MTS Explorer.

  2. Right-click the Roles folder, and select New, Role.

  3. Type Execs in the New Role screen, and click OK.

  4. Select the Users folder in the Execs role.

  5. Right-click the Users folder and select New, User.

  6. Select Everyone as the user for Execs role by clicking the Add button, as shown in Figure 43.9.

    Figure 43.9. Adding users to MTS package roles.


  7. Click OK.

Setting Role Membership for a Component

Each component within a package can have its own security settings. The Role Membership folder is the mechanism for designating which roles have access to which components. The following example grants the Execs role access to the Bank.Account component only:

  1. Open the Bank.Account component in the MTS Explorer.

  2. Select the Role Membership folder.

  3. Right-click the Role Membership folder and select New, Role.

  4. In the Select Roles window, select the Execs role and click OK (see Figure 43.10).

    Figure 43.10. Selecting roles for a component.



You should shut down a package after you change its roles or the role memberships of its components. This action will ensure that the new settings take effect. To shut down a package, right-click the package and select Shut Down. The package will restart the next time it is called.

How Do MTS and SQL Server Security Relate?

You can combine MTS security and SQL Server 2000 security in several ways. This section addresses two primary scenarios. The first scenario is when you want to grant access to the database on a per-user basis, and the second scenario is when you want to grant access on a per-package basis. Both of these scenarios assume that SQL Server is using SQL Server and Windows NT security (Mixed Mode Authentication).

Scenario 1: Per User

In this situation, you should build each MTS component to accept database usernames and passwords as parameters. The component can then use these parameters to open a connection. To set up this scenario, you must give access to the MTS component to each user through roles, and you must also give each user a login for the database.


Using this security scheme could severely limit scalability. You must create a unique connection for each user, which cripples the effectiveness of connection pooling.

Scenario 2: Per Package

In this scenario, all components in a package call the database under one database login. MTS can validate individual users by checking a component's role membership. After MTS validates the users, the users then perform all database activities under a single database login. This security scheme enables effective use of connection pooling.

Other Security Considerations

You must keep several other issues in mind regarding MTS security:

  • This discussion has only covered declarative security. Programmatic security is also available in MTS. Programmatic security enables an MTS component to determine the authorization of its caller through code.

  • It is possible to control who has administrative privileges in MTS. You might want only certain users to be able to make changes to your MTS setup.

  • Several different authentication levels are available for MTS components. All of the examples in this chapter use only the Connect authentication level.

  • You must enable Distributed COM (DCOM) security on all client machines that will access a component under MTS control. You can use the dcomcnfg.exe utility to configure the client machines.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features