Q199466: SMS: How to Set Up SQL Server Integrated Security For Use w/ SMS
Article: Q199466
Product(s): Microsoft Systems Management Server
Version(s): winnt:1.2
Operating System(s):
Keyword(s): kbDatabase smsdatabase smssecman
Last Modified: 31-JUL-2001
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft Systems Management Server version 1.2
-------------------------------------------------------------------------------
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:
a. In SQL Security Manager, on the View menu, click User Privilege.
b. On the Security menu, click Grant New.
c. 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.
d. 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:
a. Click SQL Enterprise Manager.
b. On the Manage menu, click Logins.
c. Type the login name that corresponds to the type of functions that this
login ID will have permissions for.
d. Type a password.
e. Select Permit column for the Systems Management Server database.
f. 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:
a. On the Manage menu, click Logins.
b. Select the new SQL Server login ID from the drop-down list of available
accounts.
c. 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).
d. 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.
a. In Systems Management Server Security Manager, select SQL Server login ID
from the drop-down list.
b. 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.
c. 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:
a. From SQL Enterprise Manager, right-click the SQL Server name that appears
in the Server Manager window and click Configure on the shortcut menu.
b. Click Security Options.
c. 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:
a. Using a Windows NT user account that was created in Step 1 of this
procedure, log on to a computer running Windows NT Workstation.
b. 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.
c. 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:
a. Open SQL Enterprise Manager.
b. On the Manage menu, click Logins.
c. Type the login name that matches the user's Windows NT account.
d. Type a password and note that it does NOT need to match the password for
the corresponding Windows NT account.
e. Click in the Permit column for the Systems Management Server database.
f. 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.
g. Click Add.
SQL SECURITY MANAGER METHOD:
a. In the User Privilege view of SQL Security Manager, select the group that
contains the new Windows NT user account.
b. On the Security menu, click Account Detail.
c. 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.
d. Open SQL Enterprise Manager and click Logins on the Manage menu.
e. Select the new SQL Server login ID from the drop-down list of available
accounts.
f. 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.
g. 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:
a. In SQL Enterprise Manager, click Logins on the Manage menu.
b. Select the login name that matches the user's Windows NT account from the
drop-down list.
c. 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 smsdatabase smssecman
Technology : kbSMSSearch kbSMS120
Version : winnt:1.2
Issue type : kbhowto kbinfo
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.