June 15, 2017 at 8:33 am
Hi All - We are about to setup a new SQL environment and I'm looking for some best practices/checklists for securing SQL. I've read a few articles through Google searches but I'm wondering if anyone can recommend any resources in particular on this subject. Thanks for any help.
June 16, 2017 at 1:28 am
Here are some important security guidelines:
- Use Procedures and Views for object access
- Use DB Roles as containers for users
- Manage user permissions at the role level
- Group like objects into schemas
- Manage object permissions at the schema level
- Have distinct schema owners (login-less)
- Use "Execute As" to control execution context
- Use cross-database ownership chaining only if multiple databases are treated as a single unit
- Remember the principle of Least Privilege
June 16, 2017 at 5:53 am
This is a huge topic and there are many things to consider. Here's a few high-level points:
1. At a minimum, have a firewall and IPS.
2. Disable the sa login and leave it disabled - always. Limit the number of Windows logins with the sysadmin server role.
3. Take the point above about least privilege to heart. I like to have a goal of a login used by an application having no permissions other than public and execute privs on selected stored procedures.
4. Take it seriously and never stop learning.
The MS Document on securing SQL Server is located at https://docs.microsoft.com/en-us/sql/relational-databases/security/securing-sql-server.
Here's a paper on the topic: https://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx
June 16, 2017 at 8:59 am
Thanks a bunch for the tips.
Ed - I saw the first article but didn't know about the second. I will give it a read.
June 16, 2017 at 10:29 am
Add at least failed login auditing. This will help you determine if someone is trying to hack.
June 16, 2017 at 10:57 am
Steve Jones - SSC Editor - Friday, June 16, 2017 10:29 AMAdd at least failed login auditing. This will help you determine if someone is trying to hack.
SQL Server Agent will even notify you instantly when there's a failed login if you want it and have DBmail and an Operator profile setup:
EXEC sp_add_alert @name = 'Error 18456: Login Failed', @message_id=18456, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18456: Login Failed', @operator_name='DBAs', @notification_method = 1
June 16, 2017 at 1:29 pm
Okay, thanks Steve and Chris. That was going to be my next question - What you all recommend for monitoring that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply