Security is not a product, but a process; not a destination, but a journey. Login security is the first step in securing your server, and although it doesn't stop there, understanding the fundamentals of security is essential to ensuring you a smooth journey, as you'll see in this chapter from SQL Server Security Distilled, second edition, by Morris Lewis (Apress, ISBN 1590592190).
LOGIN SECURITY IS the first step in securing a server. The basic premise is that an attacker cannot hurt what he cannot see; therefore, you will spend a lot of time ensuring unauthorized users never log into SQL Server successfully. It may seem as though authenticating logins should be a straightforward process of comparing account names and passwords to a list of authorized users but, in fact, it is a little more complicated than that. If the network were perfectly secure from protocol analyzers and other network packet capture tools, you could ignore how accounts and passwords are exchanged between a client and SQL Server. If everyone were honest and trustworthy, you would not need to verify a user’s identity before she could access data. If there were no secrets, you would not need to hide sensitive or private data from prying eyes. Because none of these conditions exist, you need to prevent passwords from being stolen, identities from being impersonated, and data from being seen by the wrong people.
What you will find as you go through this chapter is that the choice of how SQL Server authenticates will also affect your options for securing the data as it passes between the client and server. Microsoft did not give all network communication protocols the same features, and authentication protocols have evolved over the years as attackers found flaws. Each of the choices in this chapter has strengths and weaknesses that make it appropriate for some environments and not for others and, in some cases, you will need to use operating system functionality to strengthen the security even further.
The end result of everything covered in this chapter is to ensure only authorized users can connect to SQL Server. This is the first part of the twin processes of authentication and authorization. I cover how to manage authorization in Chapter 3 for SQL Server 6.5 and in Chapter 4 for SQL Server 7.0 and 2000. For now, let’s turn our attention to the primary mechanism for authenticating auser’s identity: passwords.
Password Strategies
In early versions of SQL Server, user passwords were not encrypted in the syslogins table. That meant anyone with sa privileges could read the password just by querying syslogins. There was a demand to change that behavior; therefore, SQL Server 6.5, 7.0, and 2000 encrypt users’ passwords using a one-way hashing algorithm, the details of which Microsoft will not disclose, before storing them in syslogins for SQL Server 6.5 and sysxlogins for SQL Server 7.0 and 2000. If you look at the source code for sp_addlogin,you will see the insert statement that adds the user’s login name, system user ID, and password to the table. To encrypt the password, the stored procedure uses an undocumented function named pwdencrypt(),which takes the unencrypted password as its single parameter and returns the hashed value as its output. Because it is a one-way hashing function, it is impossible to reverse the process to retrieve the unencrypted password.
NOTE As a demonstration that there are no absolutes in security, NGSSoftware found in 2002 that the algorithm used to store the password is flawed. This is a common problem in security, and it is the reason Microsoft should divulge how it secures passwords so that the world's security community can scrutinize them for flaws. You will look at the NGSSoftware discovery shortly.
There is another undocumented function, pwdcompare(), which takes an unencrypted password and an encrypted text as its two parameters. It returns avalue of TRUE if the hash of the unencrypted password matches the encrypted text; otherwise, it returns a value of FALSE. It is possible to mount a brute force attack on user passwords by generating all possible character combinations and testing them using pwdcompare(), but long passwords will make this attack take avery long time.
The issue of whether a system administrator can see user passwords is largely pointless where SQL Server is concerned. The system administrator has so many options that do not require knowing a user’s password that it seems like a waste of time to worry about whether sa can read passwords. There is an old axiom that you must trust your administrators, and it is just as true for database administrators as any other kind of administrator.
Password encryption does have a role to play, however, in protecting the users’ passwords when the syslogins or sysxlogins table leaves the protection of SQL Server. For example, every full database backup of the Master database has a copy of the syslogins or sysxlogins table. If you open the backup file for versions prior to 6.0, you will see the accounts and passwords in clear text. For SQL Server 6.0 and later, the names will be in clear text, but the passwords will be a string of unreadable characters. These characters are the text representation of the binary number that is the result of the hashing function used in pwdencrypt().
If an attacker can get a copy of the backup file, he can simply restore it to a server under his control. In addition, a client can request a trusted connection, even if the server is configured only for Standard Security logins. The attacker can log into SQL Server using the Administrator local account and read the contents of syslogins. By encrypting the passwords, it makes the process of gaining access to the original server more difficult—not impossible, but more difficult.
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.
The best password encryption scheme in the world will not protect you against weak passwords; therefore, let’s look at the options for creating strong passwords for SQL Server authenticated logins.
Sp_password is the system stored procedure used by SQL Server 6.5, 7.0, and 2000 to set and change passwords. Looking at the text of this stored procedure can help you understand what options you have for passwords. In SQL Server 6.5, user passwords can be up to 30 characters long, and there is no restriction on the characters used in the password. SQL Server 7.0 and 2000 both allow passwords to have as many as 128 characters, and they permit all characters on a standard keyboard. The maximum number of characters in a password is important information, but the real key to strong passwords is the kinds of characters used.
All three versions of SQL Server considered in this book interpret passwords differently for case-sensitive and case-insensitive sort orders. If the server has acase-sensitive sort order, case is significant in the password. If, however, the server uses a case-insensitive sort order, passwords are converted to uppercase before they are stored in the syslogins (SQL Server 6.5) or sysxlogins table (SQL Server 7.0 and 2000) and before they are compared to the entries in those tables when a user logs in. For example, Password0 would be stored and compared as PASSWORD0 on a server with case-insensitive sort order. You have, therefore, slightly weaker passwords when you use case-insensitive sort orders.
To get an idea of how strong a password might be, let’s look at how many combinations a brute force attack would have to try in order to find a user’s password.
Let’s first start with a password consisting of strictly uppercase letters A through Z. Each character in the password will have 26 possible options, and no character is dependent on any other character. That means there are 26 to the power of n possible combinations, in which n is the total number of characters in the password. A six-character password seems to be the norm whenever I go to client sites, so there are 266, or 308,915,776 possible six-character passwords using just uppercase letters. In contrast, if passwords contained both upper- and lowercase letters, there would be 52n possible combinations, or 19,770,609,664 possible six-character passwords.
If you assume an attacker could check 1,000 passwords per second (and that’s a big assumption), it would take 85.81 hours to try every combination of uppercase letters in a six-character password and 5,491.84 hours to try every combination of a six-character password that just used upper- and lowercase letters. As you can see, case-insensitive sort orders have a tremendous impact on how long it takes an attacker to break a user’s password.
While I was writing this book, NGSSoftware published a paper (http://www.nextgenss.com/papers/cracking-sql-passwords.pdf) on its web site detailing its discovery of how pwdencrypt() works. It turns out that the data stored in syslogins and sysxlogins is actually a combination of two representations of the passwords, one with the case intact and one with the letters converted to uppercase. That means that case insensitivity is irrelevant if an attacker can get the hashed representations of password from syslogins or sysxlogins. The sort order will only matter if an attacker attempts to log into SQL Server, because SQL Server controls how it interprets the data in the system table. An attacker with direct access to the system tables will be able to narrow his attack to just uppercase letters.
NOTE If you want to see some code, NGSSoftware includes a sample program in its paper. Additionally, it is not clear if the behavior described in the paper occurs in SQL Server 2000.
The question then becomes, how do you make it harder to determine passwords using a brute force attack? The answer is to make the password longer and to include nonalphabetic characters.
If you include at least one number (0 through 9) in your password, an attacker must assume that the number may occur anywhere in the password. That results in 36n or 62n combinations, depending on whether lowercase letters are available. In either case, the inclusion of numbers increases the number of combinations significantly. For six-character passwords, there are 2,176,782,336 possible passwords consisting of all uppercase letters plus numbers and 56,800,235,584 possible passwords consisting of upper-and lowercase letters and numbers. Adding the ten special characters that are on the number keys on standard keyboards—!, @, #, $, %, ^, &, *, (, and )—changes the number of combinations to 46n or 72n, depending on whether the password has lowercase letters.
Lengthening the password will have the greatest effect on its strength. For example, 366 is 2,176,782,336, 367 is 78,364,164,096, and 368 is 2,821,109,907,456. Just changing from six to eight characters changes the time to crack a password from 604.66 hours to 783,641.64 hours, assuming 1,000 password attempts per second. Requiring at least one special character in addition to uppercase letters and numbers increases an eight-character password to 468, or 20,047,612,231,936, possible combinations. That will take 5,568,781,17 hours (635.7 years) to try every combination. This is the prime reason not to have short passwords for administrator and database owner accounts. Make it a rule that all your passwords are at least eight characters long.
A brute force attack is not the only type of attack that could compromise your users’ passwords. A dictionary attack uses a program to try combinations of words from a list of words commonly found in passwords. The benefit of this attack is that it treats words as discrete units rather than trying all the combinations of letters, numbers, and special characters. For example, “Password0” has eight characters, but a dictionary attack considers it one word with one number, which would require 11 tries to guess, for example, Password, Password0, Password1, Password2, Password3, and so on. If the attacker can assume all passwords are stored in uppercase, then he can configure the program to try all the words in uppercase to find the words in the password and then try all combinations of upper- and lowercase to find the exact letters used in the password.
In general, dictionary attacks take a very small fraction of the time required for a brute force attack if the list of words is well chosen. The trick to defeating this kind of attack is not to use common words (for example, “password,” “server,” “windows,” “SQL,” and so on), common names (for example, “John,” “Mary,” and so on), common place names (for example, “New York,” “headquarters,” and so on), or dates (for example, birthdays). In addition, break up the words with numbers or special characters in multiple, odd places such as in the middle of the word (for example “P@s$w0rd”). Doing so fragments the words to the point that the dictionary either does not work or must be increased in size to compensate for common fragments.
CAUTION Attackers are catching on to the common habit of replacing the letter A with the @ symbol and the letter O with the number 0. Such typical replacements will not materially increase the difficulty of a dictionary attack.
The best way to defeat both the brute force and dictionary attacks is to use randomly generated sequences of letters, numbers, and special characters. As a general rule of thumb, if you can recognize words or sequences of numbers, the password is not random enough. The recommended practice for securing the sa account in SQL Server 7.0 and 2000 is to give it a very long password—at least 20 characters if not longer—composed of random strings of upper- and lowercase letters, at least five numbers, and at least five different special characters; write it down on a piece of paper; and then place the paper in a safe or safety deposit box for safekeeping in case of emergencies.
For users, my recommendation is that you specify eight characters as the minimum size and that you provide a list of words that should not be in the password. The argument for eight characters is that anything smaller is too easy to guess with a brute force attack, and any password longer than eight characters will just enhance security. Randomly generated passwords are probably not feasible because users will tend to forget them. Words that are relatively uncommon and that are broken up with numbers or special characters in unusual places will provide good security without being difficult to remember. There is always atradeoff between having highly secure passwords and users’ ability to remember them, and minimum eight-character passwords are a good compromise.
NOTE A list of simple programs that you can use to test your password strength is available from http://www.sqlsecurity.com.
Passwords in Enterprise Manager
Passwords are not just stored in syslogins and sysxlogins. Enterprise Manager stores the server registration information, including the account and password, in the registry in the following key: HKEY_CURRENT_USER\Software\Microsoft\ MSSqlServer\SQLEW\Registered servers\SQL6.5\ server_name.
server_name is the name you use to identify the server. Even though that key stores its data in binary format, the Windows 95 registry editor, regedit.exe, which ships with Windows NT and 2000, will translate the binary into text for you. The only way to keep passwords from being stored in the registry in clear text is to use trusted connections (Windows logins) instead of Standard Security logins.
The reason for being concerned about the passwords in the registry is that several viruses, such as KLEZ, will upload the user’s section of the registry to a hacker’s computer for later analysis. If a system with Enterprise Manager installed is infected with one of these viruses, you have to assume that the SQL Server account is compromised and change at least the passwords for all the accounts and servers that were registered in Enterprise Manager. In addition, if Enterprise Manager is installed on Windows 9 x, the passwords will be stored in easily accessible unprotected files, which anyone with physical access to the computer can read. An attacker will not have to use a virus to steal the information.
NOTE Enterprise Manager should not be installed on Windows 9x if your servers use Standard Security.
Given the problem of the way Enterprise Manager stores its passwords, it is considered best practice to use only trusted connections. That way, the account password will be stored in the more secure database maintained by Windows NT/2000.
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.
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.
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.
Microsoft SQL Server 6.5 is the second phase of Microsoft’s strategy of moving away from SQL Server’s origins as Sybase SQL Server for OS/2 and toward a database management system integrated into the Windows operating system. Although there is a significant difference between the interfaces of SQL Server 4.21a, which is the first Microsoft version to run on Windows NT, and version 6.5, much of 6.5’s authentication architecture is identical to that used in earlier versions. Sybase originally designed SQL Server to run on multiple operating systems, so the way that authentication works reflects this. SQL Server needed to be able to handle authentication on its own and not depend on the operating system.
When Microsoft ported SQL Server from OS/2 to Windows NT 3.5, SQL Server continued to validate login credentials itself to maintain backward compatibility. A little later, Microsoft introduced a new mode that allowed users to log into SQL Server using their Windows NT domain accounts. Nevertheless, the standard practice continues to be to have SQL Server manage its login accounts, probably because it is easier to use and it maintains backward compatibility.
In this section, you’ll look in detail at how authentication works in SQL Server
6.5 and at the different authentication modes that can be used. The choice of network library has a significant effect on the login process, because each library has different options for authenticating the client’s identity, including different ways of protecting the user’s account and password during the login process. Therefore, you’ll also spend some time in this chapter looking at logins for the three most common network libraries: TCP/IP, Named Pipes, and multiprotocol.
SQL Server 6.5 has three modes for authenticating user logins:
Standard Security, which uses SQL Server to manage the login
Integrated Security, which relies on Windows NT for authentication
Mixed Mode, which just combines the first two modes by allowing users to log in with either a SQL Server login account or a Windows NT account
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.
Standard Security just means that SQL Server manages the accounts and passwords itself. The Master database contains a table named syslogins, which stores account names and passwords for all logins. Table 2-1 presents the schema definition for this table.
Column
Data Type
Description
suid
smallint
Server user ID
status
smallint
Reserved
sccdate
datetime
Reserved
sotcpu
int
Reserved
sotio
int
Reserved
spacelimit
int
Reserved
simelimit
int
Reserved
sesultlimit
int
Reserved
sbname
varchar(30)
Name of user’s default database
name
varchar(30)
Login ID of user
password
varchar(30)
Encrypted password of user (may be NULL)
Language
varchar(30)
User’s default language (NULL for us_english)
Table 2-1.Schema Definition
The process of logging in follows these steps:
SQL Server 6.5 receives the account and password in the login request.
The MSSQLSERVER service issues a query similar to “SELECT * FROM syslogins WHERE name = @account AND password = @password”.
If the query returns a row, the service grants the user’s login request. If not, the service terminates the login process.
If the login request is successful, SQL Server builds in memory an internal structure. This structure holds information about the user’s session, including, among other things, his system user identifier (SUID) from syslogins. The SUID is simply a 16-bit integer assigned during the account creation process. It serves as a primary key for the syslogins table and as a unique identifier for the user. The SUID for the sa account is 1, and each new user gets his SUID by adding 1 to the largest SUID below 16382 currently in the system. For example, on my test system, my first two logins received 10 and 11 as their SUIDs.
Integrated Security
The term “integrated” in the Integrated Security mode comes from the idea that SQL Server logins have been integrated into the Windows NT/2000 authentication scheme. Administrators have a choice of granting server access either to a user’s Windows NT/2000 account or through membership in a Windows NT/2000 group. The following is a complete list of the options, but the rule of thumb is that the account or group may be anything that can be authenticated by the server running SQL Server:
Local accounts on the server running SQL Server
Local groups on the server running SQL Server
NT 4.0 domain accounts in the server’s NT 4.0 domain
NT 4.0 domain global groups in the server’s NT 4.0 domain
NT 4.0 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain
Windows 2000 domain accounts in the server’s Windows 2000 domain
Windows 2000 domain local and global groups in the server’s Windows 2000 domain
Windows 2000 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain
Windows 2000 forest universal groups in the server’s forest, if SQL Server is running on a version of Windows 2000 server
Microsoft’s implementation of Integrated Security in version 6.5 is a little strange, however. What is actually happening is that SQL Server really has two “roles” for Integrated Security logins: user and administrator. It determines which role the user should have by looking at the permissions on the HKLM\Software\ Microsoft\MSSQLServer\MSSQLServer registry key. Read permission on the key indicates the user role, and Full Control permission indicates the administrator role.
NOTE You can look at registry key permissions using the regedt32.exe tool that ships with Windows NT and 2000.
Additionally, if a user is a member of any group that has Full Control permission, that user is automatically elevated to the administrator role. For example, assume Judy Smith’s account JudyS is a member of the Domain Users group and the Administrators local group. If Domain Users has Read permission and Administrators has Full Control permission, then Judy Smith gets administrator privileges in SQL Server.
After determining the role, SQL Server maps the Windows account to a SQL Server account. Administrators all map to the sa account, regardless of the account used to login. For users, the server looks in syslogins for a record that matches the Windows account name using a query similar to "SELECT * FROM syslogins WHERE name = @accountname". If it finds a matching row, it maps the Windows account to that login account. If it doesn’t find a match, it maps the account to the default login, which is the guest account in the standard installation. Once it determines where to map the account, SQL Server builds an in-memory structure to hold session information just as it does for a Standard Security login. In fact, after the login process completes, it will be nearly impossible to tell the difference between a SQL Server Standard login and an Integrated login.
One point does bear mentioning. There is no mechanism for mapping a SQL Server login account to a Windows NT/2000 group. Members of the group can gain access to the server through permissions assigned to the group, but the name of the SQL Server login account they use must match their Windows account name.
If agroup member does not have a login account matching her Windows account, the login account will map to the default account. Remember, this caution only applies to the user role because all administrators map to the sa account.
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.
In this section, you’ll examine the login process in detail by observing the network traffic passing between the client, SQL Server, a name resolution server, and a domain controller.
However, before we look under the hood, let’s stop for a moment to run through the testing methodology used for this section. The tool used to record the network traffic was the full version of Network Monitor that ships with Microsoft’s Systems Management Server (SMS). If you have the SMS 2.0 disk, navigate to the NMEXT\I386 folder and run setup.exe.It can also be found on Windows 2000 Server (a good free sniffer is NGSSniff at http://www.nextgenss.com/products/ ngssniff.htm). You may think it odd to use network traces in a book on SQL Server, but it turns out that one of the common reasons for problems logging into SQL Server stems from the fact that, in many cases, Windows will validate the client’s identity before SQL Server even sees the login request. This process has to occur because the network libraries use a method for running code on a server called remote procedure call,or RPC.How RPC works is not relevant to this discussion, but what is important to understand is that Windows NT and 2000 requires authentication of the user’s identity before the operating system will allow the user to execute code on the server.
Furthermore, only by looking at the network traffic will you know what components have to be available in order for a client to find and then log into SQL Server. If you want your database server to support a web server, a multitiered application, or any other type of application, you have to know what components must be available on each segment of the network.
Finally, if you want to use any of the encryption methods I discuss in this book, you should also verify that they work.
NOTE Looking at the contents of the network packet is the best way to make sure that the data stream is safe. As a general rule, if you cannot read it, neither can anyone else.
Fortunately, Network Monitor 2.0 (usually called NetMon) does most of the work of turning obscure network packet content into human-readable format. There are other packet sniffers that will read the network traffic quite well, but Microsoft has done a good job of making NetMon a complete analyzer for the traffic found on Windows networks. The actual capture files and setup instructions used for this book can be found in the download file for this book at http://www.WinNetSecurity.com. You will also find instructions for loading the capture files into other network analyzers if you do not have NetMon.
The Test Network
Figure 2-1 shows the configuration of the test network used for this section.
Figure 2-1.The test network for SQL Server 6.5
Each of the machines shown here is actually a virtual machine running on VMware’s GSX Server product (http://www.vmware.com) on a private, closed network. Here is a description of each server’s role in the testing environment:
NT4DC is a Windows NT 4.0 Service Pack 6 domain controller for the NTTEST domain.
W2KDNS is a Windows 2000 Service Pack 2 member server running Windows Internet Naming Service (WINS), Domain Name System (DNS), and Dynamic Host Configuration Protocol (DHCP). It is not a member of the domain.
SS65 is a Windows NT 4.0 Service Pack 6 member server running SQL Server 6.5 with Service Pack 5a and the post–Service Pack 5a hotfix. It is a member of the NTTEST domain.
W2KCLIENT is a Windows 2000 Professional Service Pack 2 client computer with the SQL Server 6.5 Service Pack 5a client tools installed. It is a member of the NTTEST domain. (Note that none of the following explanations would change if the client were running Windows NT Workstation, or even Windows 9 x, instead of Windows 2000 Professional.)
MONITOR runs Windows 2000 Professional Service Pack 2 and acts as the monitoring station where NetMon runs. It is not a member of the NTTEST domain, and it has been specially configured to produce very little network traffic.
The main reason to put the WINS, DNS, and DHCP services on a computer other than the domain controller is that this configuration makes it easier to see the whole process. One of the assumptions made when deciding how to configure the test network was that TCP/IP would be the sole network protocol. Part of the login process involves finding the database server, and without WINS or DNS, your clients will be forced to use the IP address. If your network uses some other protocol, you will have to translate the references to WINS and DNS into whatever mechanisms your network uses to translate human-readable computer names into network addresses.
Now that you have had a brief introduction to the test network, you can look at the traffic generated when a user logs in using the TCP/IP network library.
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.
The TCP/IP network library is the simplest case in terms of network traffic. In this section, you’ll examine the listing of the actual list of network packets. It is avery short process because SQL Server handles all the authentication tasks internally. To help you make sense of what the packets contain, I go through the process in detail, step by step, pointing out potential problems along the way.
Starting in frames 5 through 7, you see the three-way handshake that starts aTCP session. This set of three packets makes a good reference point when you are looking at network traffic, because they always signal the beginning of TCP sessions. Because all communications with SQL Server happen within a TCP session, a three-way handshake will mark the beginning of every client-server session.
Frame 8 is the first login packet. Notice that the protocol used is TDS, which stands for Tabular Data Stream. TDS is the protocol used by Open Data Services (ODS), which is a set of functions on Windows NT and 2000 that handle communications with clients. SQL Server does not actually handle network communications itself. It is ODS that handles all sending and receiving between the client and server, which is why you will see all communications between SQL Server 6.5 and the client use TDS as the protocol, whether or not you are using TCP/IP. Figure 2-2 shows how TDS represents a tabular data set as a hierarchical structure.
NOTE SQL Server Books Online has more information on ODS.
Figure 2-2. How TDS represents a tabular data set as a hierarchical structure
TDS looks similar to XML in that it is designed to handle hierarchical data sets. As Figure 2-2 shows, each table is represented as a recordset. Within each recordset is a rowset that contains all rows in the table. Each row in the rowset contains a set of columns that hold the data. To read the data, the client side of the connection must have code that has three nested loops—one for the innermost list of columns, one for the list of rows, and one for the list of recordsets. In most cases, there will be only one recordset, but TDS does allow multiple record-sets in a single response to the client. Both the Open Database Connectivity (ODBC) client library and the ActiveX Data Objects (ADO) client for SQL Server use TDS internally; therefore, you will see similar network traffic if you use those clients instead of ISQL/W.
In the data portion of frame 8, you will see the login name for JoeS, which is a standard login on SQL Server. If you were to open the capture file in NetMon, you would also see the password in clear text (or at least, unencrypted, XORed, and UNICODEed text).
NOTE One of the biggest security problems with the TCP/IP network library is that it does not encrypt users' passwords. SQL Server 7.0 and 2000 have remedies for this problem, but SQL Server 6.5 does not.
The next set of frames shows something odd. Frames 26 through 28 show the three-way handshake again, which indicates that ISQL/W has broken the original connection and opened a new one. Frame 29 shows where ISQL/W logs into SQL Server again, but this time it includes the client computer’s name.
The last several frames show ISQL/W once again querying SQL Server for information about the server. What makes this second login odd is that I clicked the Connect button only once. All the packets shown in the preceding listing come from a single login. Unfortunately, I cannot tell you why there are two connections per login.
If you look at a trace of network traffic when a client uses the TCP/IP library, you will see that there is a large amount of human-readable data. The contents of character and text columns will be easily readable, and even 1- and 2-byte integers will be easy to convert. All commands are sent as text; therefore, you will be able to read the contents of SELECT, INSERT, UPDATE, and DELETE statements. That visibility is one of the failings of the TCP/IP network library; not only does it send user account names and passwords in clear text, but also it sends and receives data in moderately easily read format. When you are evaluating network libraries from a security perspective, you must consider this library to be the least secure option available when using SQL Server 6.5. That assessment will change for SQL Server 7.0 and 2000.
NOTE One potential solution would be to move down the OSI model and use IPSec to encrypt the data stream between the client and server. The main impediments to using IPSec will be the operating system and domain architectures. See the previously mentioned TechNet article for more details on implementing IPSec.
Two factors may override the problem with security, however. First, the TCP/IP network library is the fastest of all the libraries in terms of overall throughput. If you need raw speed, it is the best choice. Second, the TCP/IP network library requires the least support services of all the network libraries. If your users use a DNS name to connect to the server, the server needs a host record (aka an A record) in your DNS server. If you intend to use the IP address instead, you will need no other services. If you need the fastest configuration, as for a web site that uses a database to drive its content, the TCP/IP network library with the client using the IP address of the server will be the best choice. Just recognize that there is a sacrifice of security for that speed.
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.
The Named Pipes network library gets its name from the fact that it uses a fairly old interprocess communication (IPC) mechanism known as named pipes. IPC is simply the exchange of data between two programs. In earlier operating systems, it occurred between two programs running on the same machine, but in Windows NT and 2000, the term usually applies to the communication between applications running on two different machines. Named pipes simulate files, and to the applications, the communication process looks exactly like reading and writing to a file on the disk. For SQL Server 6.5, the client sends a query by writing it to the named pipe. The server reads the query from the file and writes the results back to the named pipe. The client receives the results by reading from the named pipe.
Figure 2-3 illustrates how named pipes work.
Figure 2-3. Using named pipes
Internally, the named pipe works like two one-way communications channels, but the key difference between it and other mechanisms is that the operating system can choose to buffer the data either to a temporary file on the disk or to a memory buffer until the receiver has time to read from the pipe. This option works well for SQL Server, because it allows the client and server to send data at their own speed, and it allows SQL Server to handle multiple users without losing any data, even if all clients send data at exactly the same instance in time.
Once again, to see what’s happening in the login process, you’ll look at the contents of the packets:
Windows NT and 2000 implement named pipes as RPCs using the NetBIOS protocol. In frame 15, you will see where the client issues a NetBIOS request (as indicated by the NBT protocol) to start a session on the server on which SQL Server runs. The server accepts the request, and then the two computers negotiate which dialect of NTLM protocols they will use to communicate. They settle on version 5 because that is the highest version SS65 supports. Remember, W2KCLIENT is running Windows 2000 Professional and SS65 is running Windows NT 4.0 Server. If both computers were running Windows 2000 or XP, you would see them agree to use NTLM version 2, which is the standard on Windows 2000.
The reason you should care which version they use is because the NTLM authentication protocol has a serious flaw that can let someone sniffing the network determine a user’s password. The full explanation of the problem is outside the scope of this book, but the main point is that the authentication process is no longer secure against a determined attacker. NTLM version 2, which was introduced in Windows 2000, fixes the security problems found in NTLM and the older LAN Manager used on older versions of Windows.
NOTE Windows 2000 can be configured to require NTLM version 2 and to refuse all requests that attempt to use earlier versions. For this reason alone, you should give serious consideration to running SQL Server on Windows 2000.
Going back to the network trace, you will see frame 19 where W2KCLIENT requests an SMB session on SS65 using FrankB’s account. SMB stands for Server Message Block, and it is the NetBIOS protocol used to handle IPC. If you were to look inside the packet, you would see that the pipe opened in frame 19 is \\IPC$. IPC$ is a hidden pipe used for authentication of IPC connection attempts. Trying to open it forces Windows NT/2000 to start authenticating the Windows account that is making the request, which in this case is NTTEST\FrankB.
The next frame, 22, begins the interesting part of the process. Between frames 19 and 22, SS65 looks for and finds the domain controller, NT4DC. In frame 22, SS65 opens the \NETLOGON named pipe on the domain controller. This is the way a server can check the authentication credentials for a domain account. The next few packets show SS65 asking the domain controller to validate FrankB’s domain account and password.
27 NT4DC SS65 R_LOGON RPC ... resp: logon:NetrLogonSamLogon(..) 28 SS65 W2KCLIENT SMB R session setup & X, and R tree connect 29 W2KCLIENT SS65 SMB C NT create & X, File = \sql\query 30 SS65 W2KCLIENT SMB R NT create & X, FID = 0x802
In frame 27, SS65 gets a positive response from NT4DC, and then in frame 28 it tells W2KCLIENT that it accepts the SMB session request. In frame 29, the client opens the \sql\query named pipe. After the server accepts the request in frame 30, the mechanism is the same as it was for the TCP/IP network library, including the second login, which seems to be an idiosyncrasy of ISQL/W.
31 W2KCLIENT SS65 TDS Login - , joes, 000002bc, MS ISQL/w 32 SS65 W2KCLIENT SMB R write & X, Wrote 0x200 33 W2KCLIENT SS65 TDS Login - (continued) 34 SS65 W2KCLIENT SMB R write & X, Wrote 0x4c 35 W2KCLIENT SS65 SMB C read & X, FID = 0x802 36 SS65 W2KCLIENT TDS Response to frame 33 - Environment Chg 37 W2KCLIENT SS65 TDS SQL - exec sp_server_info 18 ... 39 SS65 W2KCLIENT TDS Response to frame 37 - Done in Procedure 40 W2KCLIENT SS65 TDS SQL - ... select suser_name() 41 SS65 W2KCLIENT TDS Response to frame 40 - Done 42 W2KCLIENT SS65 TDS SQL - select @@microsoftversion 43 SS65 W2KCLIENT TDS Response to frame 42 44 W2KCLIENT SS65 TDS SQL select name from master.dbo.spt_values 45 SS65 W2KCLIENT TDS Response to frame 44 46 W2KCLIENT SS65 TDS SQL - select suser_name() 47 SS65 W2KCLIENT TDS Response to frame 46 48 W2KCLIENT SS65 SMB C close file, FID = 0x802 49 SS65 W2KCLIENT SMB R close file 50 W2KCLIENT SS65 SMB C NT create & X, File = \sql\query 51 SS65 W2KCLIENT SMB R NT create & X, FID = 0x803 52 W2KCLIENT SS65 TDS Login - W2KCLIENT, joes, 000002bc, MS ISQL 53 SS65 W2KCLIENT SMB R write & X, Wrote 0x200 54 W2KCLIENT SS65 TDS Login - (continued) 55 SS65 W2KCLIENT SMB R write & X, Wrote 0x4c
Now, let’s pause to consider what has just happened. First of all, notice that the client uses the JoeS standard login account for SQL Server, but Windows uses FrankB’s Windows account credentials to decide if the client is allowed to connect to SQL Server. What is happening is that FrankB is logged into the client computer, and he is logging into SQL Server using the JoeS account. Because Windows NT/2000 requires authentication of the user’s identity before it allows someone to open a named pipe, the authentication process happens at the operating system level before SQL Server even sees the request. There is simply no way for SQL Server to intercept the request or to tell the operating system not to authenticate the user.
What this means is that even for Standard logins, the user’s Windows account and password must first be validated by the operating system. In this case, I used a domain account, but it could have been an account in the local server’s security database. What you would see if you opened the trace in NetMon is that the client computer sent the domain name along with the account name. Windows NT will look in its local security database for an account matching the name sent by the client before it contacts the domain controller; therefore, if FrankB had an account on SS65 with the same password, Windows NT would use that account. If the account does not exist, or if the password is different, the operating system will contact a domain controller for verification. Windows 2000 uses a similar algorithm, but I have found instances in which it always contacts the domain controller.
In addition to authenticating the user’s Windows account, Windows will also check the account against its list of user rights on the local server. The user right that may cause problems is “Access this computer from the network”. If the user’s Windows account does not have this right, Windows refuses the connection. By default, the Everyone local group has the right; however, you should consider replacing that group with the Authenticated Users group because it ensures only authenticated accounts can access SQL Server. You can also use this right to limit access to the server to a specific set of users.
The end result is that if you use the Named Pipes network library, users will have to pass through two checkpoints: one for the operating system and one for SQL Server. If you use Standard Security or Mixed Mode, the user could even authenticate with two different accounts; thus, it is better to use Integrated Security where possible. Just remember that if the user’s Windows account does not have permission to access the database server across the network, it does not matter whether or not she has a valid SQL Server login account.
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.
As you can probably guess, Integrated Security is a byproduct of the authentication the operating system does before SQL Server sees the login. Any authenticated connection to Windows NT/2000 causes Windows to build a data structure known as an access token. The access token contains not only the SID for the user’s Windows account, but also the SIDs of all the groups, both local and domain, of which the user is a member. It also contains other information, but SQL Server only uses the SIDs to determine if a user can log in.
As I mentioned at the beginning of the section, SQL Server 6.5 uses the rather unusual method of basing login access on the security permissions a user has on a key in the registry. SQL Server compares the list of SIDs in the access token to the list of SIDs on the registry key. If an SID has Read permission on the key, it considers the login to be a user-level login. If an SID has Full Control permission on the key, it considers the login be a system administrator (sa) login. If a user can somehow receive both Read and Full Control permissions, SQL Server grants him the higher level sa login. You can use the system stored procedure xp_logininfo to find out how the user achieved an administrator login.
For user-level logins, SQL Server performs a second check on syslogins to see if it contains a row with the user’s Windows account name. Remember that SQL Server does not allow the \ domain separator character in its login names, so you will have to consult the list of replacement characters to determine what the login name will be. (SQL Server Books Online has the complete list of characters.) The Security Manager tool will also show you the name it created if you used it to grant login privileges to a Windows account or group. If SQL Server does not find a login account in syslogins, it logs in the user with the default account, which is guest.
Disabling the Guest Account
If guest does not have login privileges, the user’s login request is refused. You can use that behavior to your advantage if you want to grant login permissions to individual members of a group without granting access to all members. Granting Read permission on the registry key grants all members of the group the privileges of at least the guest login account. As you will see in the next two chapters, guest logins can gain guest privileges in databases that have the guest user account enabled. Granting access to what is essentially an anonymous user is probably not appropriate for most situations; therefore, you will probably want to disable the guest login account. Doing so allows you to grant user-level login rights to individual members of a group and refuse access to all the other members.
Eliminating Trusted Connections with Standard Security
Configuring SQL Server to use just Standard Security does not eliminate Integrated Security logins if the client requests a trusted connection. Even in Standard Security mode, SQL Server checks the permissions on the registry key if the user uses a trusted connection to log in. To eliminate trusted connections entirely, you must remove all permissions from the registry and then assign permissions to two empty, local groups. Here are the steps you need to follow:
Create two local groups named SQLUsers and SQLAdmins that contain no users.
Use SQL Security Manager to grant the System Administrator privilege to the SQLAdmins group. Grant the User privilege to the SQLUsers group.
Use SQL Security Manager to revoke all privileges from all other groups.
Because there are no users in the groups, no access token will have the groups’ SIDs, and no user will be able to log in using a Windows NT/2000 account. Just be aware that this setup can cause problems in some configurations that require trusted connections, such as replication.
Named Pipes does have one nice little feature that can be useful if your network is down. If you use the name "(local)" instead of the network name to connect to SQL Server, ISQL/W does not send any packets to the network. All communications stay on the local server. This is why it is always a good idea to leave Named Pipes enabled. If something happens to the network, you can still log into SQL Server using the local console.
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.
In comparison to the TCP/IP network library, the Named Pipes library is a little more secure because of the way the authentication process works. Although the library still sends the password for Standard logins in clear text, at least the Windows account’s password stays secure, especially if you follow my advice and use Windows 2000 instead of Windows NT for the database server. There is still a potential security problem caused by the fact that the data stream itself is unencrypted. If security is your most important criterion for choosing between network libraries, Named Pipes should rank higher than TCP/IP and lower than the Multiprotocol library, which I discuss next.
Logins Using the Multiprotocol Library
The Multiprotocol library gets its name not because it works over multiple network protocols—which it does—but because it can work with multiple IPC protocols at the same time. Clients can choose to connect to the server using Named Pipes, directly via TCP/IP, or via an SPX session using the NetWare network library. Because the sad truth is that NetWare clients are very rare, I focus on Named Pipes and TCP/IP.
The Multiprotocol library was introduced in SQL Server 6.0 to take advantage of a more efficient IPC mechanism known as a remote procedure call (RPC). Essentially, RPCs allow client applications to run subprograms on the server. The benefit is that the client can utilize existing functionality on the server without having to duplicate the code itself. The ability to accept RPCs is a significant part of what makes Windows NT and 2000 good platforms for running applications. They come with a large quantity of subprograms, which client programs can use so that they do not have to duplicate the functionality usually provided by the operating system. You can think of RPCs as a kind of Swiss army knife for programmers.
Because the explanation of RPCs can become esoteric rapidly, I do not step through a network trace for the Multiprotocol library. The overall login process is very similar to what happens with the Named Pipes and TCP/IP network libraries. The main difference is that instead of using the NetBIOS SMB protocol, as is the case for Named Pipes, or sending packets directly to SQL Server, as is the case for TCP/IP, the Multiprotocol client library uses RPCs to send the data. Clients can still choose to use Named Pipes but, instead of opening a file using NetBIOS, they use the file-management functions built into Windows NT and 2000’s RPC library. When you look at the packets, you will see the client opens a pipe named \000000BC.01 instead of \sql\query, but it still sends commands to and receives data from SQL Server by writing to and reading from that file.
What happens at the network level is less significant for the Multiprotocol library because it has little effect on the login process. The one point of interest in a network trace of the login traffic is that no matter which of the three available IPC methods you choose (Named Pipes, TCP/IP, or SPX), the Multiprotocol library will always authenticate the user’s Windows account credentials, just as we saw in the network trace of the Named Pipes library. Once again, it does not matter if the client uses a Standard Security login or an Integrated Security login. The operating system always authenticates the Windows account before SQL Server sees the login request.
The most important feature from a security point of view is that the Multiprotocol network library has the option of encrypting the data stream. Both the SQL Server login request and the data passing between the client and server will be encrypted. The process of authenticating the user at the operating system level stays the same, though. It is only after the client establishes the connection with Windows NT/2000 that the Multiprotocol library starts encrypting the information it sends to SQL Server.
For some, it may help to refer to the Open Systems Interconnection (OSI) network model shown in Figure 2-4. Because the Multiprotocol library is a mechanism for encapsulating tabular data traveling between the client and server, it sits at the bottom of the application layer. (Some purists may want to place it in the presentation layer.) Everything below it is left unencrypted. That means that the Multiprotocol network library’s encryption does not protect against problems such as the weaknesses in the NTLM authentication protocol, which I discussed earlier. Any attacks that depend on weaknesses at the bottom six layers of the OSI model will still be problems even if you enable encryption in the Multiprotocol library.
Figure 2-4.The OSI network model
What encryption at this level does protect is the user’s password and the contents of resultsets returned from the server. You can either enable encryption at the server so that all client connections will be encrypted, or you can enable encryption on a client-by-client basis. The choice will depend on your assessment of where the risks are greatest. If your server contains mostly sensitive data such as accounting information or customer credit card numbers, encrypting all sessions is a good idea. It is also a good idea if you use Standard Security, because it is the easiest way to protect users’ passwords. If all you want to do is protect logins, you should consider using Integrated Security instead of encryption because there is a performance penalty with encryption. If you have only a few users who access sensitive data, or if users authenticate through an application and the application uses its own account to log in, you can choose encryption on a client basis. The only advice I will offer at this point is that configuring the server to require encrypted connections is much easier than configuring each client’s computer.
Now it is time to see how SQL Server 7.0 and 2000 improve on the authentication process and fix some of the flaws you have seen in the way SQL Server 6.5 handles authentication.
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.
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.
Figure 2-5 shows the configuration of the network I set up for this section.
Figure 2-5. The test network for SQL Server 7.0 and 2000
As in the test network for SQL Server 6.5, each of the machines shown is a virtual machine running on VMware’s GSX Server product (http://www.vmware.com) on a private, closed network. The primary difference between the configuration for SQL Server 6.5 and the configuration in Figure 2-5 is the use of SQL Server 7.0 and SQL Server 2000 on the same network. Although both start out the test running on Windows NT, it turned out that Windows 2000 did not change the authentication process at all. Here is a description of each server’s role in the testing environment:
NT4DC is a Windows NT 4.0 Service Pack 6 domain controller for the NTTEST domain.
W2KDNS is a Windows 2000 Service Pack 2 member server running WINS, DNS, and DHCP. It is not a member of the domain.
SS7_NT_SRV is a Windows NT 4.0 Service Pack 6 member server running SQL Server 7.0 with Service Pack 4. It is a member of the NTTEST domain.
SS2K_NT_SRV is a Windows NT 4.0 Service Pack 6 member server running SQL Server 2000 with Service Pack 2. It is a member of the NTTEST domain.
W2KCLIENT is a Windows 2000 Professional Service Pack 2 client computer with the SQL Server 7.0 Service Pack 4 client tools installed. It is a member of the NTTEST domain. (Note that none of the following explanations would change if the client were running Windows NT Workstation, or even Windows 9 x, instead of Windows 2000 Professional.)
MONITOR runs Windows 2000 Professional Service Pack 2 and acts as the monitoring station where NetMon runs. It is not a member of the NTTEST domain, and it has been specially configured to produce very little network traffic.
The operating system versions and service packs are the same as the test network in the previous section.
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.
SQL Server 2000 introduces a new special network library that offers an alternative to the Multiprotocol network library for encrypting both the authentication network packets and the data packets as they travel between the client and server. As Figure 2-6 shows, the Super Socket network library is not a typical network library, in that it does not directly communicate with clients. Instead, it is a helper library that sits between the database engine and the other network libraries and encrypts the data using Secure Sockets Layer (SSL) before passing it to them. This architecture permits encrypting authentication information for clients who do not use the Multiprotocol network library’s encryption. To understand how SSL compares to encryption in the Multiprotocol network library, let’s look briefly at how SSL works.
Figure 2-6. The Super Socket network library architecture
SSL is based on the premise that the server has a pair of keys, one given to the public and one kept private, which have a special relationship to each other: data encrypted with one key can only be unencrypted by using the other key. The keys themselves are very large prime numbers, and you cannot derive one key from the other. The size of the number depends on the number of bits used to store it, with 128 bits being the current RC4 standard for SSL symmetric key encryption. That means the keys can be numbers as large as 2 to the 128th power, which is a very large number indeed. With the current technology, you’d have a better chance of winning the lottery, getting hit by lightning, and sinking a hole in one, all on the same day, than of deriving the second key in a pair knowing the first key.
Public key encryption itself is based on the fact that the private key is always kept completely secret from everyone. Only the key’s owner knows it. If you encrypt data using your private key, only your public key can decrypt it. Anyone receiving the encrypted data will know you sent it, because only you have the private key that is paired with the public key. Conversely, if someone uses your public key to encrypt data, that person can be assured that only you can decrypt the data, because only you have the private key.
Prior to public key encryption, two people who wanted to share encrypted data would need to agree on an encryption key before they encrypted the data. Known as symmetric encryption, a single key both encrypts and decrypts the data. If only two people know the key, then they both know that data came from one of them and that only the two of them will be able to read the data. The benefit of symmetric keys is that they are much faster than public key encryption, and the size of the key can be smaller. The problem is that it is quite impossible to set up shared, private keys with everyone in the world.
To implement this combination of public key encryption and symmetric key encryption, SSL has to exchange several messages with the client. In Figure 2-7, the first step is the client sends a ClientHello message, which includes a large random number, to the server. In response, the server sends a ServerHello message, which also includes a large random number, followed by the server’s public key and a ServerHelloDone message. At this point, the client and the server have agreed on what kind of encryption they will use and the size of the keys they will use. Now the client will create a 384-bit symmetric key known as the “master secret,” encrypt it with the server’s public key, and send it back to the server using a ClientKeyExchange message. When the client and the server both have the master secret, they use a special set of one-way hashing functions to combine the master key and the random numbers sent in the hello messages into a single symmetric key that will be used to encrypt the data stream.
Figure 2-7.Message exchange across SSL during encryption
The client then sends a ChangeCipherSpec message telling the server it wants to start encrypting data and a Finished message that contains various pieces of information encrypted using the new key. The server responds with its own ChangeCipherSpec and Finished messages to inform the client that the secure channel has been established. All subsequent packets sent in this transaction will be encrypted, but only at the session level.
This is a very abbreviated explanation of SSL, but the main point to understand in this process is that the client uses the fact that no one but the server can decrypt a session key that has been encrypted with the server’s public key. That allows the client and server to share a symmetric key without having some sort of prearrangement. It also allows a client to choose different keys for different sessions to keep would-be eavesdroppers from being able to use one session key to listen in on another session.
It is also important to understand what SSL does not protect. The first point to recognize about SSL is that the only identity you can authenticate is the server’s. The digital certificate is digitally signed by a certificate authority, which is supposed to verify the identity of the person requesting the certificate. VeriSign is the primary certificate authority in the world today, and it asks people to provide a lot of paperwork to prove that they own the domain name that will be in the certifi-cate—that they are in fact who they say they are. The end result is that there can be ahigh level of trust over the identity of a server with a VeriSign digital certificate.
The same cannot be said about the client, however. There is no authentication of the client’s identity other than the account and password. A server that uses SSL does not have any better way of determining the client’s identity than one that does not use SSL. SSL is simply not designed to offer that kind of authentication. Its sole purpose for SQL Server is to encrypt the data traveling between the server and the client, including the account and password for SQL Server authenticated logins.
Another common misconception is that SSL will improve the security of authentication process for Windows authenticated logins. This is not the case. Referring back to the OSI model earlier in the chapter, SSL is a session layer protocol, and it cannot encrypt any part of the network packet added by the lower layers or packets created by other services. That means that there is no protection for the source and destination addresses, TCP protocol headers, or other information in the packet, and there is no encryption of data from other services, such as the network login service in Windows NT and 2000. As you will see in the next section, Windows authentication occurs before SQL Server sees the client’s login request; therefore, there is nowhere to send the ClientHello message. SSL must wait until after Windows has authenticated the user’s account and password.
The other limitation to SSL is that it does not prevent tampering with the network packet. It is possible for an attacker to set up a “man-in-the-middle” attack by intercepting packets from the client and then rerouting them to the server. In this scenario, the attacker’s computer sets up a SSL session with the client using his digital certificate and then sets up a second session with SQL Server as proxy between the client and server. As packets pass through the attacker’s computer, he can store them and examine them for passwords and other information.
The man-in-the-middle attack works because the client does not check the authenticity of the digital certificate and SQL Server does not validate the identity of the clients that connect to it. You will see in Chapter 5 how IPSec and Kerberos provide encryption equal to SSL and prevent man-in-the-middle attacks, among others.
For now, recognize that SSL is only part of the solution to protect your data from unauthorized access.
NOTE Because there are several different paths you can take in setting up SSL, I do not cover the process of getting a certificate, installing it on the server, and turning on encryption in this book. Consult SQL Server Books Online for detailed instructions on how to implement SSL in SQL Server 2000.
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.
Let’s now look at the traffic generated when a user logs in using the TCP/IP network library and a SQL Server authenticated account. Just as it is in SQL Server 6.5, the TCP/IP network library is the easiest network trace to follow. The following listing shows an abbreviated trace of the network packets sent when Query Analyzer tries to log into the server using a SQL Server authenticated account.
NOTE The full trace can be downloaded along with the rest of the material accompanying the book from http://www.WinNetSecurity.com.
Frames 2 through 4 are the TCP three-way handshake, which indicates W2kClient is initiating a TCP session with the database server. The description in frame 5 is not really descriptive, but inside that packet is the SQL Server login account name and password. Apparently, SQL Server accepts the account and password because frame 6 does not have an error message.
Frames 7 through 24 contain queries from Query Analyzer that ask SQL Server for information about the server’s environment. Typically, this is the time that Query Analyzer will build a list of databases and turn on or off various session-specific settings. Like ISQL/W in SQL Server 6.5, Query Analyzer closes the first session when it has everything it needs and starts a new session. The only difference in the login traffic is that frame 28 includes the name of the client’s computer; otherwise, the process is the same.
Once you have a TCP session between the client and server, SQL Server authenticated logins go through a simple process of querying the syslogins system view in the Master database. If SQL Server does not find the account and password in the sysxlogins table that syslogins references, the user will get an error message. If it does find a row in the table, SQL Server then checks to see if the login account has been explicitly denied access to the server by checking the denylogin column (see the definition of the syslogins view in the section “Managing Login Accounts” later in this chapter). If the value is 1, the user receives an error message stating her login is denied.
This process is identical to the one used for SQL Server 6.5, including the fact that Windows does not check the user’s Windows account information before permitting SQL Server to receive the login request. If you have clients that use an operating system other than Windows, they can use the TCP/IP network library to connect to SQL Server with a SQL Server authenticated login. This is the only configuration that bypasses the Windows authentication process, because the client communicates directly with SQL Server via TCP port 1433 and does not use any of Windows RPC functions.
If you were to look at the actual packet contents of frames 5 and 28, you would see that the password is being sent in clear text. This is the main drawback of using the TCP/IP network library.
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.
In contrast to SQL Server 6.5, SQL Server 7.0 and 2000’s version of the TCP/IP network library supports trusted connections using Windows accounts or groups to log in. These connections are called trusted connections because SQL Server trusts Windows to handle the authentication process entirely.
The overall process is similar to what you will see in the network trace of the Named Pipes network library in the next section. The client connects to SQL Server on TCP port 1433 and asks for a trusted connection. At that point, Windows follows its normal process of authenticating the account and password, creates an access token if the account is valid, and then routes the login request to SQL Server.
One benefit of Windows authenticated logins via TCP/IP over SQL Server authenticated logins is that the password does not travel the network in clear text. As you saw earlier, this is a problem with the way SQL Server 6.5’s TCP/IP network library handles authentication. Other than using something such as IPSec to encrypt the network packet at the IP layer of the network architecture, there really is no way to prevent an attacker from discovering the passwords for SQL Server authenticated logins in SQL Server 6.5. If you are using SQL Server 7.0, Windows authenticated logins are a way to protect users’ passwords with the TCP/IP network library.
If you are using SQL Server 2000, you have the option of using the Super Socket network library and SSL to encrypt the account and password for a SQL Server authenticated login. The question, then, is what are the tradeoffs between using SSL and SQL Server authenticated logins and using Windows authenticated logins to protect users’ passwords?
There are two significant differences between Windows authenticated logins and SSL. First, SSL validates the identity of the server but not the client, whereas Windows authentication validates the client and not the server. Technically, the server’s identity is validated to a small extent for domain accounts by the fact that the server knows enough about the domain to permit its accounts to log in. This level of validation is not really worth much, though, because it does not provide the client with definite, verifiable authentication of the server’s identity.
Second, the Windows authentication protocol protects the password for the user’s Windows account but does nothing to protect the data passing between the client and server. SSL, of course, encrypts the entire data stream with SQL Server authenticated logins. The drawback is that SSL decreases the server’s performance in situations in which its clients request large amounts of data.
The conclusion you should draw from these differences is that in situations in which knowing the server’s identity is just as important as knowing the client’s, use SSL. If you need to protect the user’s password but not the data stream, or if SSL will hurt performance too much, use Windows authenticated logins. If you need definite, verifiable validation of both the client and server identities, you can use Windows logins in concert with SSL. Finally, if you need both to secure the authentication process and to encrypt the data as it travels the network, you can use SSL with the Named Pipes, Multiprotocol, and TCP/IP network libraries.
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.
The login process for SQL Server 7.0 and 2000’s version of named pipes is the same as it was for SQL Server 6.5, primarily because the RPCs that implement the named pipes IPC in Windows 2000 are backward compatible with Windows NT. As a result, the following network trace will be very similar to the trace for SQL Server 6.5:
In frame 15, you can see where the client issues a NetBIOS request (as indicated by the NBT protocol) to start a session on the server on which SQL Server runs. The server accepts the request, and then the two computers negotiate which NTLM protocol dialect they will use to communicate. They settle on dialect 5, because that is the highest version Windows NT supports. Remember, W2KCLIENT is running Windows 2000 Professional and SS7_NT_SRV is running Windows NT 4.0 Server. If both computers were running Windows 2000 or XP, you would see them agree to use NTLM dialect 6, which corresponds to NTLM version 2, the standard on Windows 2000.
19 W2KCLIENT SS7_NT_SRV SMB C session setup & X, Username = FrankB ... 22 SS7_NT_SRV NT4DC SMB C NT create & X, File = \NETLOGON 23 NT4DC SS7_NT_SRV SMB R NT create & X, FID = 0x800f 24 SS7_NT_SRV NT4DC MSRPC c/o RPC Bind:UUID 12345678-1234-ABCD-EF0 25 NT4DC SS7_NT_SRV MSRPC c/o RPC Bind Ack: call 0x2 assoc grp 26 SS7_NT_SRV NT4DC R_LOGON RPC ... req: logon:NetrLogonSamLogon(..) 27 NT4DC SS7_NT_SRV R_LOGON RPC ... resp: logon:NetrLogonSamLogon(..) 28 SS7_NT_SRV W2KCLIENT SMB R session setup & X, and R tree connect 29 W2KCLIENT SS7_NT_SRV SMB C NT create & X, File = \sql\query 30 SS7_NT_SRV W2KCLIENT SMB R NT create & X, FID = 0x802 31 W2KCLIENT SS7_NT_SRV TDS Login - , joes, 000002bc, MS ISQL/w 32 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x200 33 W2KCLIENT SS7_NT_SRV TDS Login - (continued) 34 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x4c 35 W2KCLIENT SS7_NT_SRV SMB C read & X, FID = 0x802 36 SS7_NT_SRV W2KCLIENT TDS Response to frame 33 - Environment Chg 37 W2KCLIENT SS7_NT_SRV TDS SQL - exec sp_server_info 18 ... 39 SS7_NT_SRV W2KCLIENT TDS Response to frame 37 - Done in Procedure 40 W2KCLIENT SS7_NT_SRV TDS SQL - ... select suser_name() 41 SS7_NT_SRV W2KCLIENT TDS Response to frame 40 - Done 42 W2KCLIENT SS7_NT_SRV TDS SQL - select @@microsoftversion 43 SS7_NT_SRV W2KCLIENT TDS Response to frame 42 44 W2KCLIENT SS7_NT_SRV TDS SQL select name from master.dbo.spt_values 45 SS7_NT_SRV W2KCLIENT TDS Response to frame 44 46 W2KCLIENT SS7_NT_SRV TDS SQL - select suser_name() 47 SS7_NT_SRV W2KCLIENT TDS Response to frame 46 48 W2KCLIENT SS7_NT_SRV SMB C close file, FID = 0x802 49 SS7_NT_SRV W2KCLIENT SMB R close file 50 W2KCLIENT SS7_NT_SRV SMB C NT create & X, File = \sql\query 51 SS7_NT_SRV W2KCLIENT SMB R NT create & X, FID = 0x803 52 W2KCLIENT SS7_NT_SRV TDS Login - W2KCLIENT, joes, 000002bc, MS ISQL 53 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x200 54 W2KCLIENT SS7_NT_SRV TDS Login - (continued) 55 SS7_NT_SRV W2KCLIENT SMB R write & X, Wrote 0x4c
Going back to the network trace, you will see in frame 19 where W2KCLIENT requests a SMB session on SS7_NT_SRV using FrankB’s account. In frame 22, SS7_NT_SRV opens the \NETLOGON named pipe on the domain controller. This is the way a server can check the authentication credentials for a domain account. The next few packets show SS7_NT_SRV asking the domain controller to validate FrankB’s domain account and password. In frame 27, SS7_NT_SRV gets a positive response from NT4DC, and then in frame 28 it tells W2KCLIENT that it accepts the SMB session request. In frame 29, the client opens the \sql\query named pipe. After the server accepts the request in frame 30, the process is the same as it was for the TCP/IP network library, including the second login, which seems to be an idiosyncrasy of ISQL/W.
Just as in the SQL Server 6.5 authentication trace, notice that the client uses the JoeS standard login account for SQL Server, but Windows uses FrankB’s Windows account credentials to decide if the client is allowed to connect to SQL Server. What is happening is that FrankB is logged into the client computer, and he is logging into SQL Server using the JoeS account. Because Windows NT/2000 requires authentication of the user’s identity before it allows someone to open a named pipe, the authentication process happens at the operating system level before SQL Server even sees the request. This is one situation in which SQL Server 6.5, 7.0, and 2000 all exhibit the same behavior because Windows NT and 2000 must authenticate the user’s identity before he can use operating system functions to communicate with SQL Server.
In addition to authenticating the user’s Windows account, Windows will also check the account against its list of user rights on the local server. The one that may cause problems is the “Access this computer from the network” user right. If the user’s Windows account does not have this right, Windows refuses the connection. By default, the Everyone local group has the right; however, you should consider replacing that group with the Authenticated Users group because it ensures only authenticated accounts can access SQL Server. You can also use this right to limit access to the server to a specific set of users. Just remember that if the user’s Windows account does not have permission to access Windows across the network, it does not matter whether or not she has a valid login account in SQL Server.
As you can probably guess, Windows authenticated logins are a byproduct of the authentication the operating system does before SQL Server sees the login. Any authenticated connection to Windows NT/2000 causes Windows to build a data structure known as an access token. The access token contains not only the SID for the user’s Windows account but also the SIDs of all the groups, both local and domain, of which the user is a member. It also contains other information, but SQL Server only uses the SID to determine if a user can log in. You will look at how SQL Server 7.0 and 2000 use SIDs in more detail a little later in this chapter.
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.
The Multiprotocol network libraries in SQL Server 6.5, 7.0, and 2000 all follow the same process to authenticate users. Because the network library is using RPCs, Windows will always authenticate the client’s Windows account and password before it permits SQL Server to see the login request, just as you saw in the trace for the Named Pipes network library. The main point of discussion for SQL Server 2000, therefore, is how the Multiprotocol network library’s encryption compares to SSL.
NOTE Knowledge Base article Q271242 states that Microsoft made a change in the Multiprotocol client driver that comes with Microsoft Data Access Components (MDAC) version 2.1. If the client application chooses a TCP/IP connection within the Multiprotocol network library, the MDAC 2.1 and later versions include a version dbmsrpcn.dll (which is the .dll that implements the Multiprotocol library) that does not force a check of the user's Windows account for SQL Server authenticated logins. In my testing, the versions of the library that come with SQL Server 7.0 Service Pack 4 and SQL Server 2000 Service Pack 2 both still check the user's Windows account when connecting with Query Analyzer. When you use MDAC in a client application, be sure to check the behavior of the Multiprotocol network library.
The most important feature from a security point of view is that the Multiprotocol network library has the option of encrypting the data stream. Both the SQL Server login request and the data passing between the client and server will be encrypted. The process of authenticating the user at the operating system level stays the same, though. It is only after the client establishes the connection with Windows NT/2000 that the Multiprotocol library starts encrypting the information it sends to SQL Server.
In comparison, the encryption provided by SSL and the encryption offered by the Multiprotocol network library are very similar. SSL is part of the session layer or the presentation layer, depending on whom you ask, and it does not offer protection for any of the information in the layers below the session layer. In terms of what they encrypt and the overall security they offer, SSL and Multiprotocol encryption are similar enough to be interchangeable, at least where SQL Server 2000 is concerned. Servers running SQL Server 7.0 will not have the option of using SSL, so the Multiprotocol network library is the only choice if you want to secure your data on the network.
Given that SSL does not offer a clearly superior mechanism for securing data, the fact that SQL Server 2000 offers it is probably not sufficient reason by itself to upgrade from SQL Server 7.0. If you need to decide whether to upgrade to SQL Server 2000 in order to use SSL, there are two factors to consider:
First, the Multiprotocol library requires that a client have a Windows account. If you have users who do not have a Windows account or do not have an account that can be authenticated by your domain controllers, the Super Socket network library in conjunction with the TCP/IP network library will be the only choice for encrypting the password and data stream offered by SQL Server.
Second, the protocols used by the Multiprotocol library to implement encryption are proprietary to Microsoft, and the library is only available for Windows. SSL, on the other hand, is a protocol in the public domain, which means it is widely used by vendors other than Microsoft. If you need secure data transmission between SQL Server and something other than a Windows client, SSL would be the only option.
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.
Having covered the influence the network libraries have on the login process, it is time to look at login account management within SQL Server itself. Table 2-2 shows the definition of the syslogins view, which is the starting point for any discussion of login accounts.
Column Name
Data Type
Description
suid
smallint
Server user ID
sid
varbinary(85)
Security ID
status
smallint
For internal use only
createdate
datetime
Date the login was added
updatedate
datetime
Date the login was updated
accdate
datetime
For internal use only
totcpu
int
For internal use only
totio
int
For internal use only
spacelimit
int
For internal use only
timelimit
int
For internal use only
resultlimit
int
For internal use only
name
varchar(30)
Login ID of the user
dbname
nvarchar(128)
Name of the user’s default database when connection is established
password
nvarchar(128)
Encrypted password of the user (may be NULL)
language
nvarchar(128)
User’s default language
denylogin
int
1, if login is a WindowsNT user or group and has been denied access
has access
int
1, if login has been granted access to the server
isntname
int
1 if login is a Windows NT user or group; 0 if the login is a SQL Server login
isntgroup
int
1, if login is a Windows NT group
isntuser
int
1, if login is a Windows NT user
sysadmin
int
1, if login is a member of the sysadmin server role
Securityadmin
int
1, if login is a member of the securityadmin server role
Serveradmin
int
1, if login is a member of the serveradmin fixed server role
Setupadmin
int
1, if login is a member of the setupadmin fixed server role
Processadmin
int
1, if login is a member of the processadmin fixed server role
Diskadmin
int
1, if login is a member of the diskadmin fixed server role
Dbcreator
int
1, if login is a member of the dbcreator fixed server role
Loginname
nvarchar(128)
Actual name of the login, which may be different from the login name used by SQL Server
Table 2-2.Syslogins View
In a change from SQL Server 6.5, syslogins is a view, not a table.
Many of syslogins’s columns are translations of status bits in the xstatus column of the sysxlogins table, and others, such as the SUID column, are computed values based on system functions. SQL Server 7.0 handles generation of the SUID a little differently from SQL Server 6.5, because Microsoft deprecated the use of SUIDs in favor of using Windows NT SIDs.
If you are familiar with Windows NT security, then you already know that each account has an SID. That SID is actually a globally unique identifier (GUID) and is guaranteed to be unique across all accounts in the Windows NT domain or aWindows 2000 forest. SQL Server uses that SID as the identifier for an account in syslogins so that it can distinguish accounts from one another. The benefit of this approach is that servers can share SIDs so that a user can access a database on more than just the local server. For accounts that use SQL Server authentication, the sp_addlogin stored procedure will then generate a GUID for the new account.
SQL Server 7.0 also generates a pseudo-SID for native SQL Server logins that is guaranteed to be unique within the server but has no relevance outside the database server. For database user accounts, SQL Server 7.0 maps the SID to a user ID in the sysusers system table (which I discuss in greater detail in Chapter 4) instead of the SUID, no matter which kind of authentication the login uses. There is an SUID column in sysusers, but it is only there for backward compatibility. In SQL Server 2000, SUIDs disappear from syslogins and sysusers completely. The end result is that in SQL Server 7.0 and later versions, all users in a database will map to an SID that either comes from a domain controller or from SQL Server itself.
NOTE Windows 98 does not support Windows NT–style login protocols; therefore, the desktop version of SQL Server 7.0/2000 only supports SQL Server authenticated logins. This is the only special consideration for the discussion of login security in this chapter.
The login process is quite simple once the user completes the connection to the server. SQL Server compares the account and password presented at login to the entries in the syslogins table. In the example output from the syslogins table shown in Table 2-3, if Jack tries to log in with SQL Server account Jack and the password PailofWater, SQL Server looks for Jack in the name column and then checks to see if the password matches.
Name
Password
guest
NULL
Jack
0x2131214A2130402F49494F46384F3
C380000000000000000000000000000
repl_publisher
0x2131214433243E392A234836262A4
12A0000000000000000000000000000
repl_subscriber
0x2131214A212B26214948353936215
9390000000000000000000000000000
NTTEST\morris
NULL
sa
0x2131214A212E2458483E3B373B3F2
F3D0000000000000000000000000000
NULL
NULL
NULL
NULL
Table 2-3.Example Output
Note that in the example, the passwords are encrypted, so you’ll have to trust me that Jack’s password is PailofWater and SQL Server does allow him access to the server.
For Windows authenticated logins, the value in the name column will be the full Windows account name, and the password column will be NULL. As you can see in the example, my account, NTTEST\Morris, has a NULL password. What you do not see in the example is the SID column, which holds the Windows security identifier for my account. Why this is significant requires a little explanation.
Whenever Windows NT/2000 authenticates a Windows account, it creates an access token, as described previously. Inside the access token is a list containing the SID for the account and all the SIDs for the groups to which the user belongs. When I log in, SQL Server does not actually look for my account name in syslogins, but instead searches for my account’s SID. If it does not find my account’s SID, it looks for a row containing any one of the SIDs for the groups listed in the access token. If two or more group SIDs have matching rows, then SQL Server pseudo-randomly picks which group to use. In all cases, if my account SID has a matching row, it is the SID used to grant access.
The ability to match any SID in the access token creates some new options for granting server access. Rather than having to grant login permission to every user individually, as you have to do in SQL Server 6.5, it is now possible to grant permission to a group and then add users who should have access to that group. This kind of structure makes it easy to manage server access at the domain level instead of at the level of the individual server. In addition, it makes assigning access rights to multiple servers very easy. You can either create a group for each server and add users to the groups based on which servers they need to use, or you can create groups that represent roles and permit login access to the servers that support those roles. For example, the latter example can be a good choice for web server farms that access multiple database servers to generate their content, because the accounts used by the web servers can all be members of a single group that has login access to the database servers. You will explore these options in greater detail in Chapter 4, but for now, here is a complete list of the options that can be assigned an SID:
Local accounts on the server running SQL Server
Local groups on the server running SQL Server
NT 4.0 domain accounts in the server’s NT 4.0 domain
NT 4.0 domain global groups in the server’s NT 4.0 domain
NT 4.0 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain
Windows 2000 domain accounts in the server’s Windows 2000 domain
Windows 2000 domain local and global groups in the server’s Windows 2000 domain
Windows 2000 domain accounts and global groups in a domain trusted by the server’s NT 4.0 or Windows 2000 domain
Windows 2000 forest universal groups in the server’s forest, if SQL Server is running on a version of Windows 2000 server
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.
As you would expect, you can set the account name, the password, the default database the user will use when he logs in, and the default language to use (for example, English, French, or Spanish). Setting this latter option helps SQL Server know which language to use for error and system messages. Application developers can also tailor an application’s messages based on this value as well.
The encryption option determines how to encrypt the password when it is stored in sysxlogins. The option can take one of the following three values:
skip_encryption:Causes the password to be stored in plain, unencrypted text.
skip_encryption_old: Indicates the password is already encrypted using SQL Server 6.5’s encryption algorithm. The only purpose of this option is to aid in upgrading a server from version 6.5 to 7.0.
Unless you have some really great need to see the passwords, it is highly recommended that you accept the default and encrypt the passwords. For all new logins, you can safely leave this option alone.
Windows authenticated logins use sp_grantlogin instead of sp_addlogin:
sp_grantlogin [@loginame =] 'login'
[@loginame =] 'login'
'login' Is the name of the Windows NT user or group to be added. The Windows NT user or group must be qualified with a Windows NT domain name in the form Domain\ User—for example, NTTEST\FrankB.
The major difference between the two stored procedures is that sp_grantlogin accepts both Windows accounts and Windows group names. If you want to use one of the built-in local groups or accounts, you can specify it using the special keyword 'builtin' instead of a computer name. Local accounts and groups will use the computer name (for example, SS7_NT_SRV\MaggieM), and domain accounts and groups will use the domain name (for example, NTTEST\FrankB).
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.
Let’s take a look at some sample commands. The first example shows how to add the Users local group to the sysxlogins table:
Sp_grantlogin 'builtin\Users'
Here, I grant login permissions to the local account MaggieM:
sp_grantlogin 'SS7_NT_SRV\MaggieM'
Next, I grant login permissions to the domain account NTTEST\FrankB:
sp_grantlogin 'NTTEST\FrankB'
Finally, I grant login permissions to the domain global group Domain Admins:
sp_grantlogin 'NTTEST\Domain Admins'
The only drawback to granting login permission to a Windows NT group as a whole is that there may be a time when a user needs to be a member of the group but does not need to have access to the server. It is for just this situation that SQL Server allows you to deny access to individual accounts or groups using sp_revokelogin:
sp_revokelogin [@loginame =] 'login'
The problem with this approach is that it does not revoke the login privileges for any other groups of which the user is a member. To keep the user out completely, you must use the following command:
sp_denylogin [@loginame =] 'login'
Table 2-4 shows the bit mappings for the xstatus column.
Purpose
Bit
Description
denylogin
1
Indicates whether the login account is permitted access to the server.
hasaccess
2
Indicates whether the login account is permitted access to the server.
isntname
3
Is the name a Windows NT account name?
isntgroup
3
Is the name a Windows NT group name?
isntuser
4
Is the account a Windows NT user account?
If bit 4 is 0 and bit 3 is 1, the account is a Windows NT group. If bit 4 is 1 and bit 3 is 1, the account is a Windows NT user.
sysadmin
5
The rest of the bits indicate membership in system roles: 0 = not a member 1 = is a member
securityadmin
6
serveradmin
7
setupadmin
8
processadmin
9
diskadmin
10
dbcreator
11
bulkadmin
12
Table 2-4.Xstatus Column
The sp_denylogin stored procedure will add an entry in sysxlogins that specifically prohibits logins by the specified account. Denied login accounts have bit 1 set in the xstatus column of sysxlogins. The decision whether to use sp_revokelogin or sp_denylogin depends on whether you want to remove an entry from sysxlogins or specifically prohibit an existing user from logging in. Removing the entry for a user account or a group from the table denies access to the server, because SQL Server will not be able to find the account’s SID. If a user has access through any other SID, however, she can still log in. Denying access to a SID is like the “No Access” permission on NTFS files in Windows NT, in that it overrides all other permissions granted to the other groups in the access token. This restriction applies even to system administrators, because you cannot be a system administrator until you log in successfully.
To reverse the effects of either sp_revokelogin or sp_denylogin, you can simply call sp_grantlogin again. If you have denied access to a user’s individual account SID, but you still want to allow that user to log in through membership in a group, call sp_grantlogin with the user’s account, and then call sp_revokelogin with the same account. The user will not be able to log in using his individual account, thanks to sp_revokelogin, but he will be able to log in using the group. That might sound a bit confusing at first, so let’s look at another example.
Execution of the following command grants login access to all members of the Domain Users global group. When it finishes, you can query either syslogins or sysxlogins and find a new row with the Domain Users name and its SID:
Exec sp_grantlogin 'NTTEST\Domain Users'
Because FrankB’s domain account is a member of Domain Users by default, he is automatically permitted to log into SQL Server. Let’s suppose, however, that FrankB is untrustworthy and should not have access to the server. To take away his right to log in—which was granted to the Domain Users group—you execute the following command:
Exec sp_denylogin 'NTTEST\FrankB'
This is preferable to revoking login permissions from the Domain Users group, because now you are telling SQL Server that FrankB is explicitly denied access. To understand why that is significantly different from just revoking his permissions, you can create another domain global group named NTTEST\SQL Server Users, make FrankB a member, and grant login access to the group:
Exec sp_grantlogin 'NTTEST\SQL Server Users'
Everyone in this group, except FrankB, will now be granted access. If you had simply used sp_revokelogin, and the deny login entry were not in sysxlogins, FrankB would now be able to log in because he is a member of both the Domain Users and the SQL Server Users groups. Membership in either one would be sufficient to grant him the right to log in. However, the explicit deny login permission overrides those permissions. Without it, you would have to revoke login permissions from both groups to prevent FrankB from logging in.
What this example shows is the potential pitfall of granting permissions to Windows groups. Because SQL Server permits users to log in if the permission has been granted to any one of the groups listed in the access token, it becomes necessary either to plan both group membership and permission assignment carefully or to deny permissions to individual members. The choice between the two approaches depends mostly on whether you want to manage permissions positively, by saying who can access the server, or negatively, by saying who cannot access the server.
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.
The server roles are a fixed set of identities that a user can assume. They represent server administrator tasks and thus are rather narrow in their scope in order to give you more granular control over what the administrators can do. This is in contrast to earlier versions that offered only either system administrator privileges or user privileges. The different server roles are summarized in Table 2-5.
Fixed Server Role
Description
sysadmin
Performs any activity in SQL Server.
serveradmin
Configures serverwide configuration options; shuts down the server.
setupadmin
Manages linked servers and startup procedures.
securityadmin
Manages serverwide security settings, including linked servers, and CREATE DATABASE permissions. Resets passwords for SQL Server authentication logins.
processadmin
Terminates processes running in SQL Server.
dbcreator
Creates, alters, drops, and restores any database.
diskadmin
Manages disk files.
bulkadmin
Allows a non-sysadmin user to run the bulkadmin statement.
Table 2-5.Server Roles
NOTE Unless mentioned otherwise, all the roles in the following sections grant their members the right to add another member to the role. I'm not really sure why Microsoft did this, but it did and you need to be aware of it.
Primary Server Roles
In this section, I refer to sysadmin, serveradmin, securityadmin, and dbcreator as primary server roles, because they encompass the operations that most administrators will perform. (Note, however, this term is not used by Microsoft.) In general, serveradmin, securityadmin, and dbcreator represent the subcategories of the system administrator’s overall duties. A judicious use of these primary server roles will allow the senior administrator to parcel out jobs according to skill level and expertise.
sysadmin
This is the role from which the sa account gets all its abilities. In previous versions of SQL Server, sa was a special account for which security checking was bypassed. In version 7.0, the sa account became a member of the sysadmin server role. The sa account is unique in that it cannot be removed from the sysadmin role.
NOTE The SQL Server sa account cannot be renamed. This means you must protect this account with a strong password, because every hacker in the world will know at least one account name on your system.
Actually, the sa account can be renamed by hacking the system table, but I do not recommend this course of action!
The sysadmin role can bestow the same privileges on its other members as it does on the sa account. SQL Server accounts, Windows NT accounts, and Windows NT groups can all be members of this role. In fact, by default the local Windows NT Administrators group for the server is granted membership in the sysadmin role during setup. If your server is also a member of a domain, it is very likely that the Domain Admins global group is a member of the Administrators local group, which means that all domain administrators will have sysadmin privileges on the server.
serveradmin
This server role confers upon its members the ability to set both server configuration options and table options. Although SQL Server 7.0 automatically configures most of the main settings, there will be times when you need to change some of them. For example, to optimize SQL Server’s performance when other BackOffice applications are on the same server, you may need to change the minimum and maximum amount of memory SQL Server uses.
Essentially, members of serveradmin have permission to run the sp_configure stored procedure. Some settings only take effect after you stop and restart the MSSQLSERVER service; others can take effect after you run the RECONFIGURE statement. This statement takes no parameters and simply activates the changes you made with sp_configure.
NOTE Interestingly, members of serveradmin cannot shut down SQL Server using the SHUTDOWN statement. Only members of the sysadmin role can execute SHUTDOWN .As an alternative, members of the Administrators local Windows group and the Server Operators local group can stop and start the MSSQLSERVER service from the Services applet in Control Panel or using the Net Stop command at the command prompt.
The serveradmin role has several other miscellaneous permissions besides being able to run sp_configure. It can drop extended stored procedures, although it cannot create them; it can run the sp_tableoption stored procedure in all databases; and it can run the DBCC printable command.
securityadmin
The securityadmin role is perhaps the second most important server role, because its members may run all security-related system stored procedures, including sp_addlogin and sp_grantlogin, as well as GRANT, DENY, or REVOKE permissions to create databases. It is also the only role besides sysadmin that may read the SQL Server error log, although anyone with access to the file system or xp_cmdshell can read this.
The securityadmin role limits who can create a database by controlling the permission to run the CREATE DATABASE statement. By granting this statement permission, securityadmin effectively controls who can create databases on the server. Notice, however, that the permission does not extend to the DROP DATABASE statement. Only the database owner and members of the sysadmin role may drop a database.
dbcreator
This role may seem as though it duplicates one of the permissions of the securityadmin role, but it really has a different purpose. This role is designed for users who will be managing their own databases because it allows them to run not only the CREATE DATABASE statement but also the ALTER DATABASE statement. It also allows them to rename their databases using the sp_renamedb stored procedure.
The primary difference between the dbcreator and securityadmin roles is that dbcreator is the only role other than sysadmin that has permission to run the ALTER DATABASE command. Members of the securityadmin role can grant users permission to create a database, but the user cannot alter the database’s size, change the location of its files, or add files or file groups. Members of the dbcreator role can make those changes as well as changes to the automatic filegrowth setting.
NOTE Membership in this role grants both ALTER DATABASE and DROP DATABASE permissions in SQL Server 7.0/2000.
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.
The secondary server roles offer very little functionality. The first two serve very specialized purposes, and the last one exists simply to maintain compatibility with previous versions of SQL Server.
setupadmin
This role is useful for one purpose: to manage linked servers. It can also configure a stored procedure to run at startup, but that job can be just as easily done by members of sysadmin.
processadmin
The sole capability of processadmin is to delete SQL Server processes that are causing problems. Because the sysadmin role includes this capability, it will be rare that you assign someone to this role, although it is useful for tech support in killing deadlocks.
diskadmin
Included mainly to provide backward compatibility with previous versions, this role has permissions to manage database disk devices, which are not used in SQL Server 7.0+. This role can run all the Disk commands such as Disk Init, Disk Reinit, Disk Refit, Disk Mirror, and Disk Remirror. It can also run the sp_diskdefault and sp_dropdevice system stored procedures.
The key feature of this role for version 7.0 is the ability to run the sp_adddumpdevice system stored procedure to add backup devices. Note that it does not confer the ability to make database backups (that is a database role); rather, it allows its members to create the devices onto which the backups will go.
bulkadmin
Added in SQL Server 2000, this role has one special permission: the authorization to run the BULK INSERT command. Interestingly, it does not grant its members any other permissions on the target table.
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.
In addition to identities mentioned in the previous sections, SQL Server 7.0/2000 has two special cases that need mentioning here: sa and guest.
The sa login account is the “superuser” account within SQL Server. There is literally nothing it cannot do within the confines of the server. There is only one sa account for each server, and it cannot be deleted or disabled. Furthermore, when a user logs in with the sa account, that user is considered to be every database’s owner. This is the one exception to the rule that only one login account can be assigned ownership to a database. You will explore how sa differs from all the other identities on the system throughout the rest of the book, but for now, just assume that sa can do any and every operation SQL Server supports.
The guest identity exists both at the login and the database levels. Users that have no other access options can log into the server using the guest login account, which is validated by SQL Server and is different from the Windows NT Guest account. Along the same lines, if a user’s login identity has not been mapped to an identity in a database, that user will assume the guest database identity. This situation is similar to “aliasing” mentioned in the last chapter. Multiple users may use the database as guest at the same time.
Both of these special identities pose their own security risks. sa must be protected because of the unlimited permissions it has on the systems, and guest must be managed carefully because it can allow access to the server and its databases without regard to the appropriateness of that access. It turns out that with Windows Only mode turned on, SQL Server completely ignores non-Windows logins, effectively disabling the sa account. Because the sa account does not have a valid Windows SID, there is no way to use the sa account to log in. This is worth highlighting in a Note.
NOTE Using Windows Only mode disables all SQL Server authenticated login accounts, including the sa account.
As a note of caution, if you decide to use only Windows authentication, be sure to assign an account to the sysadmin role (by default, the Windows local Administrators group is a member). The reason is that only sysadmin can perform some operations. If you forget, you can set SQL Server back to Mixed Mode security by setting the following registry variable to 0:
Finally, if you decide to use Mixed Mode, sa is a special account in SQL Server and the other members of the sysadmin role will not be able to read or change its password. Remember to make the password for the sa account a long string of numbers, letters, and special characters, and keep it in a safe place. That way you will always have access to an administrator account if any problems arise.
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.
In summary, remember that in general, authentication is a matter of trust. Different authentication mechanisms have different levels of perceived trust. Supplying an account and password proves only that the user knows two pieces of information. The account is usually public information, and the password is often something someone can guess or derive through brute force testing of specific patterns of characters. There are other ways to identify users, but they all have some flaw that makes them imperfect. For example, there is an old story of a bank in Australia that thought thumbprint scanners were the perfect way to identify users at automated teller machines until kidnappers started forcing victims to use their thumbs to log in and withdraw money. Some of the more ruthless robbers made the rather gruesome discovery that they did not need their victims to be alive to make withdrawals. This is an extreme example, but it does show that attackers will try to circumvent even really good authentication mechanisms if the reward is great enough.
The key is to make the task so difficult that an attacker will prefer to look elsewhere for another target. The first step is to ensure all accounts use long, complex passwords that are difficult to guess. All versions of SQL Server support a password length and a list of legal characters that can potentially make brute force or dictionary-based password attacks completely infeasible. It does not matter whether you use Windows authenticated logins or SQL Server authenticated logins. Windows’ list of legal characters for passwords is different from SQL Server’s, but it is still possible to have passwords on Windows accounts that are just as hard to break. As a rule of thumb, the minimum requirement should be eight-character passwords with both upper- and lowercase letters, at least one number, and at least one special character. Every character beyond eight increases the security of the password by several powers of ten, so use more than eight characters if you can. For example, all my production servers have aminimum of fourteen characters and after a while, it takes a fraction of a second longer to type the six extra characters. The added protection is worth it.
The next most important task is to secure the password exchange between the client and server. If you use SQL Server authenticated logins, use IPSec, SSL, or the Multiprotocol network library encryption to encrypt the password. The security offered by strong passwords can still be foiled if an attacker can read them in plain text in the network packets sent during the login process. If you use Windows authenticated logins, seriously consider upgrading from Windows NT to Windows 2000 or XP for both the database servers and the clients. The NTLM authentication protocol has been broken, so you need to move to NTLM version 2 to protect your passwords. If moving to Windows 2000 or XP is not an option, IPSec can be a good way to protect the authentication network traffic.
Of the three main network libraries—Named Pipes, Multiprotocol, and TCP/IP—Named Pipes offers the least security for authentication, at least for SQL Server 7.0 and 2000. That makes it a poor choice for insecure environments. For SQL Server 7.0 and 2000, the TCP/IP network library combined with SSL is a good alternative to the Multiprotocol network library’s encryption. The main drawback is that each server will need its own digital certificate for SSL, whereas the Multiprotocol network library does not need anything else. If obtaining adigital certificate is not a significant impediment, the TCP/IP network library with SSL is a superior choice for SQL Server 7.0 because the encryption algorithm is stronger. For SQL Server 2000, the Super Socket network library is the best choice for speed and encryption.
SQL Server 6.5 has several limitations in terms of securing the authentication process. SSL is not an option at all, and its TCP/IP network library does not support Windows authenticated logins. For environments that use Windows NT, the weakness in the NTLM authentication protocol makes Windows authenticated logins a little more risky than SQL Server authenticated logins because an attacker that compromises a Windows account can use it to log into SQL Server. At least with SQL Server authenticated logins, there is an additional step in the authentication process that can be encrypted using the Multiprotocol network library. The fact that SQL Server 6.5 only supports three levels of access for Windows authenticated logins—user, guest, and sa—means that administrators have a limited range of choices for granting server access.
The general rule is that if you protect the authentication process, unauthorized users will not have access to the data on the server. Protecting the sa account and the accounts that are members of the sysadmin role (in SQL Server 7.0 and 2000) is critically important because those accounts have unlimited privileges within SQL Server. Just remember that the authentication process involves both Windows and SQL Server; therefore, you must be alert for security flaws that affect either one of them. Keeping your patches and service packs up to date is vital to keeping your login authentication safe and secure.
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.
/ 20 
A Security Roadmap
