All About Passwords

Wednesday Feb 12th 2003 by Neil Boyle

Neil Boyle explains the mechanisms SQL Server uses to secure itself from unauthorized access, and discusses best practice for selecting and administering passwords.

In this article, we will look at the mechanisms SQL Server uses to secure itself from unauthorised access by implementing password-controlled access, and discuss best practice for selecting and administering passwords.

The two security models

SQL Server has two different ways of authenticating users--called Standard and Integrated.

  • In Standard security, SQL Server maintains it's own Login IDs and passwords,.
  • With Integrated security, no passwords are maintained within SQL Server, and SQL Server relies on Windows NT/2000 to do the job of authenticating users.

You, as a DBA, have a choice of implementing security in either NT/2000 or SQL Server, or a combination of both (giving rise to a third term: mixed security)

In fact, in later versions of SQL Server, Microsoft has removed the option of using only Standard security; you can only choose to implement Integrated or Mixed mode, (though if you choose Mixed, there is nothing to force you to use the Integrated part).

Here are a couple of advantages of using each method--there are plenty more, but we could go on forever...




Easier to move USER IDs across servers (especially on different domains)

Passwords can expire regularly

Maintained entirely within the DBA environment

Users can change their own passwords

Provides a second level of authentication (If a user walks away from their PC leaving it logged, in, anyone else could then log on to a Trusted connection (i,e, Integrated) SQL application SQL Server a NT/2000 has already authenticated the real user)

No password worries about password encryption methods

Client applications do not need to store a system password in the registry or in hard coded form.

SQL Server password encryption models

Depending on the version, SQL Server had implemented three (ish) modes of encrypting passwords...

SQL Server 4.21

Passwords were stored unencrypted

SQL Server 6.0 and 6.5

First generation encryption

SQL Server 7 and 2000

Second generation encryption

That's right--early versions of SQL Server did not encrypt the passwords at all--anyone who could "select" from the master..syslogins table could potentially see all users passwords. Obviously not everyone could see into syslogins, but even the SA should not be able to see other users' passwords. Change them, yes, but not see them.

Versions 6.0 and 6.5 sensibly introduced an encryption model, which was upgraded for SQL Server 7.

How SQL Server encrypts and stores passwords

SQL Server (not 4.21) implements 2 functions for dealing with passwords:

  • Pwdencryp()t encrypts a password, returning the encrypted string. This is used when you set a password, and the encrypted password is stored in the master..syslogins table.
  • Pwdcompare() accepts a clear password and an encrypted one, and checks whether they match by encrypting the clear password and comparing the two. When you type your password to log into SQL Server, this routine is called.

Note that there is no routine to decrypt the encrypted password. Otherwise, the SA, or a malicious attacker, could still get at all the other users passwords.

These routines are right there for you to play with--why not try it in Query analyser?

select pwdencrypt('hello')

You should see a long binary string, which represents your encrypted password.

Now try it again, and see if you notice anything strange!

The password that came out the second time was probably different to the first one. This process is called "salting", and is designed to make the password harder to reverse engineer. As well as a standard encryption routine, the current time is noted, and this is used as a second layer of protection by applying a mathematical function based on the password and the time.

The pwdcompare() routine knows how to deal with this because it knows that the time the encrypted password was encrypted is stored (in encrypted format--still with me?) within the encrypted password. Pwdcompare{} will return a 1 or 0 depending on whether the clear and encrypted passwords are compatible.

Try cutting & pasting your encrypted password into the two calls below--you will see the return codes for the valid and invalid passwords, and even though the same password encrypted at different times produces different encrypted strings, you will see that pwdcompare() can deal with this.

select pwdcompare ('hello', encrypted_pwd_here_NO_QUOTES

select pwdcompare ('goodbye', encrypted_pwd_here_NO_QUOTES

Moving passwords around

Because SQL Passwords are accessible to the SA (in encrypted format) we are able to migrate passwords to another server (should we wish to migrate a database to a new server, but not necessarily migrate the whole server).

To add logins to a new server, we can use the standard SQL Server Stored Procedure sp_addlogin to extract a login on a old server, and migrate its current password. On the old server we can run something like this:

select	'exec sp_addlogin ['
+	name
+	'],'
,	password
,	', @encryptopt=skip_encryption'
from	master..sysxlogins

The resulting 'exec sp_addlogin' commands will let you transfer users to another server without having to worry about them losing their passwords. The magic is in this clause:


This tells SQL Server not to encrypt the password--it's already encrypted.

The code above lets you migrate SQL 7 and 2000 passwords between servers, but you can also use this technique to "upgrade" 6.X users to SQL 7--just replace the @encryptopt cause with this:


This tells a SQL 7 or 2000 server that the password was encrypted using the old algorithm (which 7.0 and 2000 still know how to deal with).

When you use this option, a flag is set in the syslogins table so that SQL Server will know to use the correct algorithm when the user tries to log in each time.

The unencrypted password

Even the latest versions of SQL Server can contain unencrypted passwords--this occurs when the password is NULL, and the encrypted password appears as an empty string. Blank passwords are bad practice at the best of times, but this is positively giving the game away--so why not audit your syslogins table and surprise those people who still have a blank password?

Integrated security and syslogins

Integrated Security logins also have a blank password in syslogins, but that is not a problem. Remember--there users are authenticated by NT or Win 2000, not by SQL Server. You can spot Integrated Security by looking for the following flags in syslogins being set to 1.

  • isntname
  • isntgroup
  • isntuser

Passwords and application roles

Another place where passwords are stored, and the pwdencrypt() and pwdcompare() functions are employed, is in the sysusers of a database, for application roles.

Application roles are a way of password protecting a set of rights in a database, which are not connected to any specific user. Any user that can enter a database, even if they have no other rights in the database, can invoke an application role and inherit the rights of that application role. All they need to know is the correct password.

Microsoft took a step in the right direction when they implemented application roles--you cannot specify a NULL password for an application role. Unfortunately you can still specify an empty string, but at least pwdencrypt('') produces something that looks like a real password, whereas pwdencrypt(NULL) produces a NULL result every time, which sticks out like a sore thumb to any attacker who can access a list of encrypted passwords.

Passwords and basic psychology

A friend of mine who used to work on a Network Admin used to give out a speech, when informing a new user of their password, (which they would have to change at their first login), that went something like this:

"Don't use a blank password, and don't use 'password'. Don't use your name, your partner's or kid's or pet's name, your maiden name, your mother or father's name, your phone number, your car registration or your job title. Do not use "It's me" or 'only me', and do not use anything you could find in a dictionary.

That is very good advice!

If everybody who knows you knows that you are an Elvis fanatic, don't use 'Elvis' or 'Presley' as a password. If you are in love with Laura Dern, don't use 'Laura' or 'Dern' or 'JurassicPark'--try to pick something that somebody else would not guess you might use as a password.

Do not use stuff that is easy to guess, and try not to use words that are in the dictionary--search for 'dictionary attack' in google if you want to know why this is.

Now that I have (hopefully) convinced you to use a reasonable password, try & convince everyone else to do the same.

And finally, some interesting password relate links

Do not believe that encryption of passwords is unbreakable. Microsoft introduced new encryption routines in SQL 7 for a reason--the old ones were too easy to break. Even the new routines are not totally secure. Check out this article.

This article details a worm, which attacks SQL Server machines with a blank SA password.

» See All Articles by Columnist Neil Boyle

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved