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 ['
, ', @encryptopt=skip_encryption'
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
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.
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 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
"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.
article details a worm, which attacks SQL Server machines with a blank SA
See All Articles by Columnist Neil Boyle