All About Passwords - Page 2

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:

            @encryptopt=skip_encryption

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:

            @encryptopt=skip_encryption_old

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