SQL Server HARDENING best practices after Installation

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

SQL Server Hardening

Hi, As DBA, its many time we have to faced Questions from Auditors or Clients where your SQL Server follow SQL Server Hardening best practices?, Here are some few as,

• During installation, Install only required components, When the SQL Server installation is complete, harden the SQL Server environment.
  
• After the installation, use the SQL Server Configuration Manager tool in order to disable unnecessary features and services.
  
• Install the most recent critical fixes and service packs for both Windows and SQL Server.
  
• When you’re selecting authentication modes, Windows Authentication is a more secure choice
  
• If there is still a need to use SQL Authentication – enforce strong password policy.
  
• Do not use the SA account for day-to-day administration, logging on to the server remotely, or having applications use it to connect to SQL. It is best if the SA account is disabled and renamed.
  
• Create a role-based security policy with the Security Configuration Wizard tool.
  
• Create policies with Policy Based Management enable/ disable unnecessary features and services.
  
• After the server has been hardened, periodically asses the server’s security using the MBSA (Microsoft Baseline Security Analyzer) and SQL Server BPA (Best Practices Analyzer).
  
• For production SQL Servers running mission-critical databases, either hide the instance or disable the SQL Server Browser service.
  
• Change the default ports associated with the SQL Server installation to put off hackers from port-scanning the server.
  
• Enable a firewall to filter unnecessary and unknown traffic.
  
• At the very least, set security auditing to failed login attempts; otherwise, both failed and successful logins should be captured and monitored.
  
• If upgrading previous versions of SQL Server, remove the BUILTIN/Administrators group from the SQL Server Logins.
  
• Use the IIS Lockdown and URL Scan tools to harden IIS.