All About 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…

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

)