Today Michele and I were chatting about Security. She’s focused a lot on it these days and is chatting with everyone about their best practices… I thought I’d mention a few important links/ideas here:

Login Mode:

  • Allow windows authentication only – making sure that the sa account has a strong password set (regardless of the fact that with Windows Only set it’s effectively disabled)
  • When that’s not possible and you need to allow SQL Authentication then make sure to use SSL so that the passwords are not transmitted in clear text.

Default Accounts Allowed Access – should you remove any?

  • Do not remove the sa account – only make sure it has a strong password.
  • Consider removing Builtin\Administrators (which is local machine\administrators group) BUT make sure that you have the following accounts setup as administrator or you could end up finding yourself locked out:

    • YOU!
    • Your SQLAgent
    • Your Cluster Admin – if you’re running SQL Server on a Cluster

NOTE: if you do find yourself locked out with no way in… and have your server set to windows authentication only then as an NT Admin you can atleast set your SQL Server to Windows/SQL Auth and then login with the sa account (hopefully you know the password and hopefully you’ve set the password, realize that if your password is blank AND you do this there’s nothing that will remind you that you have a blank SA password. If you change the authentication mode in SQLEM (but remember in this strange case you have no way to get in) then at least that prompts you for an SA password). PLEASE – get in and set the SA password ASAP. The registry key change is detailed in this KB Article in the section titled: Turn on Mixed Mode Authentication After You Install MSDE.

Interesting related link: PRB: Unsecured SQL Server with Blank (NULL) SA Password Leaves Vulnerability to a Worm

Above all – make sure that you adhere to many of the common best practices recommended: