Thanks again to Raymond Barley, and Jeremy Kadlec, leaders of the Baltimore SQL Server User Group for sliding me in so quickly!
The Database Hive for SQL Server DBAs by Hugo Shebbeare, MVP (2010)
sql server optimisation, sql server database management, sql discussions, help, clusters, backups, expansion, usefull tools, mcitp certification, server management, meilleures pratiques sql server, best practices.disaster recovery, deployment management, mirroring best practices and high availability, database security best practices
Monday, November 27, 2017
Vigilant SQL DBA Vulnerability Management & 1st Look at Azure Vulnerability Assessments
Just a quick note to share last Wednesday night's presentation on how to manage SQL Server vulnerabilities, plus a first look at Azure's Vulnerability Assessments. I shall follow up with a comparison of what a scan in Azure does versus traditional STIG/CIS/NIST checks.
Thanks again to Raymond Barley, and Jeremy Kadlec, leaders of the Baltimore SQL Server User Group for sliding me in so quickly!
Thanks again to Raymond Barley, and Jeremy Kadlec, leaders of the Baltimore SQL Server User Group for sliding me in so quickly!
Wednesday, July 26, 2017
Happy Belated 150th Birthday Canada, and the Importance of Compliance, Especially Vulnerability Assessments
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]
.....
-- 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.
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, 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):
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)
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
--- 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.
Subscribe to:
Posts (Atom)