Table of Contents

Chapter 3

Planning Database and Server Security

Certification Objectives *

From the Classroom *

SQL Service Account *

Standard versus Integrated Security Model Assessment *

Leveraging the Windows NT Group Structure *

From the Classroom *

Using NT Groups for SQL Server Access *

Planning the Use and Structure of SQL Server Roles *

Fixed Server *

Fixed Database *

Description *

User-Defined Database *

Mapping Windows NT Groups Directly Into a Database or Mapping Them to a Role *

Assessing Which Windows NT Accounts Will Be Used To Run SQL Server Services *

Planning An N-Tier Application Security Strategy *

Planning the Security Requirements for Linked Databases *

Assigning Database Access to Windows NT Accounts *

SQL Server Login Accounts *

The Guest User Account *

The DBO User Account *

The SA Account *

Certification Objectives

Since SQL Server controls the security in the client/server environment, it is the responsibility of the database administrator to enforce security policies. In order to set up the proper security procedures, database administrators must understand the process of creating and managing users, logins, and groups. Security policies and procedures can get very complex because security occurs in three levels:

This chapter will explore the different stages that SQL Security occurs in so that you can maintain proper security.

Login Authentication Modes

The first stage that security occurs is at the server level where the user logs in. There are two types of login authentication modes:

It is up to the database administrator to decide what security mode to set up for SQL Server.

In Windows NT authentication mode, SQL Server authentication is dependent on Windows NT authentication. SQL Server "trusts" the Windows NT login and approves access to the second level of security. There are many benefits of using Windows NT authentication instead of SQL Server authentication. Windows NT security is much more feature-rich. Such features are as follows: accounts can be locked out, passwords are encrypted, and passwords can expire.

On the Job: As the name implies, Windows NT authentication on SQL Server does not exist under Windows 95/98.

The mixed mode security mode utilizes both the Windows NT and the SQL Server authentication. If the user logs in to SQL Server without a login, SQL Server will allow access based on the NT user profile. If the user logs with in with SQL Server as a SQL Server based logon, the user will be given access based on SQL Server authentication. Since Windows NT authentication does not exist in non-Windows NT operating systems, SQL server must run in mixed mode. SQL Server must also run in mixed mode if connection with the Internet and a non-Windows client is required.

Permission Validation

Once a user successfully logs in to Microsoft SQL Server, they must be given explicit access to each database. This is done so that a valid SQL Server can’t have access to all the databases in the server. Once logged in, the database user only has select permission to the system tables. If their account is not valid for a particular database, and the guest account exists, they would be given guest access and rights. If a user is able to access the database, they can proceed to the next level of security authentication, which are the database objects. SQL Server checks access to each database object to ensure the permissions of the user’s login is granted. This is done so that a valid SQL Server user in one database does not have access to all of the databases on the server.

From the Classroom

SQL Service Account

The service account that you use to run SQL Server must have local administrator privileges on your SQL Server. With these powerful account privileges, however, a knowledgeable user can make any changes to your data, such as including deleting it. Therefore, all precautions must be taken to ensure that this account is not compromised. Here are a few tips for securing this account.

Also remember that your service account does not have to be a Domain Administrator unless your SQL Server is also a Domain Controller.

by David Smith, MCSE + Internet

Planning SQL Server Security

It is imperative that the database administrator exercises greatest caution in granting security to SQL Server. It is the administrator's job to make sure all users are given only enough permissions to do their job. It is never acceptable for the database administrator to assume that the user will restrict themselves to only data to which they are supposed to have access. It is very common that a well-intentioned user will stumble upon sensitive data and/or accidentally delete the entire database. The database administrator must never make assumptions on users. The highest security should be given to all database objects to protect the data and the users from themselves.

Standard versus Integrated Security Model Assessment

Microsoft SQL Server 7.0 only supports two security modes: standard and mixed. Despite this, it is still important that we understand the difference between the standard and integrated security model. This is because the mixed model is both a standard and integrated security model. SQL Server 6.5 supported three security modes: standard, integrated, and mixed.

With standard security, SQL Server maintains its own security by requiring the user to authenticate if they wish to access the database. The standard security model is the most secure, since both types of authentication is used. Since this model is separate from Windows NT, you do not need to load any additional network libraries. You must, however, install logins and password for every user you want to have access to the databases in SQL Server. The Standard Model is the most useful in a non-Windows NT environment such as Novell Intranetware, where all authentication is done by Intranetware itself.

With the Integrated Security Model, the best benefit is less administration. Windows NT now does all the authentication for SQL Server. Therefore separate logins and passwords do not have to be created, and users no longer have to remember multiple logins and passwords. There are two requirements that must be fulfilled before you decide to implement the integrated security model.

Switching over from the default integrated authentication model to the mixed security mode requires some steps. Go to SQL Server’s Enterprise Manager, expand the SQL Server group, and right-click on desired server in which you want to implement integrated security model. Then click Properties. Click on Windows NT only under the Security Tab as shown in Figure 3-1.

Figure 1: Security Tab in SQL Server properties

Leveraging the Windows NT Group Structure

In SQL Server 7.0 Microsoft introduces new powerful roles to replace SQL Server groups. If you want to, however, give access to all members of a Windows NT group, you can grant the entire Windows NT local or global group access to the database. To give a Windows NT group access to a SQL Server database, go to SQL Server’s Enterprise Manager and expand the SQL Server Groups. Expand your desired server and right-click on Login. Your screen should look familiar to Figure 3-2. Click Windows NT Authentication and enter the Microsoft Windows NT group to add.

From the Classroom

Using NT Groups for SQL Server Access

If you are using NT Server Accounts as your primary user account for SQL Server, you need to use User Manager For Domains to control access to your server. This simplifies your administration for the following reasons:

—David Smith, MCSE + Internet

Figure 2: SQL Server Login Properties for New Login

Planning the Use and Structure of SQL Server Roles

With SQL Server roles, you can categorize groups of users. With these categories, you can assign, deny, and revoke permissions to all members of the group. Members inherit permissions of the entire role. If a member’s job situation changes, you can delete him from the group and/or add him to another group. Roles make administration much easier because you do not have to set permissions for every user. Users can belong to more than one role. The four types of server roles are:

All users in the database belong to that database’s public role. If you wish all members of that database to receive the same permissions, assign the permissions to the public database role.

Exam Watch: Fixed server, fixed database, and public roles are built-in roles and cannot be dropped.

Fixed Server

Fixed server roles have permissions on all databases in a server. All member of this role gains all permissions applied to it. The fixed server roles are listed in Table 3-1..

Fixed Server Role Description
Sysadmin Has full access to all database objects and is able to perform any task.
Serveradmin Can shut down server and set all server-related options
Setupadmin Can install replication and have full access to extended procedures
Securityadmin Has access to set login and CREATE DATABASE permissions
Processadmin Can manage all SQL Server related processes.
Dbcreator Has authority to create and alter all the databases in the server.
Diskadmin Has full access to all disk files.

Table 1: Fixed Server Roles

Fixed Database

The fixed database role only exists in the database level. Every member added to this role gains all permissions applied to it. The fixed database roles are listed in Table 3-2.

Fixed Database Role Description
db_owner Has full access to the database.
db_accessadmin Can manager user IDs.
db_datareader Can give the read (SELECT) permissions to any database object.
db_datawriter Can give the write (INSERT, UPDATE, and DELETE) permissions on any database object
db_ddladmin Can issue ALL DDL (Database Definition Language) which is used to define and declare all database objects.
db_securityadmin Can manage all permissions, ownerships, and roles.
db_dumpoperator Can execute the DBCC, CHECKPOINT, and BACKUP statements.
db_denydatareader The opposite of db_datareader. Can deny the read (SELECT) permissions to any database object.
db_denydatawriter The opposite of db_datawriter. Can deny the write (INSERT, UPDATE, and DELETE) permissions on any database object.

Table 2: Fixed database roles

User-Defined Database

You may have a group of users that wish to perform a particular task or tasks and are not classified in the fixed database or fixed server roles. If this is the case, you can create a user-defined database role. This role is similar to the fixed database role in that it exists only in the database level and only affects the database it is created in. Exercise 3-1 will show you how to create your own user-defined database role.

Exercise 3-1: Creating a user-defined database role

  1. Select Start | Programs | SQL Server 7.0 | Enterprise Manager.
  2. Expand the SQL Server group and then expand the desired server where the database you wish the user-defined database role is located.
  3. Right-click on the database and click on database roles. You should see a screen similar to Figure 3-3.
  4. Enter the name of the role.
  5. Click OK.

Figure 3: Database Role Properties of a New Role

Mapping Windows NT Groups Directly Into a Database or Mapping Them to a Role

Using Windows NT Groups greatly simplifies administration. You do not have to set up access and permissions for each user. You can add them into groups and map them to the database so all members can access the database. You can also map them to a role to give them permissions on the database or server level. Exercise 3-2 will show you how to map a role to a Windows NT Group.

Exercise 3-2: Mapping a Windows NT Group To a Role

  1. Follow Exercise 3-1 to Add a New Role.
  2. Click on Add.
  3. Click on the group of users to add.
  4. Click OK.

Assessing Which Windows NT Accounts Will Be Used To Run SQL Server Services

Microsoft SQL Server, SQL Server Agent, and Microsoft Distributed Transaction Controller (MS DTC) are SQL Server services that must be run under NT as Windows NT Services. In order for these services to run, each service must be run under a Windows NT account. The database administrator can choose what kind of a Windows NT account under which to run the services. There are three to choose from:

The local system account and the local user account only run on the local system. This account is a bad choice to use if you plan on communicating with other SQL Servers, because it can’t access the network. The Domain User Account runs on the entire Windows NT domain. It has access to both local and network resources. This account is generally the best choice if your SQL Server communicates with other database servers on the network. The domain user account must have full access to local and network resources, Because of this configuration, it must be a member of the Administrators local group and be configured to logon as a network service. Though SQL Server has three different services, you can run all the services on separate accounts or one account. It might be more convenient to run all the services on one account. Exercise 3-3 will show you how to set up a SQL Server service to log on as a different user account.

Exercise 3-3: Setting Up a SQL Server Service To Log On As A Different User Account

  1. Click on Start | Settings | Control Panel.
  2. Double-click the Services Icon.
  3. Double-click SQLServerAgent. You should see a screen similar to Figure 3-4.
  4. In the Services dialog box under Logon As, click on This Account, and enter an account in your server.
  5. Type the password and confirm the password.
  6. Click OK.

Figure 4: Services Dialog Box

On the Job: In replication, it is better for both the Publishers and Subscribers to be using the same domain user account. Don’t forget to make them members of the local administrator group!

Exam Watch: Though SQL Server can scale from Windows 95/98 to Windows NT, Microsoft SQL Server services only run on Windows NT platforms because Windows 95/98 does not support services. Windows 95/98 simulates services. You do not have to create user accounts for them.

Planning An N-Tier Application Security Strategy

As the enterprise gets bigger, the client/server system shifts from being a two-tier system to an n-tier system, also called a multitier system. In a multitier client/server system, the client logic is split in two places:

This type of architecture is much more efficient in that the application can be broken into Active-X components and DLLs. Once broken down into its components, it can be put into one server, instead of distributing it to all the clients. With a "component-ized" application, it is much more difficult to apply security since the application exists in more than one place.

SQL Server uses application roles to secure components in a multi-tier system. Application roles shift the role of the security policeman from the SQL Server to the application. With application roles, users are restricted to the type of application that is used to access the data. Users can no longer access the data outside the application, such as with Microsoft Access or SQL Server Query Analyzer. They also lose all their permission applied to their login account or database roles. You may wish to use application roles in order to control the type of access the user makes to the database. These roles insure that users will not run queries outside your control that may affect the performance of your database. Application roles are disabled by default and must be activated by sp_setaddrole system stored procedure. Exercise 3-4 will show you how to create an application role.

Exercise 3-4: Creating an Application Role

  1. Click on Start | Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Expand the SQL Server group, and then your server. Expand Databases and then the specific database in which you want to create the role.
  3. the desired server where the database you wish the application to be created in.
  4. Right-click Database Roles and click New Database Role. Your screen should look similar to Figure 3-5.
  5. In Name, enter the name of your application role.
  6. Select the Application role.
  7. Enter the password and click OK.

Figure 5: Application Role Properties

Microsoft Transaction Server and other mid-tier security mechanisms take a different approach to application roles. While application roles only allow access through the client application, MTS (Microsoft Transaction Server) leverages the security features of Windows NT on the users and groups. Security is set at the database level, which is the lowest level. This is the preferred method because it provides security on all data access methods, including the following: applications, Microsoft Excel, or SQL Server Query Analyzer.

Planning the Security Requirements for Linked Databases

In SQL Server 7.0, Microsoft includes native support to OLE DB data sources, called linked databases. In a linked database environment, a distributed query can access data on all the linked databases. A sending SQL Server must provide a login name and password when it connects to a database or linked server. In order to enable the sending of the login and password, login mappings must be created between the two servers. This is done with the stored procedure ‘sp_addlinkedsrvlogin.’ When ‘sp_addlinksrvlogin’ is used, the user uses the same permissions as their own login, or they can emulate the permissions of another user. Windows NT does not allow security account information to be passed down from the Windows NT account to the SQL Server account. Therefore, a SQL Server authenticated account must be used. Exercise 3-5 will show you how to add a linked server login.

Exercise 3-5: Add A Linked Database on a Server Login

  1. Click on Start | Programs | Microsoft SQL Server 7 | Enterprise Manager
  2. Expand the SQL Server group and expand the desired server.
  3. Click on Linked Servers.
  4. Right-click the linked server and click on Properties.
  5. On the security tab, click on the login to add.
  6. If the login is connecting to the linked server with its own login credentials, select impersonate. If not, enter the remote user login and password.

Planning Database Permissions

After a user passes security clearance at the server level, they must have permission at the database level. In order for a user to have access to the data, they must be a defined-user for that particular database.

Assigning Database Access to Windows NT Accounts

Instead of granting access to an entire Windows NT group, you can also assign database access to individual Windows NT accounts. Exercise 3-6 will show you how to grant a Windows NT user access to a database.

Exercise 3-6: Granting a Windows NT user access to a database

  1. Click on Start | Programs | SQL Server 7.0 | Enterprise Manager
  2. Expand the SQL Server group and expand your desired server.
  3. Right-click your desired database and click New Database User. You should see a screen similar to Figure 3-6.
  4. Next to Login Name, click on the Windows NT user name.
  5. Then enter the User Name.
  6. Click OK.

Figure 6: Database User Properties for New User

SQL Server Login Accounts

If you do not wish to use the Windows NT authentication model, you will have to add individual SQL Server accounts to access the database. Exercise 3-7 shows you how to add an individual SQL Server login account.

Exercise 3-7: Adding a SQL Server Login Account

  1. Click on Start | Programs | SQL Server 7.0 | Enterprise Manager
  2. Expand the SQL Server group and expand your desired server.
  3. Right-click Logins and click New Login. You should see a screen similar to Figure 3-7.
  4. Enter the name of the SQL Server login.
  5. Click SQL Server Authentication and enter a password.
  6. You can also enter the default database and language.
  7. Click OK.

Figure 7: SQL Server Login Properties For a SQL Server Login

The Guest User Account

Without a SQL Server user account, the user will default to the guest user account if it exists. The administrator can restrict this account to the most basic access to allow all server logins basic access to a database. The guest user account was created when the tempdb, master, and msdb system database were installed. Despite this, the guest user account can be changed in all databases except the master database and tempdb. For the other user databases, you must add the guest user account after you create your database. To add a guest user account, execute the following:

Exec sp_adduser guest

The DBO User Account

The database owner (DBO) is the administrator for the database. It has full access to all operations and rights. The DBO exists only at the database level. This means that a user who is a DBO in one database might not necessarily be the DBO in another in the same server. When a user creates a database, he or she is automatically a DBO. The SA must explicitly grant all other user permissions except the SA. To transfer DBO to another user, execute the following:

EXEC sp_changedbowner 'login id'

The SA Account

The system administrator (SA) is similar to the DBO except it is of the entire server. It has the same access and permissions as the DBO on all the databases in the server. This account is the administrator at the server level. If another user is given ownership of a database, SA can still control the database as owner. This is a built-in SQL Server account and should only be used sparingly. All administrators should be made members of the sysadmin fixed-server role. When SQL Server is installed, SA is created without a password. Exercise 3-8 will show you how to set the password for SA.

Exercise 3-8: Setting the password for SA

  1. Click on Start | Programs | Microsoft SQL Server 7.0 | Enterprise Manager
  2. Expand the SQL Server group and expand the desired server.
  3. Click Logins.
  4. Right click on sa in the right panel, and click on Properties. Your screen should now look similar to Figure 3-8
  5. Input the password in the Password box.
  6. Click OK.

Figure 8: SQL Server Login Properties for SA

Certification Summary

One of the best ways to protect data is through proper security. Security is implemented at three levels: the server, the database, and the database object. A user must have permission to access all three levels in order to do most tasks.

The first level is the server. Security through the server is through login authentication. SQL Server supports two types of login authentication: mixed mode and Windows NT authentication mode. Mixed mode supports access through both the SQL Server and Windows NT login authentication. Windows NT authentication only supports access through Windows NT.

The second level of authentication is the database. In order for the user to have access to the database, they must be a user of that database. There are many ways to be given permission for this database. You can acquire permission through the Windows NT group, SQL Server roles, or as an individual user. It is much easier to administer a group and a role than several individual users.

The final level of access is the database object. Users can access the database if their NT account, SQL Server login account, or the guest account is given permission to do so. The DBO (database owner) has full access to its database. The SA (system administrator) accounts have full access to all the databases.

By becoming familiar with the different levels of authentication, the database administration can secure the database from the users. Tightening security will help the database administrator protect the database users from themselves.

Two-Minute Drill