CHAPTER 17 • AUTOMATING ADMINISTRATION
674
You then learned how to create and use performance alerts to stop problems
before they start.
Using the Database Maintenance Plan Wizard: Many tasks need to be
performed on your server to keep it running smoothly and efficiently. You need
to back up databases and transaction logs, reorganize index and data pages
inside the database files, and check for database integrity regularly. Rather than
trying to remember to do all of that and the order to do it in, use the Database
Maintenance Plan Wizard to automate these processes for you.
Working with SQL Mail: Finally you learned that if you want to e-mail a
query to SQL Server and get a result set back, or have SQL Server e-mail you
from a procedure other than an alert, you need to configure SQL Mail.
Now that you know how to automate the tasks on your system, you need to know
how to secure your system as well. Let’s peer into the depths of SQL Server security in
our next chapter.
2627ch17.qxd 8/22/00 11:00 AM Page 674
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 18
Security and SQL
Server 2000
FEATURING:
Understanding Security Modes 676
SQL Server Logins 680
Fixed Server Roles 688
Creating Database User Accounts 691
Understanding Permissions 693
Database Roles 698
N-Tier Security 710
Monitoring SQL Server Logins with
SQL Profiler 712
Creating a Security Plan 717
Summary 719
2627ch18.qxd 8/22/00 11:08 AM Page 675
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
P
rotecting information—guarding access to an organization’s data—is much
like protecting a physical structure. For example, imagine that you own a
business and the building that houses it. You do not want the general pub-
lic to gain access to your building—only your employees should have
access. However, you also need restrictions on the areas to which your employees
have access. Because only accountants should have access to the accounting depart-
ment, and almost no one should have access to your office, you must put various
security systems in place.
Protecting SQL Server (your “building”) holds true to this concept: No one gets in
unless they’re granted access, and once users are inside, various security systems keep
prying eyes out of sensitive areas. In this chapter, we will discuss the methods used to
apply security to SQL Server.
Understanding Security Modes
To continue our analogy, for your employees to gain access to the building, they will
need some sort of key, whether a metal key or an electronic access card. For your users
to gain access to SQL Server, you will need to give them a key as well. The type of key
you give them largely depends on the type of lock—authentication mode—you use.
An authentication mode is how SQL Server processes usernames and passwords.
There are two such modes in SQL Server 2000: Windows NT/2000 Authentication
mode and Mixed mode.
Windows NT/2000 Authentication Mode
With this mode, a user can simply sit down at their computer, log on to the Win-
dows NT/2000 domain, and gain access to SQL Server. The process is a little bit dif-
ferent between Windows NT 4 and Windows 2000, though; here is how it works on
Windows NT 4:
1. The user logs on to a Windows NT domain; the username and password are ver-
ified by Windows NT.
2. The user then opens a trusted connection (see Figure 18.1) with SQL Server. This
means that SQL Server trusts Windows NT/2000 to verify the user’s password.
3. SQL Server will then try to match the username or group membership to an
entry in the Syslogins table.
4. Because this is a trusted connection, SQL Server does not need to verify the user
password; that is, SQL Server trusts Windows NT/2000 to perform that function.
2627ch18.qxd 8/22/00 11:08 AM Page 676
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
677
FIGURE 18.1
Using a trusted
connection, SQL
Server trusts
Windows NT/2000
to verify user
passwords.
In a domain that uses Windows 2000, the users can connect to SQL Server using the
Kerberos security protocol. Although an in-depth discussion of Kerberos is beyond the
scope of this book, here is a brief overview of how this security protocol works:
1. When the user logs on, Windows 2000 performs a DNS lookup to locate a Key
Distribution Center (KDC).
2. Once the KDC is located, the user’s machine logs on to the domain.
3. After the user’s machine successfully logs on, the KDC issues a special security
token called a Ticket Granting Ticket (TGT) to the user.
4. To access the SQL Server, the user’s machine presents the TGT to the SQL Server;
if the ticket is accepted, the user is allowed access.
It may be easier to think of Kerberos security as a trip to the carnival. If you have
ever been to a carnival and seen all of the rides, you probably know that to get on one
of those rides, you need a ticket. To get that ticket, you must buy them from a counter
at the gate of the carnival. Once you have those tickets in hand, you can give them to
the ride operator and enjoy yourself on the ride.
In Kerberos security, the services, such as SQL Server, would be considered the rides
that you want to access, but to use the services, you need to present a ticket. The
ticket you present is the Ticket Granting Ticket that you received from the KDC at
logon time, so you can think of the KDC as the counter at the carnival that sells the
tickets. Once you have this TGT, you can access any services to which you have been
given permission, including SQL Server 2000.
The main advantage to Windows NT/2000 Authentication mode is that users do not
have to remember multiple usernames and passwords. That will vastly increase secu-
rity, because there is less danger of users writing their passwords down and storing
SQL Server
Windows
Trusted connection
to SQLpassword
verified by Windows
Password
UNDERSTANDING SECURITY MODES
Administering SQL
Server
PART
IV
2627ch18.qxd 8/22/00 11:08 AM Page 677
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 18 • SECURITY AND SQL SERVER 2000
678
them in an unsafe place (such as a sticky note on their monitor). This mode also gives
you tighter reign over security, because you can apply Windows NT/2000 password
policies, which will do such things as expire passwords, require a minimum length for
passwords, keep a history of passwords, and so on.
One of the disadvantages is that only users with the proper net-library (Named
Pipes, TCP/IP, or Multi-Protocol) can open a trusted connection to SQL Server. This
means that someone like a Novell client running the IPX net-library cannot use Win-
dows NT/2000 Authentication mode. If it turns out that you have such clients, you
will need to implement Mixed mode.
Mixed Mode
Mixed mode allows both Windows NT/2000 Authentication and SQL Server Authenti-
cation. In SQL Server Authentication:
1. The user logs on to their network, Windows NT/2000 or otherwise.
2. The user opens a nontrusted (see Figure 18.2) connection to SQL Server using a
username and password other than those used to gain network access. It is
called a nontrusted connection because SQL Server does not trust the operating
system to verify the user’s password.
3. SQL Server matches the username and password entered by the user to an entry
in the Syslogins table
FIGURE 18.2
With a nontrusted
connection, SQL
Server verifies user
passwords itself.
SQL Server
Windows
Trusted connection
to SQLpassword
verified by Windows
Password
2627ch18.qxd 8/22/00 11:08 AM Page 678
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
679
The primary advantage here is that anyone can gain access to SQL Server using
Mixed mode, regardless of the net-library used. This means that Mac users, Novell
users, Banyan Vines users, and the like can gain access. You could also consider this to
be a second layer of security, because if someone hacks into the network in Mixed
mode, it does not mean that they have automatically hacked into SQL Server at the
same time.
Ironically, multiple passwords can be a problem as well as an advantage. Consider
that users will have one username and password to log on to the network and a com-
pletely separate username and password to gain access to SQL Server. When users
have multiple sets of credentials, they tend to write them down and thus breach the
security system you have worked so hard to set up.
Setting the Authentication Mode
As an administrator, you will probably set the authentication mode no more than
once, at installation time. The only other time you might need to change the authen-
tication mode would be if changes were made to your network. For example, if you
had set your SQL Server to Windows NT/2000 Authentication mode and needed to
include Macintosh clients, you would need to change to Mixed mode.
It is interesting to note that although most things in SQL Server can be done
through either Enterprise Manager or Transact-SQL (T-SQL), setting the authentica-
tion mode is one of the rare things that can be done only through Enterprise Man-
ager. The next series of steps takes you through setting the authentication mode.
1. Open Enterprise Manager by selecting it from the SQL Server 2000 group under
programs on the Start menu, then right-click your server and select Properties.
2. Select the Security tab.
3. In the Authentication section, select SQL Server and Windows NT/2000. This
will set you to Mixed mode for the rest of the exercises.
UNDERSTANDING SECURITY MODES
Administering SQL
Server
PART
IV
2627ch18.qxd 8/22/00 11:08 AM Page 679
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 18 • SECURITY AND SQL SERVER 2000
680
4. Click OK to close the Properties dialog box.
Now that you have set the proper authentication mode, it is time to move forward
and give your users a key to your building with SQL Server logins.
NOTE On Windows 9x you will not be able to change the authentication type from the
default.
SQL Server Logins
Once you have decided what type of lock (authentication mode) to use on your build-
ing, you can start handing out keys so that your employees can gain access. A real key
will give your employees access to the building as a whole, but to none of the resources
2627ch18.qxd 8/22/00 11:08 AM Page 680
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
681
(such as filing cabinets) inside. In the same way, a SQL Server key—a login—will give
your users access to SQL Server as a whole, but not to the resources (such as databases)
inside. If you are a member of the sysadmin or securityadmin fixed server roles (dis-
cussed later in this chapter), you will be able to create one of two types of logins: stan-
dard logins (such as the metal key in our analogy) and Windows NT/2000 logins
(such as the newer electronic access card).
Standard Logins
You learned earlier in this chapter that only clients using the Named Pipes, Multi-
Protocol, or TCP/IP net-libraries can make trusted connections to SQL Server (where
SQL Server trusts Windows NT/2000 to validate the user’s password). If the user
(such as a Macintosh or Novell client) for whom you are creating a login cannot
make a trusted connection, you must create a standard login for them. In the next
series of steps, you will create two standard logins that will be used later in the
chapter.
NOTE Although you can create standard logins in Windows NT/2000 Authentication
mode, you won’t be able to use them. If you try, SQL Server will ignore you and use your
Windows NT/2000 credentials instead.
1. Open Enterprise Manager and expand your server by clicking the + sign next to
the icon named after your server.
2. Expand Security and click the Logins icon.
3. Choose Action ➢ New Login.
4. In the Name box, type SmithB.
5. In the Authentication section, select SQL Server Authentication.
6. In the Password textbox, type password.
7. Under Defaults, select pubs as the default database.
SQL SERVER LOGINS
Administering SQL
Server
PART
IV
2627ch18.qxd 8/22/00 11:08 AM Page 681
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CHAPTER 18 • SECURITY AND SQL SERVER 2000
682
8. Click OK.
9. In the Confirm New Password textbox, type password.
10. Click OK and notice your new Standard type login in the contents pane.
2627ch18.qxd 8/22/00 11:08 AM Page 682
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
683
11. Choose Action ➢ New Login.
12. In the Name box, type GibsonH.
13. In the Authentication section, select SQL Server Authentication.
14. In the Password textbox, type password.
15. Under Defaults, select pubs as the default database.
16. Click OK.
17. In the Confirm New Password textbox, type password.
Now you are ready to test your new logins to make sure they work; let’s do that
now with the SmithB login:
1. To test the new login, open Query Analyzer by selecting it from the
SQL Server 2000 group under Programs on the Start menu.
2. Under Connection Information, select Use SQL Server Authentication.
3. In the Login Name box, type SmithB.
4. In the Password box, type password.
5. Click OK and notice the title bar. It should read “sqlserver.pubs.SmithB.”
WARNING A standard login, sa, is created at installation time with a blank default
password. Because the system administrator (sa) has godlike power over the system, you
should choose a new password immediately.
SQL SERVER LOGINS
Administering SQL
Server
PART
IV
2627ch18.qxd 8/22/00 11:08 AM Page 683
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét