Remote access

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and control the status of remote access to stored procedures across servers in a database environment.
• Why: This job is important to ensure security and compliance within the database environment. By keeping track of which stored procedures can execute remotely, administrators can prevent potential malicious activities or misconfigurations that could compromise the database’s integrity and security.
• Manual checking: You can manually check the current configuration of remote access in the database with the following SQL command:

Select * FROM sys.configurations WHERE name = 'remote access'

Detailed Description

The job primarily checks whether local stored procedures can execute on remote servers or if remote stored procedures can be executed on the local server. It employs a series of SQL implementations to efficiently monitor any changes and status of the remote access configuration. If remote access is enabled and the configuration is set to disable it, the job will automatically adjust the settings to turn off remote access, enforcing an additional layer of security. This automatic reconfiguration only happens based on the job parameters set during the job setup.

Dependencies

• “dbw_remote_access_proc” – Main procedure that handles the checking of remote access status.
• “dbw_remote_access_status_info” – Table that logs current status information.
• “dbw_remote_access_status_histr” – Table for storing historical information about the remote access status.

Implementation Steps

1. Create necessary tables to log current and historical remote access states.
2. Check the current state of ‘remote access’ and update the ‘dbw_remote_access_status_info’ table.
3. If the remote access setting is enabled and needs to be disabled, execute ‘sp_configure’ to change the configuration.
4. Aggregate old statistics into ‘dbw_remote_access_status_histr’ table and clean up the data based on the history threshold parameter.
5. Update compliance-related information in the ‘dbw_security_framework_info’ table if exists.

Scheduled Jobs and Reporting
|| Frequency || Description ||
| Once every hour | The job is designed to run at this frequency to ensure timely updates and quick response to any unauthorized changes in remote access configuration. |
| Once every day at 1:23 AM | Default schedule for aggregating remote access status into historical data. |

Security & Compliance Integration

The job updates and utilizes the ‘dbw_security_framework_info’ table for compliance reporting purposes, demonstrating how each execution aligns with established security guidelines and configurations pertinent to remote access management.

Upgrades

• Version: From 1.1 to 1.2
• Changes include enhancements to the stored procedure ‘dbw_remote_access_proc’ to more efficiently process historical data and manage configurations. This ensures optimal performance and adaptability to both current and future SQL Server environments.

System Requirements

The job is applicable for instances running SQL Server (version greater than 2005) and Azure SQL Managed Instances where the instance has necessary privileges for execution. The job selectively targets environments where it can operate without elevated privileges, focusing on those configurations most likely to benefit from its remote access monitoring capabilities.