Happy Belated 150th Birthday Canada, and the Importance of Compliance, Especially Vulnerability Assessments

Assess your vulnerabilities before a system compromise brings it all down: just like what wasn't done early morning on Zhiggy's Meadow on Blackcomb, May, 2017 (luckily nobody was on the mountain at 4am).

About ten years ago, I was all keen to speak publicly about controlled environments, and the importance of the separation of duties, now a whole cycle of permanent+consulting jobs has passed, it seems that it's time for a good discussion on the importance of Vulnerability Assessments, in a similar fashion to other Configuration management information, which can easily be automatically stored to tables on a regular basis.

By default, we know that SQL Server has locked down a lot of settings to prevent the exploitation of the system, but there are always more improvements to be made from the typical production instance configuration, post installation.  Even years ago, I worked with Microsoft to help the database community better understand what best practices can help protect organisational data assets, but then I had the chance to work with IBM Security Guardium, a pricey, but excellent product for watching over database servers agnosticly.  Guardium was acquired by IBM years ago, with a (slow, needs compression optmisation) backend in MySQL Oracle Enterprise 5.628 (last I worked with it), and intense work with a great group of Israeli developers has improved it greatly. The tool that is included with Guardium I shall focus on, for infrastructure managers to handle risk down to a very granular level, is known as, and allows you to conduct Vulnerability Assessments.

What I really wanted to share are three hardening controls, and what to store for Vulnerability Assessments as required by the Gramm-Leach-Bliley Act in the U.S. with equivalents in other jurisdictions:

1) these are a few extra hardening steps to aim for a 100% pass on (adjust from default slightly) Vulnerability Assessments, that for SQL Server configuration are rather unusual, even for someone who has been obsessed with database security best practices for years:

-- Revoke some unwanted Public role permissions,
-- followed by an explicit Grant to all users that actually require them:
REVOKE EXECUTE on xp_instance_regread to [public]   -- Don't use DENY
REVOKE EXECUTE on xp_regread to [public]
-- yes, Revoke to extended stored proc.s are a pain, b/c it means that each
-- group of users needs: Grant Execute on xp_ to [GroupName]
REVOKE EXECUTE on sp_helptext to [public]
REVOKE SELECT on sys.syscomments to [public]
-- additional from Guardium's scan for Access to General Extended procedures REVOKE EXECUTE on xp_getnetname to [public]
REVOKE EXECUTE on xp_dirtree to [public]
REVOKE EXECUTE on xp_msver to [public]
REVOKE EXECUTE on xp_fixeddrives to [public]
REVOKE EXECUTE on xp_sscanf to [public]

GRANT EXECUTE on xp_instance_regread to [UsernameOrGroupName]
GRANT EXECUTE on xp_regread to [UsernameOrGroupName]
.....

Do not forget for every Revoke, do an explicit grant to all users-groups that need these rights.  Yes, painful, but it can easily be automated by code, so please do not shoot the messenger, as some negligent banking executives have to done to someone for just simply suggesting Vulnerability Assessments be conducted on database systems in the first place (a Gramm-Leach Bliley requirement for compliance in the first place, and a SOX 404b managerial responsibility).

Please note that Microsoft will not recommend touching public permissions, but if you do this right, then only Grant (after the revoke) to those that need to see some of this sensitive information (I seriously don't want it to be easy for someone to read the registry of my server, would you?). Test this out in your environment to ensure all your applications work. 

2)  Here's another obscure, but warranted vulnerability lockdown:
-- Disable Adhoc access, the default OLE DB Provider Permissions:
Use master

--- disable adhoc access for each ole db provider

EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI11', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'ADsDSOObject', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'DTSPackageDSO', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'DTSPackageDSO', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'IBMDADB2.DB2COPY1', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.Jet.OLEDB.4.0', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'MSDAORA', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'MSDAOSP', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'MSDASQL', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'MSDMine', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'MSIDXS', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'MSOLAP', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI10', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'SQLOLEDB', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'SQLReplication.OLEDB', N'DisallowAdHocAccess', 1

--in sql 2014 you will need to do more OLE, and validate your 2016 providers

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DisallowAdHocAccess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DisallowAdHocAccess', 1

GO

NB, there are many other common hardening steps should not be forgotten also: such as hiding the instance, changing the default TCP port, disabling Browser services, reducing SysAdmin membership, disabling Named Pipes (if you don't need FT indexing), disabling OLE DB automation, disable Guest account in Model, avoid the use of xp_cmdshell  - if your situation permits, exploiting flexible server roles, and for more, see this presentation DBSec for the Vigilant  (or follow fellow keeners who speak of CISSP certification crossed over with DBSec.).

3) Finally, here’s the code to create the tables for the system export process from collectors to an import process, by SSIS package, on a centralised security management server (or in the case of database security, I like to call it a DBSec server).

The steps to export from Guardium are as such, within the Guardium GUI system export with the resulting CSV mailed via an audit process, or through winscp /var folder transfer, you'll left with data that can be imported into the following structure, or your respective central security management database (note: columns intentionally padded):

CREATE TABLE [dbo].[GuardiumSecurityAssessment_Import] (

[AssessmentID] int,

[DATASOURCE_NAME] varchar(50),

[DATASOURCE_TYPE] varchar(25),

[DB_NAME] varchar(150),

[VERSION_LEVEL] varchar(15),

[PATCH_LEVEL] varchar(15),

[FULL_VERSION_INFO] varchar(120),

[DESCRIPTION] varchar(150),

[HOST] varchar(60),

[TEST_DESCRIPTION] varchar(150),

[TEST_SCORE] varchar(15),

[SCORE_DESCRIPTION] varchar(50),

[RESULT_TEXT] varchar(max),

[RECOMMENDATION] varchar(max),

[SEVERITY] varchar(12),

[CATEGORY] varchar(10),

[EXECUTION_DATE] datetime,

[ASSESSMENT_DESCRIPTION] varchar(200)

)

Here's a sample of what the export looks like:

Don't forget to keep your configuration management history too. You never know when it’ll become useful one day. Here’s a script for all the other tables which are useful for a system security and configuration management viewpoint, and to keep historically for proof that you can easily stress-free hand over to auditors (no scrambling when they descend upon your office):

-- add insert date [dbaInsertedDate] with getdate() for each table, if you like too, also
--- after original Into statement

USE [ Your DBA Tools or Audit DB]

GO

  Insert into [dbo].dbaSysConfiguration

  ([configuration_id]

           ,[name]

           ,[value]

           ,[minimum]

           ,[maximum]

           ,[value_in_use]

           ,[description]

           ,[is_dynamic]

           ,[is_advanced]

           ,[ServerName]

           ,[InstanceName]

           ,[SP_installed]

           ,[SQLVersion]

           ,[WindowsAuthentificationOnly])

select   [configuration_id]

      ,[name]

      ,[value]

      ,[minimum]

      ,[maximum]

      ,[value_in_use]

      ,[description]

      ,[is_dynamic]

      ,[is_advanced]

      ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ServerName,

         SERVERPROPERTY('InstanceName') as InstanceName,

         SERVERPROPERTY('ProductLevel') as SP_installed,

       SERVERPROPERTY('ProductVersion') as SQLVersion,

       SERVERPROPERTY('IsIntegratedSecurityOnly') as WindowsAuthentificationOnly

              from master.sys.Configurations

-- master sys database principals

insert into dbaSysDatabase_principals ([name]

      ,[principal_id]

      ,[type]

      ,[type_desc]

      ,[default_schema_name]

      ,[create_date]

      ,[modify_date]

      ,[owning_principal_id]

      ,[sid]

      ,[is_fixed_role]

      ,[authentication_type]

      ,[authentication_type_desc]

      ,[default_language_name]

      ,[default_language_lcid])

SELECt [name]

      ,[principal_id]

      ,[type]

      ,[type_desc]

      ,[default_schema_name]

      ,[create_date]

      ,[modify_date]

      ,[owning_principal_id]

      ,[sid]

      ,[is_fixed_role]

      ,[authentication_type]

      ,[authentication_type_desc]

      ,[default_language_name]

      ,[default_language_lcid]

FROM master.sys.database_principals

USE [ Your DBA Tools or Audit DB]

GO --truncate table dbaSysServerPermissions

Insert into dbaSysServer_Permissions

       ([class]

      ,[class_desc]

      ,[major_id]

      ,[minor_id]

      ,[grantee_principal_id]

      ,[grantor_principal_id]

      ,[type]

      ,[permission_name]

      ,[state]

      ,[state_desc])

SELECT [class]

      ,[class_desc]

      ,[major_id]

      ,[minor_id]

      ,[grantee_principal_id]

      ,[grantor_principal_id]

      ,[type]

      ,[permission_name]

      ,[state]

      ,[state_desc]

FROM master.sys.server_permissions

insert into dbaSysDatabase_Permissions

              ([class]

      ,[class_desc]

      ,[major_id]

      ,[minor_id]

      ,[grantee_principal_id]

      ,[grantor_principal_id]

      ,[type]

      ,[permission_name]

      ,[state]

      ,[state_desc])

Select [class]

      ,[class_desc]

      ,[major_id]

      ,[minor_id]

      ,[grantee_principal_id]

      ,[grantor_principal_id]

      ,[type]

      ,[permission_name]

      ,[state]

      ,[state_desc]

FROM master.sys.database_permissions

insert into dbaSysServer_Principals

       ([name]

      ,[principal_id]

      ,[sid]

      ,[type]

      ,[type_desc]

      ,[is_disabled]

      ,[create_date]

      ,[modify_date]

      ,[default_database_name]

      ,[default_language_name]

      ,[credential_id]

      ,[owning_principal_id]

      ,[is_fixed_role])

select [name]

      ,[principal_id]

      ,[sid]

      ,[type]

      ,[type_desc]

      ,[is_disabled]

      ,[create_date]

      ,[modify_date]

      ,[default_database_name]

      ,[default_language_name]

      ,[credential_id]

      ,[owning_principal_id]

      ,[is_fixed_role]

FROM master.sys.server_principals

insert into dbaSysUsers

       ([uid]

      ,[status]

      ,[name]

      ,[sid]

      ,[roles]

      ,[createdate]

      ,[updatedate]

      ,[altuid]

      ,[password]

      ,[gid]

      ,[environ]

      ,[hasdbaccess]

      ,[islogin]

      ,[isntname]

      ,[isntgroup]

      ,[isntuser]

      ,[issqluser]

      ,[isaliased]

      ,[issqlrole]

      ,[isapprole])

select [uid]

      ,[status]

      ,[name]

      ,[sid]

      ,[roles]

      ,[createdate]

      ,[updatedate]

      ,[altuid]

      ,[password]

      ,[gid]

      ,[environ]

      ,[hasdbaccess]

      ,[islogin]

      ,[isntname]

      ,[isntgroup]

      ,[isntuser]

      ,[issqluser]

      ,[isaliased]

      ,[issqlrole]

      ,[isapprole]

FROM master.sys.sysusers

insert into dbaSysDM_Server_Services

       ([servicename]

      ,[startup_type]

      ,[startup_type_desc]

      ,[status]

      ,[status_desc]

      ,[process_id]

      ,[last_startup_time]

      ,[service_account]

      ,[filename]

      ,[is_clustered]

      ,[cluster_nodename])

select  [servicename]

      ,[startup_type]

      ,[startup_type_desc]

      ,[status]

      ,[status_desc]

      ,[process_id]

      ,[last_startup_time]

      ,[service_account]

      ,[filename]

      ,[is_clustered]

      ,[cluster_nodename]

from master.sys.dm_server_services

insert into dbaSysDM_Exec_Connections

       ([session_id]

      ,[most_recent_session_id]

      ,[connect_time]

      ,[net_transport]

      ,[protocol_type]

      ,[protocol_version]

      ,[endpoint_id]

      ,[encrypt_option]

      ,[auth_scheme]

      ,[node_affinity]

      ,[num_reads]

      ,[num_writes]

      ,[last_read]

      ,[last_write]

      ,[net_packet_size]

      ,[client_net_address]

      ,[client_tcp_port]

      ,[local_net_address]

      ,[local_tcp_port]

      ,[connection_id]

      ,[parent_connection_id]

      ,[most_recent_sql_handle])

select [session_id]

      ,[most_recent_session_id]

      ,[connect_time]

      ,[net_transport]

      ,[protocol_type]

      ,[protocol_version]

      ,[endpoint_id]

      ,[encrypt_option]

      ,[auth_scheme]

      ,[node_affinity]

      ,[num_reads]

      ,[num_writes]

      ,[last_read]

      ,[last_write]

      ,[net_packet_size]

      ,[client_net_address]

      ,[client_tcp_port]

      ,[local_net_address]

      ,[local_tcp_port]

      ,[connection_id]

      ,[parent_connection_id]

      ,[most_recent_sql_handle]

from master.sys.dm_exec_connections

Happy Auditing, Hardening, Assessing, your database instances.