Few weeks ago, I presented the session on security via Policies for “Les journées SQL Server 2014”, organized by the French SQL Server User Group (GUSS) in Paris.

b2ap3_thumbnail_presentation.JPG

I promised to post our policies script on a blog.

Security Policies are split into 4 categories:

  • Server
  • Instance
  • Database
  • Data

architechture.png

All policies follow this naming convention:

dbi_”Level number”_”Level name”_”Policy name”

I give you the policy name, the condition and the query or facet associated.
To understand correctly all policies, I suggest you to read this blog. I advise you to understand policies level by level.

Level 1: Server

  • Enable Windows Firewall (3 policies – one per profile)
    • Policy name: dbi_security_l01_server_firewall_domain_profile
    • Condition: is_firewall_domain_profile_enabled
    • Query: exec master.sys.xp_instance_regread @rootkey=N'HKEY_LOCAL_MACHINE',@key=N'SOFTWAREPoliciesMicrosoftWindowsFirewallDomainProfile', @value_name=N'EnableFirewall', @value=@get_value OUTPUT

     

    • Policy name: dbi_security_l01_server_firewall_service_profile
    • Condition: is_firewall_service_profile_enabled
    • Query: exec master..xp_instance_regread @rootkey=N'HKEY_LOCAL_MACHINE',@key=N'SOFTWAREPoliciesMicrosoftWindowsFirewallServiceProfile',@value_name=N'EnableFirewall', @value=@get_value OUTPUT

     

    • Policy name: dbi_security_l01_server_firewall_public_profile
    • Condition: is_firewall_public_profile_enabled
    • Query: exec master.sys.xp_instance_regread @rootkey=N'HKEY_LOCAL_MACHINE',@key=N'SOFTWAREPoliciesMicrosoftWindowsFirewall PublicProfile',@value_name=N'EnableFirewall', @value=@get_value OUTPUT
  • Allow only required network protocol (one policy per protocol)
    • Policy name: dbi_security_l01_server_protocols_named_pipes
    • Condition: is_named_pipes_enabled
    • Facet: @NamedPipesEnabled

     

    • Policy name: dbi_security_l01_server_protocols_shared_memory
    • Condition: is_shared_memory_enabled
    • Query: SELECT count(*) from sys.dm_server_registry WHERE registry_key LIKE '%TCP' and value_name='Enabled' and convert (nvarchar(5),value_data) ='1'

     

    • Policy name: dbi_security_l01_server_protocols_tcpip
    • Condition: is_tcp_ip_enabled
    • Facet: @TcpEnabled
  • Change default SQL Server ports associated with the SQL Server installation ad use fixed port
    • Policy name: dbi_security_l01_server_protocols_fixed_ports
    • Condition: is_fixed_port_in_the_range
    • Query: SELECT cast(convert (nvarchar(5),value_data) as int) from sys.dm_server_registry WHERE registry_key LIKE '%IPAll' and value_name='TCPPort'

     

    • Policy name: dbi_security_l01_server_protocols_dynamic_ports
    • Condition: is_dynamic_port_enabled
    • Query: SELECT count(*) from sys.dm_server_registry WHERE value_name='TcpDynamicPort' and registry_key NOT LIKE '%AdminConnection%' AND convert(nvarchar(5),value_data) !=0 AND convert(nvarchar(5),value_data)!=null
  • Hide SQL server instances or disable the SQL Server Browser Services
    • Policy name: dbi_security_l01_server_protocols_hide_instance_or_browser
    • Condition: is_instance_hide
    • Facet for the SQL Server Browser Service: @BrowserStartMode
    • Query for the hide instance option: exec master.sys.xp_instance_regread @rootkey=N'HKEY_LOCAL_MACHINE',@key=N'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLib',@value_name=N'HideInstance',@value=@get_value OUTPUT
    • In this case, I also create 2 distinct policies for each clause:
      • dbi_security_l01_server_browser_service
      • dbi_security_l01_server_hide_instance
  • Use “Extended Protection for Authentication”
    • Policy name: dbi_security_l01_server_protocols_extended_protection
    • Condition:is_extended_protection
    • Query: exec master..xp_instance_regread @rootkey=N'HKEY_LOCAL_MACHINE',@key=N'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLib',@value_name=N'ExtendedProtection',@value=@get_value OUTPUT
  • Only enable required SQL Server services
    • Policy name: dbi_security_l01_server_service_brocker_disabled
    • Condition:is_service_brocker_disabled
    • Facet: @ServiceBrokerEndpointActive
    • Policy name: dbi_security_l01_server_vss_writer
    • Condition:is_service_vss_writer_enabled
    • Query: exec master..xp_instance_regread @rootkey=N'HKEY_LOCAL_MACHINE',@key=N'SOFTWAREMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLib',@value_name=N'ExtendedProtection',@value=@get_value OUTPUT
    • Policy name: dbi_security_l01_server_soap_endpoints_disabled
    • Condition: is_soap_endpoints_disabled
    • Facet: @SoapEndpointsEnabled
  • Install last Service Pack and Security Fix
    • Policy name:dbi_security_l01_server_last_update
    • Condition:is_last_update
    • Query:SELECT SERVERPROPERTY('productversion')

Level 2: Instance

  • Choose Windows Authentication vs mixed mode
    • Policy name: dbi_security_l02_instance_sql_server_login_mode
    • Condition: is_windows_authentication_mode
    • Facet: @LoginMode
  • Use complex passwords for SQL Server logins
    • Policy name: dbi_security_l02_instance_sql_server_password_policy
    • Condition: is_password_policy_enforced
    • Facet: @PasswordPolicyEnforced
    • Policy name: dbi_security_l02_instance_sql_server_password_expiration
    • Condition: is_password_expiration_enabled
    • Facet: @PasswordExpirationEnabled
  • Revoke extended and OLE Automation stored procedures for the public role
    • Policy name: dbi_security_l02_instance_execute_right_extended_store_procedure
    • Condition:is_execute_rights_for_public_for_extended_store_procedure
    • Query: SELECT count(*) FROM sys.database_permissions WHERE OBJECT_NAME(major_ID) IN ('sp_OACreate','sp_OADestroy','sp_OAGetErrorInfo','sp_OAGetProperty','sp_OAMethod', 'sp_OASetProperty','sp_OAStop','sp_sdidebug','xp_availablemedia','xp_cmdshell','xp_deletemail','xp_dirtree','xp_dropwebtask','xp_dsninfo','xp_enumdsn','xp_enumerrorlogs','xp_enumgroups','xp_enumqueuedtasks','xp_eventlog','xp_findnextmsg','xp_fixeddrives','xp_getfiledetails','xp_getnetname','xp_grantlogin','xp_logevent','xp_loginconfig','xp_logininfo','xp_regread','xp_perfend','xp_perfmonitor','xp_perfsample','xp_perfstart','xp_readerrorlog','xp_readmail','xp_revokelogin','xp_runwebtask','xp_schedulersignal','xp_sendmail','xp_servicecontrol','xp_snmp_getstate','xp_snmp_raisetrap','xp_sprintf','xp_sqlinventory','xp_sqlregister''xp_sqltrace','xp_sscanf','xp_startmail','xp_stopmail','xp_subdirs','xp_unc_to_drive','xp_dirtree') AND USER_NAME(grantee_principal_id) LIKE 'PUBLIC'
  • Remove the BuiltinAdministrators Windows Group
    • Policy name: dbi_security_l02_instance_builtin_administrators
    • Condition: is_builtin_administrators_removed
    • Facet: @Name
  • sa account disabled or locked
    • Policy name: dbi_security_l02_instance_sa_locked
    • Condition: is_account_locked
    • Target: is_sa_account
    • Facet: @IsLocked or @IsDisabled
  • Removed orphaned Logins
    • Policy name: dbi_security_l02_instance_orphaned_logins
    • Condition: is_orphaned_logins
    • Target: is_sa_account
    • Query: see the blog here
  • Options & Configurations
    • Policy name: dbi_security_l02_instance_xp_cmdshell_disabled
    • Condition: is_xp_cmdshell_disabled
    • Facet: @XPCmdSellEnabled
    • Policy name: dbi_security_l02_instance_cross_db_ownership_chaining
    • Condition: is_cross_db_ownership_chaining_enabled
    • Facet: @CrossDBOwnershipChainingEnabled
    • Policy name: dbi_security_l02_instance_web_assistant_disabled
    • Condition: is_web_assistant_disabled
    • Facet: @WebAssistantEnabled
    • Policy name: dbi_security_l02_instance_clr_integration
    • Condition: is_clr_integration_enabled
    • Facet: @ClrIntegrationEnabled
    • Policy name: dbi_security_l02_instance_ad_hoc_remote_queries
    • Condition: is_ad_hoc_remote_queries_enabled
    • Facet: @AdHocRemoteQueriesEnabled
    • Policy name: dbi_security_l02_instance_defaut_trace
    • Condition: is_default_trace_enabled
    • Facet: @DefaultTraceEnabled
    • Policy name: dbi_security_l02_instance_auditing_logins
    • Condition: is_auditing_logins_enabled
    • Query: SELECT count(*) FROM sys.server_audit_specification_details sasd JOIN sys.server_audits sa on sa.audit_id=sasd.server_specification_id WHERE sa.name='Audit-Logins' and (sasd.audit_action_id='LGFL' or sasd.audit_action_id='LGSD')
    • Policy name: dbi_security_l02_instance_number_logins_failed
    • Condition: is_number_login_failed_acceptable
    • Query:Exec master.sys.xp_ReadErrorLog, 0,1, N'Login',N'Failed'
    • Policy name: dbi_security_l02_instance_containment
    • Condition: is_containment_enabled
    • Facet:@ContainmentEnabled
  • Choose “Both failed and successful login for audit”
    • Policy name: dbi_security_l02_instance_errorlog_failed_successful_logins
    • Condition: is_failed_successful_logins_enabled
    • Target: is_sa_account
    • Query: exec master.sys.xp_instance_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWAREMicrosoftMSSQLServerMSSQLServer', @value_name='AuditLevel', @value=@AuditLevel output

Level 3: Database

  • Not all databases should be owned by SA or by any other users in sysadmin server role
    • Policy name: dbi_security_l03_database_database_owner_is_service_account
    • Condition: is_database_owner_is_service_account
    • Facet: @Owner
  • Have distinct owners for databases
    • Policy name: dbi_security_l03_database_default_schema_owner
    • Condition: is_default_schema_owner
    • Facet: @Name=@Owner
  • No access to database for the user Guest
    • Policy name: dbi_security_l03_database_guest_permissions
    • Condition: has_no_database_access
    • Targets: is_guest + is_user_or_model
    • Facet: @HasDBAccess
  • Delete orphaned database-users
    • Policy name: dbi_security_l03_database_no_orphan_sql_database_user
    • Condition: is_no_orphan_sql_database_user
    • Query: SELECT count(*) from sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid WHERE b.sid IS NULL AND a.type = 'S' AND a.principal_id > 4 AND DATALENGH(a.sid)
    • Policy name: dbi_security_l03_database_no_orphan_windows_database_user
    • Condition: is_no_orphan_windows_database_user
    • Query: SELECT count(*) FROM sys.database_principals dp LEFT OUTER JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE sp.sid IS NULL AND dp.type In ('U', 'G') AND dp.principal_id > 4
  • Remove database objects granted to public
    • Policy name: dbi_security_l03_database_objects_permissions_granted_to_public
    • Condition: is_database_objects_permissions_granted_to_public
    • Targets: is_guest + is_user_or_model
    • Query: SELECT COUNT(*) FROM sys.database_permissions dp WHERE USER_NAME(dp.grantee_principal_id) = 'public' AND dp.major_id > 0 AND exists (select 1 from sys.all_objects ao where ao.object_id = dp.major_id and ao.is_ms_shipped = 0) AND OBJECT_NAME(dp.major_id) not in ('pbcattbl','pbcatcol','pbcatfmt','pbcatvld','pbcatedt') AND OBJECT_NAME(dp.major_id) not in ('sp_upgraddiagrams','sp_helpdiagrams','sp_helpdiagramdefinition','sp_creatediagram','sp_renamediagram','sp_alterdiagram','sp_dropdiagram','fn_diagramobjects','dt_properties')

Conclusion

Finally, you canfind all these policies in the package SecurityPolicies.zip. You can also find here the associated presentation Security_via_Policie-GUSS-2014.pdf.
The session is on Youtube here for the explanation.
It may be not an exhaustive list. So, you can send me your feedback and suggestions to complete the package. Or you also can buy us a consulting day.:roll:

IN ANY CASES DO NOT FORGET
However people working with databases and protecting data remain always unsung heroes!