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:
- 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:
- 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:
- 10 Steps to Help Secure SQL Server 2000
- Microsoft Baseline Security Analyzer
- SQL Server 2000 SP3 Security Features and Best Practices
- Security Section of Operations Guide
One thought on “Security Best Practices”
Quite an interesting post about "Execute As". This has deffinitely woken me up since we use Execute AS in stored procedure to perform elevated tasks. But that’s not why I am here. I am an avid follower of SQLSkills.com and thought my question would be best answered here. We constantly encounter this error when we try to attahc MDF and LDF files stored on our shares for developers consumption. What we do is copy these files in our TEMP directory and then do an "Attach database". And quite often we receive the Access is denied error. The test says to the effect "unable to open file, OS error 5, Access is denied, MSSQL error 5120".
On several posts the solution mentioned is to add the SQL Service account to the ACL of the directory and give it full control. In our case the account is NT Authority\Network Service. The default ACL on the files is: Authenticated Users, SYSTEM, Machine Administrators (machine_name\Administraotrs) and Machine Users (machine_name\Users). The SYSTEM and machine admins have full control, Auth. users have everything except full control and machine users have Read and Execute and List folder contents permissions.
Granting the service account (we tried adding "network service" to the ACL) has never worked in our case. We invariably have to give full control to the machine_name\Users group to be able to successfully attach this database and I would desperately like to understand why we have to do this and why the network service account never works.