Authenticating Logins - Authentication in SQL Server 7.0 and 2000
(Page 11 of 24 )
SQL Server 7.0 represents the final phase in transitioning away from the security architecture in Sybase SQL Server to one that integrates seamlessly with Windows NT/2000 security structure. Whereas SQL Server 6.5 implemented Windows logins in a very limited way by checking permissions on a registry key, SQL Server 7.0 and 2000 use Windows NT/2000’s built-in facilities for authenticating accounts and passwords. There is also full support for Windows groups within the database server itself, giving database administrators a new, richer set of options for assigning database permissions. Although SQL Server can still validate logins from its own internal tables, the real advances in security management are due to the flexibility derived from full integration of Windows accounts and groups.
The other major improvement in terms of security comes from the introduction of roles, collections of users in SQL Server similar to Windows groups. In SQL Server 6.5, a database user could be a member of only one other group in addition to the public group. The main reason for that limitation, as you saw in the last chapter, is the fact that the sysusers system table has only one column in which to hold group information. In contrast, SQL Server 7.0 and 2000 allow a user not only to be a member of multiple roles at the same time, but also to aggregate the permissions for all the groups of which he is a member. SQL Server’s database roles accept Windows accounts, Windows groups, and SQL Server database user accounts. This means that administrators now have a tremendously flexible set of mechanisms for assigning permissions in databases. I further cover database roles in Chapter 4.
Along with the database roles, SQL Server 7.0 introduces the concept of system roles. These roles cover the general categories of server administration and eliminate the need for all administrators to use the sa account for all tasks. It is possible now to grant administrative permissions at a more granular level so that someone who just needs to add accounts to the server cannot also drop databases or change service settings. The basic rule of any security plan is to grant users and administrators exactly the permissions they need to do their jobs and nothing more. Server roles make it possible to adhere to that rule.
To underscore the move away from the old Sybase SQL Server security architecture, Microsoft removed the option of using only SQL Server logins. SQL Server 7.0 now has only two login modes: “SQL Server and Windows NT” and “Windows NT only.” In SQL Server 2000, these have different names, “SQL Server and Windows” and “Windows only,” but they are the same modes as in SQL Server 7.0. In this book, I use the terms “SQL Server authenticated login” and “Windows authenticated login” to distinguish between the two ways in which a user’s login credentials can be validated, because how the login is authenticated greatly affects the options available for assigning database permissions. As far as the modes (as opposed to logins) go, however, the only real difference between them is that the Windows Only mode disables SQL Server authenticated logins.
 This is from SQL Server Security Distilled, second edition, by Morris Lewis (Apress, ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
Next: Tracing Login Network Traffic >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
