Authenticating Logins
(Page 1 of 24 )
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. |
Next: Creating Strong Passwords >>
More MS SQL Server Articles
More By Apress Publishing
/ 16 
