Hi Guys, Now days Payment Card Industry Data Security Standards (PCI DSS) is a preventative standard intended to reduce the risk of payment card fraud and information theft. As per my views after SQL server installation on Production Box, I hope below settings should be set using SP_CONFIGURE as,
- The ‘Ad Hoc Distributed Queries’ Server Configuration Option should be set to ‘0’
- The ‘CLR Enabled’ Server Configuration Option should be set to ‘0’
- The ‘Cross DB Ownership Chaining’ Server Configuration Option should be set to ‘0’
- The ‘Database Mail XPs’ Server Configuration Option should be set to ‘0’.
- The ‘Ole Automation Procedures’ Server Configuration Option should be set to ‘0’
- The ‘Remote Access’ Server Configuration Option should be set to ‘0’
- The ‘Remote Admin Connections’ Server Configuration Option should be set to ‘0’.
- The ‘Scan For Startup Procs’ Server Configuration Option should be set to ‘0’.
- The ‘SQL Mail XPs’ Server Configuration Option should be set to ‘0’
- The ‘Trustworthy’ Database Property should be set to Off
- Unnecessary SQL Server Protocols should be disabled.
- SQL Server should be configured to use non-standard ports
- The ‘Hide Instance’ option should be set to ‘Yes’ for production SQL Server instances.
-
Non-clustered SQL Server instances within production environments should be
designated as hidden to prevent advertisement by the SQL Server Browser service.
- The ‘sa’ login account should be disabled or renamed.
Extended Stored Procedures
The following extended stored procedures should not be used by any application or maintenance script.
- Execute on ‘xp_availablemedia’ to PUBLIC should be revoked.
- The ‘xp_cmdshell’ option should be set to disabled
- Execute on ‘xp_dirtree’ to PUBLIC should be revoked.
- Execute on ‘xp_enumgroups’ to PUBLIC should be revoked.
- Execute on ‘xp_fixeddrives’ to PUBLIC should be revoked.
- Execute on ‘xp_servicecontrol’ to PUBLIC should be revoked.
- Execute on ‘xp_subdirs’ set to PUBLIC should be revoked.
- Execute on ‘xp_regaddmultistring’ to PUBLIC should be revoked.
- Execute on ‘xp_regdeletekey’ to PUBLIC should be revoked.
- Execute on ‘xp_regdeletevalue’ to PUBLIC should be revoked
- Execute on ‘xp_regenumvalues’ to PUBLIC should be revoked.
- Execute on ‘xp_regremovemultistring’ to PUBLIC should be revoked
- Execute on ‘xp_regwrite’ to PUBLIC should be revoked
- Execute on ‘xp_regread’ to PUBLIC should be revoked.
Authentication and Authorization
- The ‘Server Authentication’ Property should be set to Windows Authentication mode
- CONNECT permissions on the ‘guest user’ should be revoked within all SQL Server databases excluding the master, msdb and tempdb
-
Orphaned Users should be dropped from SQL Server database. A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance and is referred to as orphaned and should be removed.
Password Policies
- The ‘MUST_CHANGE’ Option should be set to ‘ON’ for all SQL authenticated logins
- The ‘CHECK_EXPIRATION’ Option should be set to ‘ON’ for all SQL authenticated logins within the ‘Sysadmin’ Role
-
The ‘CHECK_POLICY’ Option should be set to ‘ON’ for all SQL authenticated logins
- Auditing and Logging
- The ‘Maximum number of error log files’ setting should be set to greater than or equal to 12.
- The ‘Default Trace Enabled’ Server Configuration option should be set to 1.
- ‘Login Auditing’ to both failed and successful logins
Thanks for Reading, Keep smiling, keep learning
WikiDBA 
Good article but, just like the PCI standards themselves, there are some huge misunderstandings as to what is effective.
For example, everyone thinks that ad hoc distribute queries and xp_CmdShell are some of the biggest security risks in the world. In truth, they’re not because no one but folks with “sysadmin” (affectionately known as just “SA” privs and not to be confused with the actual “SA” login) can use either of those things. In fact, only the people that can use them can turn them on or off and that’s a good thing.
To keep this as short as possible, turning those things off may actually cause “security complacency” in that they give everyone the sense that their system is “hardened” and absolutely nothing could be further from the truth. If someone gets into the system as an individual with “SA” privs, it’s all over but the crying and the news articles about the fact that you got broken into.
Although attacker software is specifically geared to turn on xp_CmdShell and other things in the first 3ms that the attacker software gains access, they don’t actually need p_CmdShell to do the same kind of damage once they get in with “SA” privs.
And, no… they’re not going to break in as themselves. They going to break in as one of the existing logins, which are pretty easy to guess because people use the first initial and last name of a person for most logins. It just a matter of playing rainbow tables against your silly junk passwords (“leet” and keyboard patterns figure prominently in rainbow tables) or exploiting a little of the knowledge that you’ve shared on things like FaceBook, Twitter, LinkedIn, and other social websites. They might even guess your password just by looking up public information about you that businesses and the town you live in offer up and it doesn’t take much digging to find such things. And, if someone really wants your goodies, you’ll be amazed at the number of friends you’ll suddenly have at the local bar.
“Hardening” is fine but when it comes to some of the features of SQL Server, disabling such features as OPENROWSET and xp_CmdShell, etc, ad infinitum, does NOTHING but provide a 3ms diversion to hacker’s attack software while, at the same time, depriving your team of some really useful tools. Yeah, I know. A lot of people say “Well, just use SSIS instead”. Yeah…. you just doubled your surface area by doing that and have unwittingly given people that shouldn’t have it some incredible implicit privs through your connections. You even unwittingly give them massive privs back to SQL Server so that they can run “scripts”.
The leading cause of database break-ins is NOT xp_CmdShell or any of the things that thousands, perhaps, millions of people fear the most. The leading cause is STILL SQL Injection followed closely by insufficient, non-existent, or just plain stupidity on the part of the developers and administrators especially when it comes to password policy.
Don’t for a minute think that your server is hardened just because you’re depriving yourself of using some of the more useful features of SQL. The real keys are
1. No one gets “SA” privs except DBAs and properly written stored procedures. Notice that SSIS is NOT included in that group.
2. Applications should never have more than Read/Write privs and only PUBLIC privs if you’re a believer in stored procedures .
3. NEVER give a non-“SA”-prived person “proxy access” to anything that would ordinarily require that user have “SA” privs especially xp_CmdShell, PowerShell, etc. Instead, write well protected single purpose stored procedures that can only do certain things with those and then give the user privs to run the stored procedures.
4. Remember that “DBA” stands for DATABASE Administrator and not SYSTEM Administrator. If you don’t or can’t trust your DBAs, you either need to find new DBAs that can be trusted or just give them Backup Operator and other “just enough” privs and call it a day.
Along with safeguarding PII, the primary goal of security is to keep people from gaining access when they shouldn’t have access, especially as “SA”.
p.s. To reiterate an tremendously important point made in the article, if you haven’t disabled the “SA” login yet, you’re making one of the worst security mistakes there is. š
LikeLiked by 1 person