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.

Rate

Share

Share

Rate