All About Passwords

February 12, 2003

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...

SQL Server password encryption models

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

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
)