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

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

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

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

Neil Boyle
Neil Boyle
Neil Boyle left school at the age of sixteen thinking that computers were things that only existed in Star Trek. After failed careers as a Diesel Mechanic, Industrial Cleaner, Barman and Bulldozer Driver he went back to college to complete his education. Since graduating from North Staffs Poly he has worked up through the ranks from Trainee COBOL Programmer to SQL Server Consultant, a role in which he has specialised for the past seven years.

Latest Articles