SQL Clone
SQLServerCentral is supported by Redgate
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.


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: 6640 | Views in the last 30 days: 1
Related Articles

changing passwords at next login?

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


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


Changing Linked Server Passwords

How to article on changing passwords for linked servers via TSQL and SSMS.


Password change

Password change


change password

change password

sql server 7    
stored procedures