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?
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
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 -
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
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.
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
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.
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:
- 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
is always a potential target of any hackers out there. But at least you have made it harder for