Knowledge Base

SMS: How to Set Up SQL Server Integrated Security For Use With SMS

Article ID: 199466

Article Last Modified on 6/14/2005

APPLIES TO

This article was previously published under Q199466

SUMMARY

When Systems Management Server is used on a large scale administrators often find it necessary to give other people permission to use the Systems Management Server Administrator utility. In response to this need and since granting full permissions to all the features of the Systems Management Server Administrator is rarely desired, it is necessary to create a seperate set of SQL Server login IDs. These IDs have different permissions matching the needs of unique and specific tasks. Furthermore, using SQL Server integrated security can help reduce the amount of administration required because the users only have to remember one password; that of their Windows NT account.

MORE INFORMATION

When using the Systems Management Server Administrator tool, the user will only have access to those features granted to their matching SQL Server login ID or the SQL Server login ID that the user is aliased to. The procedure described below incorporates the use of SQL Server integrated security so that users will only have to log on to their workstation once.

These instructions also incorporate aliasing certain SQL Server logins to a "unique" SQL Server login ID that does not match a specific user account. This makes managing the permissions on the SQL Server login IDs easier since the Systems Management Server Security Manager only allows permissions to be modified on individual SQL Server login IDs. For example, if you have 20 SQL Server logins; that is, user accounts, that need to be given access to perform Remote Control in Systems Management Server, they can be given permissions much easier. Modifying each account's rights individually can take a long time. But, if those 20 SQL Server logins are aliased to one SQL Server login ID called "HelpDesk", it becomes a simple task to modify permissions on the single HelpDesk login ID.

NOTE: If you are managing a small number of SQL Server logins, it may not be necessary to use aliases. If this is the case, simply skip the references below where SQL Server login IDs are aliased to other SQL Server logins. For Step 5, simply assign permissions to the users' SQL Server login IDs.

Initial Setup

  1. In the Windows NT User Manager, create the Windows NT user group(s) and assign user accounts to the group(s).
  2. Use SQL Security Manager to create SQL Server login IDs for users in the Windows NT groups you just created. To do this, perform the following steps:

    1. In SQL Security Manager, on the View menu, click User Privilege.
    2. On the Security menu, click Grant New.
    3. Select the correct Windows NT group and make certain the Add Login IDs For Group Members and Add Users to Database options are selected. Also ensure that the SMS database is selected from the drop-down menu.
    4. Click Grant.
  3. Using the SQL Enterprise Manager utility, create unique SQL Server login IDs for the types (or levels) of access that your users will need when using the Systems Management Server Administrator utility. These are the SQL Server login accounts that you will assign the different permissions to using the Systems Management Server Security Manager utility. These unique SQL Server login IDs serve the purpose of custom templates or user groups. Again, if you need to change permissions, it is a simple process to modify the SQL Server login ID that the users are aliased to as opposed to changing permissions on each individual SQL Server login ID. Some examples include "HelpDesk", "SoftDist" (software distribution), or "SMSAdmins". To create a login ID, perform the following steps:

    1. Click SQL Enterprise Manager.
    2. On the Manage menu, click Logins.
    3. Type the login name that corresponds to the type of functions that this login ID will have permissions for.
    4. Type a password.
    5. Select Permit column for the Systems Management Server database.
    6. Click Add.
  4. While still operating in the SQL Enterprise Manager, alias the SQL Server login IDs (that match the Windows NT user logon accounts) created by SQL Security Manager to the unique logins that were manually created in Step 3. To do this, perform the following steps:

    1. On the Manage menu, click Logins.
    2. Select the new SQL Server login ID from the drop-down list of available accounts.
    3. Click Alias (for the Systems Management Server database) and select the appropriate unique SQL Server login ID that was created in Step 3 of the Initial Setup (for example, "HelpDesk", "SoftDist", and so on).
    4. Click Add.
  5. Use the Systems Management Server Security Manager to grant access to specific areas of Systems Management Server to the unique SQL Server logins that were created in Step 3. Because the users of SQL Server login IDs are aliased to a few unique ones, it is not necessary to modify the permissions on the users' SQL Server logins. To do this, perform the following steps:

    NOTE: Do not modify the permissions on "dbo" (database owner). This login should always have full permissions to all security objects.

    1. In Systems Management Server Security Manager, select SQL Server login ID from the drop-down list.
    2. Select the appropriate security objects and select the type of access desired for those objects; that is, full, view, or no access. You can use the default security templates by clicking Use Template on the Security menu.
    3. After setting the permissions you want, click Save on the toolbar or click Save User on the Security menu to make the changes take effect.
  6. To implement SQL Server integrated security, perform the following steps:

    1. From SQL Enterprise Manager, right-click the SQL Server name that appears in the Server Manager window and click Configure on the shortcut menu.
    2. Click Security Options.
    3. Select Windows NT Integrated as the Login Security Mode, and then click OK.
    NOTE: When SQL Server integrated security is enabled, all members of the Administrators built-in Windows NT user group will have System Administrator (SA) privilege to the SQL Server. To avoid this, you must create a separate Windows NT user group (for example, a group named "SQLAdmins") that contains those user accounts that need SA privilege to SQL Server. The Systems Management Server Service Account should be in this new group. After the group is created, use SQL Security Manager (with the SA privilege view) to grant SA privilege to the new group and then revoke SA privilege from the Administrators group.
  7. Stop and restart the MSSQLServer service, to do this, use SQL Enterprise Manager, SQL Service Manager, or Control Panel Services.
  8. After these steps have been accomplished, the system should be tested. To test, perform the following steps:

    1. Using a Windows NT user account that was created in Step 1 of this procedure, log on to a computer running Windows NT Workstation.
    2. Run the Systems Management Server Administrator utility and type the SQL Server name and database information. Because SQL Server integrated security is being used, it is not necessary to type any SQL Server login ID or password. In fact, doing so will have no effect.
    3. Click OK.
The Systems Management Server Administrator now only allows those features that have been enabled to function. If some functionality is not working correctly, it can be the result of a dependency on another security object not being enabled. If in doubt, examine the list of dependencies in Chapter 6: "Configuring Security for the Systems Management Server Administrator" in the Installation And Configuration guide in the Systems Management Server Books Online.

Adding a User After the Initial Setup

To add another user after completing the steps above, perform the following steps:

  1. In Windows NT User Manager, add the Windows NT user account to the appropriate group.
  2. Use SQL Security Manager or SQL Enterprise Manager to create the new SQL Server login ID. Choose either to use SQL Security Manager or SQL Enterprise Manager. NOTE: The SQL Enterprise Manager method is quicker when only adding a few (one or two) SQL Server login IDs. The SQL Security Manager method is easier if you want to add several new logins at once.

    SQL ENTERPRISE MANAGER METHOD

    1. Open SQL Enterprise Manager.
    2. On the Manage menu, click Logins.
    3. Type the login name that matches the user's Windows NT account.
    4. Type a password and note that it does NOT need to match the password for the corresponding Windows NT account.
    5. Click in the Permit column for the Systems Management Server database.
    6. Click Alias (for the Systems Management Server database) and select the appropriate unique SQL Server login ID that was created in Step 3 of the "Initial Setup" procedure in this article.
    7. Click Add.

    SQL SECURITY MANAGER METHOD

    1. In the User Privilege view of SQL Security Manager, select the group that contains the new Windows NT user account.
    2. On the Security menu, click Account Detail.
    3. From the Account Detail window, click Update Logins. This will create a matching SQL Server login ID for the new Windows NT user account. NOTE: For each Windows NT account that already has an SQL Server login ID, an error saying that the account already exists will be generated. You can ignore these error messages.
    4. Open SQL Enterprise Manager and click Logins on the Manage menu.
    5. Select the new SQL Server login ID from the drop-down list of available accounts.
    6. Click Alias (for the Systems Management Server database) and select the appropriate unique SQL Server login ID that was created in Step 3 of the "Initial Setup" procedure in this article; that is, HelpDesk, SoftDist, and so on.
    7. Click Add.

Delete a User

To remove a user, perform the following steps:

  1. Using SQL Enterprise Manager, delete the SQL Server login ID that matches the Windows NT user account. To do this, perform the following steps:

    1. In SQL Enterprise Manager, click Logins on the Manage menu.
    2. Select the login name that matches the user's Windows NT account from the drop-down list.
    3. To temporarily disable this user's access, click to clear the Permit column for the Systems Management Server database and then click Modify. To permanently delete the SQL Server login ID, click Drop.
    NOTE: If necessary, use Windows NT User Manager utility to remove the user's account from the Windows NT user group and/or delete the account entirely.

Possible Problems

The following are things to be aware of:
  • If a user's SQL Server login ID is deleted and then re-created, the Systems Management Server Security Manager may show that the user's SQL Server login ID still has certain permissions allowed to the Systems Management Server database when in fact those permissions are not valid. If this occurs, revoke the existing permissions and grant them again, using the Systems Management Server Security Manager. If aliases are being used, re-establish the alias for the re-created account (see Step 4 in the "Initial Setup" procedure).
  • When running the Systems Management Server Administrator with a reduced set of permissions, certain menu buttons will be removed from the toolbar. When the Systems Management Server Administrator console is run again with full permissions, the buttons will not be automatically restored. To restore these buttons, perform the following steps:
    1. On the Options menu in the Systems Management Server Administrator, click Customize Toolbar.
    2. Click Reset and then click OK.

REFERENCES

For more information concerning this topic, including descriptions of the Security objects (viewed with the Systems Management Server Security Manager), see Chapter 6: "Configuring Security for the Systems Management Server Administrator" in the Installation And Configuration guide in the Systems Management Server Books Online.

For descriptions of the security objects, see Chapter 6: "Configuring Security for the Systems Management Server Administrator" in the Installation And Configuration guide in the Systems Management Server Books Online.

For more information on implementing SQL Server integrated security, see Chapter 8, "Security Concepts" and Chapter 9, "Managing Security" in the Administrator's Companion in the SQL Server Books Online.

Additional query words: prodsms

Keywords: kbdatabase kbhowto kbinfo KB199466


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.