Posts Tagged ‘SQL Security’

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

Advertisements

Sometime SQL Server error msg 8101 occurred, when anyone is trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and trying to assigning a value to the identity column instead of letting SQL Server assign the value. Error displays as

Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table “Table_Name” can only be specified when a column list is used and IDENTITY_INSERT is ON.

 The solution for above error is , we should include SET IDENTITY_INSERT ON,

Example :

SET IDENTITY_INSERT Table_name ON
Go
Insert into Table_Name (Col1,Col2,Col3,Col4)
Select Col1,Col2,Col3,Col4 from Any_Table_Name
Go

SET IDENTITY_INSERT Table_name OFF
Go