Authenticating Logins - The Effects of Windows on Authentication
(Page 3 of 24 )
At the time I am writing this second edition, the most likely question concerning the choice of operating systems should be whether it is worthwhile upgrading a database server from Windows NT or Windows 2000 to Windows 2003. The answer is, “It depends.”
I do not discuss Windows 2003 Server in this book because it has not had enough public scrutiny to determine if it can withstand a concerted attack by hackers. Windows 2003 seems to have improved security, but Windows 2000 is a more stable platform. In typical Microsoft fashion, there are whitepapers on the SQL Server web site recommending Windows 2003 as the preferred platform, but those papers are based on what should be true. Because Microsoft has a history of overestimating the security of its products, it makes more sense to me to let the new operating system survive its trial by fire before you move a business-critical database system onto it.
When you are evaluating Windows NT versus Windows 2000, Windows 2000 offers some clear advantages. As you will see later, all connections that use the Named Pipes or Multiprotocol network libraries will have their Windows accounts validated as part of the login process. There will, therefore, be an opportunity to capture the authentication traffic every time someone logs in. How often that opportunity will arise will depend on the environment. Just be aware that the tools to break the NT LAN Manager (NTLM) authentication algorithm used by Windows NT are easily available in multiple places on the Internet, and they do not require much expertise or knowledge to be successful.
NOTE To use NTLM version 2, the clients connecting to Windows 2000 will need to run Windows 2000/XP, or they will need to have a patch from Microsoft installed if they are running Windows NT, Windows 98, or Windows Millennium Edition. If either the client or the server does not support NTLM version 2, Windows will downgrade to NTLM.
Additionally, if you plan on using the TCP/IP network library, Internet Protocol security (IPSec) is a good way to protect the authentication process and the data stream by using the encryption option. Managing IPSec is far easier in Windows 2000 than in Windows NT, so you will probably find that the decision to use IPSec will lead you to choose Windows 2000 for its ability to force clients to use IPSec for all connections.
For SQL Server 6.5, because the database system relies on the operating system to handle most of the interactions outside the database server environment, the decision whether to run SQL Server 6.5 on Windows NT or Windows 2000 largely depends on which one offers the features you need at the network and operating system level as opposed to the database level. Factors such as hardware support, domain structure, management overhead, cost, and so on should carry greater weight, because unlike SQL Server 2000, SQL Server 6.5 doesn’t have any features available in Windows 2000 but not available in Windows NT. You can, therefore, safely make the decision based on which operating system is best for your environment. If you do decide to use Windows 2000, be aware that there are fewer compatibility issues if you perform a fresh installation of Windows 2000 and SQL Server 6.5 instead of upgrading Windows NT. Be sure to check the Microsoft TechNet Knowledge Base before you make the change, as there are a few known bugs when installing SQL Server 6.5 on Windows 2000.
NOTE For an explanation of how to implement IPSec to lock down a server, consult the following URL: http://www.microsoft.com/technet/itsolutions/network
/maintain/security/ipsecld.asp .
For SQL Server 7.0, the choice is similar to the one for SQL Server 6.5, in that SQL Server 7.0 does not have any features that are available in Windows 2000 and not in Windows NT. The choice of operating system will greatly depend on your overall network environment. There are many factors that make Windows 2000 easier to manage and more stable than Windows NT, but SQL Server 7.0 will run well on either one.
SQL Server 2000, however, almost requires Windows 2000. It will run on Windows NT, but many of the newest features require the services found only in Windows 2000. The optimal environment is to have SQL Server 2000 running on Windows 2000 in an Active Directory domain with all clients using Windows 2000 or XP. That is the environment that is currently the most secure, is the easiest to manage, and offers SQL Server 2000’s entire feature set.
NOTE This is a good place to mention that I will not consider Windows 9x , Windows Millennium Edition, or Windows XP Professional as platforms for any version of SQL Server. The special editions of SQL Server that will run on the client operating systems have limited options in terms of security, and Windows 9x in particular effectively has no security. In this book, I focus on securing production environments and only discuss the server versions of Windows NT and Windows 2000.
All the descriptions of how things work in this chapter through Chapter 6 are based on the most recent service packs available at the time of this writing. Here is the list of service packs used in all examples in this book:
- Windows NT 4.0 Service Pack 6 (6a now available, same results)
- Windows 2000 Service Pack 2 (3 now available, same results)
- SQL Server 6.5 Service Pack 5a with the post-5a hotfix
- SQL Server 7.0 Service Pack 4
- SQL Server 2000 Service Pack 2
SQL Server 6.5 Service Pack versions:
- 6.50.201 Original SQL Server 6.5 release
- 6.50.213 SQL Server 6.5 with Service Pack 1
- 6.50.240 SQL Server 6.5 with Service Pack 2
- 6.50.258 SQL Server 6.5 with Service Pack 3
- 6.50.281 SQL Server 6.5 with Service Pack 4
- 6.50.415 SQL Server 6.5 with Service Pack 5
- 6.50.416 SQL Server 6.5 with Service Pack 5a
Password Strength for Windows Logins The maximum length and the list of legal characters for passwords are different for Windows NT and 2000 accounts, but the explanation of password strength still applies to them. Passwords for Windows accounts should also be a minimum of eight characters, and they should have upper-and lowercase letters, numbers, and at least one special character. You should be especially careful with accounts that are members of the SQL Server 7.0 and 2000 sysadmin server role, which will be discussed a little later in the chapter, because those accounts will have the same privileges as the sa account; that is, full control over the server.
In general, Windows does a good job securing the authentication information as it travels the network, and it offers many different tools for monitoring login activity. Both Windows NT and 2000 can be configured to require passwords be a minimum length, and users with Windows 2000 domains also have the option of having the domain controllers require complex passwords that must consist of both upper- and lowercase letters, at least one number, and at least one special character. In contrast, SQL Server has no way to require a minimum length or complexity for SQL Server authenticated account passwords, although it is possible to modify the code in the sp_addlogin and sp_password stored procedures.
In the end, though, how SQL Server authenticates logins is irrelevant if users have weak passwords. Managing password strength for Windows authenticated logins requires cooperation between database and network administrators. Choices made at the domain level affect how easily an attacker can authenticate with SQL Server and what permissions she has once she logs in; therefore, network administrators need to understand their role in securing the database servers on their networks.
Now, we will change our focus from passwords to the authentication process itself. We will start with SQL Server 6.5 and then explore how improvements in SQL Server 7.0 and 2000 can make the database server more secure against unauthorized 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: Authentication in SQL Server 6.5 >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
