SQLServerCentral Article

How to Make Sure You Have Good Passwords

,

As many articles have covered SQL passwords are very easy to figure out. SQL passwords

are stored in the sysxlogins table in master database. To access this table you have to be

a member of the fixed role sysadmin or sysadministrator. Still there are programs out

there that use the lack of SQL accounts lockout to hack a password. What can

we do to prevent this or at least make it harder for them?

Log logins

When performing an installation of SQL server you will be asked if to use SQL Server and/or

Windows authentication, default it is set only to Windows authentication. Even

though you

choose not to allow SQL logins this is not that hard to change in the registry and then

allow SQL accounts to login as well. There is always at least one SQL account to hack -

sa.

If you change to do some type of log of logins, you will have a fair chance to trace

someone trying to hack your SQL server through a SQL password. To change so that SQL server

log logins in the errorlog you right-click on the server in SQL Server Enterprise Manager

and select Properties. Change to the Security-tab and review your options:

    Audit level

  • None
  • Success
  • Failure
  • All

Choose carefully, since All and Success might cause your errorlog to fill

up the disk rather quick depending on the applications/users using your SQL server.

Any changes made at the Security-tab needs a restart of SQL server to take affect.

After changing you are able to use different ways to check the SQL server errorlog for any

login attempts, but that "how to" will not be covered in this article.

Safe passwords

Dictionary word are one of the first ways any hacker will try to crack a password, that

is why a combination of letters, digits and special characters are required in order to

have a really safe password. Making any password to hard to remember will end up in users writing their

passwords in a document or even on a "post it".

My idea of a safe password that is not to hard to remember:

    Password requirements

  • Minimum 6 characters long
  • Minimum 1 uppercase letter
  • Minimum 2 alphanumeric characters
  • Maximum 2 equal letters after each other
  • Not the same as the login

There are only two sp's that perform any type of password check sp_addlogin and

sp_password. It is not recommended to make any changes in any stored

procedures

shipped by MS but this is the only way you can check for password requirements.

Update stored procedures

After updating the two stored procedures and inserting a password check for new or existing

users can add a insecure password. Then trying to, an error message will appear prompting for

a safer password:

First, make a backup of your master-database, then run the two scripts:

Remember that both scripts may be updated in hotfixes and servicepacks. So

remember to check

the password requirements after applying a SQL update.

Note: None of the existing password will be checked. This will only affect existing

users that change their current password and new users that will be added to SQL server.

Existing passwords

This is only a fairly easy check of the existing passwords in your SQL server. The only

things that will be checked is if the password is:

  • NULL
  • Same as login name *
  • Same as login name but reversed *
  • Only one char long

Run this script in Query Analyzer:

The script will generate a list of the SQL users passwords that are fairly easy figure out.

Talk to the users and make them understand about the password sequrity, and what they should

do about it.

* Same does not check for upper- and lower cases. SQL login "abc" and password "Abc"

will report as same.

Summary

Even if you do all of above, your SQL server will not be safe from hackers. Your

environment

is always a potential target of any hackers out there. But at least you have made it harder for

them.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating