SQL Server Protocols

Job details

Name:	SQL Server Protocols
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Check what SQL Server protocols are in use.
Long description:	Check what SQL Server protocols are in use. By default, TCP/IP and Shared Memory protocols are enabled.
Version:	1.21
Default schedule:	27 1 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when other SQL Server protocols are in use than listed by parameter “permitted protocols”.
permitted protocols	Shared memory, TCP	List of permitted SQL Server Protocols.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: To monitor and report on the SQL Server protocols currently in use, ensuring only approved protocols are active and logging historical data.
Why: This job is crucial for maintaining the security and compliance requirements of SQL Server environments by enforcing permitted communication protocols and providing audit capabilities through historical tracking.
Manual checking: Administrators can manually check the current in-use SQL Server protocols by executing the following SQL command:

SELECT net_transport FROM sys.dm_exec_connections GROUP BY net_transport;

Job Details

Default Schedule

The job runs monthly, as indicated by the schedule parameter set to “27 1 1 *”.

Acknowledgements

The job supports acknowledgements, implying that it can handle manual interventions or overrides by users.

Dependencies

Dependency Description	Object	Cleanup On Fail
Main procedure	dbw_sql_server_protocols_proc	True
Current status table	dbw_sql_server_protocols_info	True
History table	dbw_sql_server_protocols_histr	True

Implementation
This job includes SQL code for:

Creating necessary tables for logging current protocols and historical data.
A stored procedure to:
- Fetch the allowed protocols and current date.
- Insert aggregated historic protocol usage into a history table.
- Identify and flag unapproved protocols in use.
- Manipulate security and compliance relevant data.
Broadcasting detailed execution status and any exceptions encountered.

Reporting Mechanism

The job report compiles data on the SQL Server protocols in use with specifics on process counts and the respective dates. Historical protocol usage data is also visualized graphically.

Data Presentation
The report display follows two major presentations:

Current Protocols in Use: Lists types of protocols currently in operation including the number of processes each protocol is handling along with the respective date.
History Statistics: Shows historical data on the count of checks performed for the protocols over time to depict trends and ensure compliance over an extended period.

Tables and Queries for Report Generation

Presentation Title	SQL Query
Current Protocols in Use	bc. SELECT * FROM dbw_sql_server_protocols_info ORDER BY histr_date ASC
History Statistics	bc. SELECT protocol_name, check_count, histr_date FROM dbw_sql_server_protocols_histr ORDER BY histr_date ASC

Each presentation is designed to aid in confirming that the SQL Server instance remains within the defined compliance and showcases the protocols activity trend which can be vital in audit and compliance scenarios.