Have you any issues? (re-post from http://blog.solutionsctrl.com/sql-security-part-2)
Can you answer yes to all these questions:-
• Do you have a defined security policy that tells you what are acceptable settings?
• Have you and do you regularly check compliance against these?
• Do you audit system configuration changes?
• Do you get notified about these changes?
• Does the DBA audit the system?
The last one’s a difficult one, I‘m a DBA and am completely trustworthy and honest (ironically trustworthy is a setting you should have switched off). However to have a robust system – and I’ve seen attempts at a few – you should audit the administrators, but hey let’s not run before we can walk. The ones we need to worry about first off are the users that have more permissions than they need to and more than you want them to have! But let’s not get too far ahead, what we really need is a security policy.
Define the Security Policy
This is a must, else how do you know what’s acceptable and what isn’t? Best practices are best practices, it doesn’t mean they will always fit in at your organisation or company. If you veer away from them and by doing so increase your risk profile, then it should be documented with management having visibility of the risks they are carrying. It can also help you make a case to get the versions of SQL you need/want to make sure the systems are secure.
Data is a company’s business nowadays, data loss either accidental or malicious can have a detrimental impact on the running / reputation of a company.
So we need to define the rules in play:-
Define what is mandatory e.g. xp_cmdshell should not be enabled, if it is we need to know where and why. There needs to be business requirement and justifications, there are always many ways to achieve the same thing in SQL.
Define what can be optional e.g. Production environment should be segregated from development and test environments. Remember, it’s your environment, it needs to be tailored to you, there is no point in saying production should be separated if you are a small company and it will never happen as you only have one server.
Cover all aspects including:
• Standard SQL server build settings, what’s installed and not installed. What’s the standard?
• What accounts should be used? Identify the permissions required for service accounts, don’t just give them local admin, sysadmin. I have seen the same account used everywhere this is a no no.
• What settings and configurations should be switched off / on?
• Security who are the administration groups? Who needs access? Don’t under any circumstances give Sysadmin out like sweets.
Then once you have defined your security policy (this can be a work in progress) at least you’re starting to mitigate your risks and check how compliant you are. Work out the risks and start to fix them or at least highlight the risks so you can get buy in to start to remove them.
That I’ll cover next…