Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Secure Your SMS in 6 Steps
Use SQL Server's Enterprise Manager and SMS Security Manager to complete the 6-step process of securing your SMS.
Bart Groenewoud
October 31, 1998
6 Min Read
You need SQL Server security to make your SMS system secure
System security is one of the least documented aspects of Systems Management Server (SMS). However, an unsecured SMS system can definitely lead to problems. The default access level for anyone using the SMS Administrator tool is the same as the SQL Server systems administrator login. The systems administrator login gives you Database Owner (DBO) permissions on the database. In other words, SMS out of the box gives users unlimited access to its resources and, thus, full control of its mechanisms.
Consider this scenario. A Help desk employee develops an interest in the SMS Administrator tool he works with every day. Because Windows NT system resources aren't available to him, he expects that the same will be true with SMS. Imagine his surprise when he finds he can easily set up a mandatory job (e.g., send Microsoft Office 97 to all clients at all subsites in the SMS hierarchy). Imagine your surprise when your network clogs up as a result.
You can avoid this scenario if you secure your SMS system. SMS security isn't difficult to plan or implement. However, SMS depends heavily on SQL Server; thus, setting up SMS security requires a basic understanding of SQL Server security and how it interacts with SMS security.
SQL Server Security
SQL Server's standard security mode requires a user to log in manually each time that user establishes a connection to the server. (For more information about the standard security mode, see the sidebar "SQL Server Security Modes and SMS.") When you log in to a SQL Server database, you must enter a SQL Server login and the name of the database you want to access. A SQL Server login is a type of user account. SQL Server uses two types of user accounts: login and database user. SQL Server's Enterprise Manager (EM) creates logins independently of any database and gives a user access to SQL Server as an application. SQL Server's EM creates a database user as part of a database and gives a user access only to the database the user belongs to. Typical permissions for a login are to create a database or to change SQL Server configuration settings. For a database user, typical permissions are to insert, view, or delete database objects (e.g., tables and views).
SQL Server's EM maps logins to database users. Therefore, when you log in to SQL Server using your login and a database name, SQL Server knows which database user to use. Because a network user uses a database user to define permissions within the database, SQL Server knows the appropriate security context for this database connection.
How SMS and SQL Server Security Interact
Typically, if you want to set up security requirements for multiple users, you add the users to a group and arrange security requirements for the group. However, the SMS Security Manager doesn't recognize SQL Server groups. The SMS Security Manager accepts only database users to set security. Thus, if you work with groups, you still have the cumbersome task of defining security rights for each database user.
A workaround to this problem is to use aliases instead of groups. An alias is an extension of a mapping from a login to a database user. After creating a map, you can tell any number of logins to share the same database user. So, if you create one database user for each role in your SMS database (e.g., Helpdesk, Administrator) and map a login to that database user, you can extend as many of your other logins to this mapping as necessary to ensure that they all receive the same permissions.
Steps to Take
Now that you understand the basics of SQL Server security and how it interacts with SMS security, you are ready to take the necessary steps to secure your SMS system. Securing SMS involves six steps. You use SQL Server's EM to complete step 1 through step 3 and SMS Security Manager to complete step 4 through step 6. Here are the six steps:
Select Logins from the Manage menu. Using the Manage Logins dialog box, create a login for each user who needs to access SQL Server. In addition, create a login for each role you want to put in the database. (For example, in Screen 1, I created the login Helpdesk.) Set the default database to SMS and fill in a password.
Select your database from EM. Select Users from the Manage menu. Create database users for the organizational roles you want to use in your SMS database. Before you can create an alias, you must first map the login to the database user. (I created a login for each role.) Map database users to logins that don't refer to an existing person. If you use one of your regular logins (i.e., one that refers to a real employee), you'll need to change this mapping as soon as that employee leaves the company. Using a role-related login is a more generic approach.
When you add a user to the database, the lower part of the Manage Logins dialog box becomes available. From here, you can create as many aliases as you want. However, you're restricted to extending a login to one database user. If you attempt to extend the same login to two database users, Manage Users will remove the login from the previous database user's list of extended logins. Click Modify to save your changes. Screen 2, page 175, shows the list of extended logins I created.
Start the SMS Security Manager and log in using the systems administrator login or another login that has DBO equivalence in your SMS database. Notice all the security objects you can set. Table 1 (an excerpt from Microsoft SMS Books OnlineBOL) provides a detailed description of these objects. For the selected user (e.g., DBO), all objects are accessible. For a new user, the default setting on all objects is No access.
Select a user from the SMS Security Manager menu. Screen 3, page 175, provides an example of this menu. Notice that all objects are closed for this user. You can now change security settings for this user to View or Full access.
Consider using one of the security templates that come with SMS Security Manager. (Even if you don't intend to use these templates, you might want to look at them.)
Now you've set up security for SMS. However, before you begin using SMS, you must carefully determine which user needs which type of access to the database. In addition, you must determine what roles are necessary in your SMS environment. Some users might need to access the database in different roles (e.g., Helpdesk and Job Manager). You can either let some roles include others (e.g., job manager includes Help desk employee) or create a new role user (e.g., Job Manager plus Helpdesk). Whatever you do, always set up a convention to manage your users and use that convention rigorously.
SMS security isn't hard to implement. However, it's a key factor in protecting your SMS environment and maintaining the integrity of your inventory database. Considering the importance of the inventory database for all other SMS roles, you need to take security seriously.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
