Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Make Sure You Have Good Passwords

By Robin Back,

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.

Total article views: 6627 | Views in the last 30 days: 1
 
Related Articles
FORUM

changing passwords at next login?

How do users change passwords at next login when they are sql server logins?

BLOG

PowerShell – Change SQL Server Login Password

Here’s a quick post detailing a PowerShell script that can be used to change the password for a SQL ...

FORUM

Password change

Password change

FORUM

change password

change password

FORUM

List all Logins in the server and change the CHECK_POLICY=ON"

List all Logins in the server and change the CHECK_POLICY=ON"

Tags
miscellaneous    
security    
sql server 7    
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones