Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 12, 2003

All About Passwords

By Neil Boyle

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

Standard

 

Integrated

Easier to move USER IDs across servers (especially on different domains)

Passwords can expire regularly

Maintained entirely within the DBA environment

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


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM