如何默认加固SQL Server 2016实例级配置？含public组存储过程访问限制

Great question! Securing a SQL Server 2016 instance involves a mix of locking down default configurations and tightening permissions on high-risk system objects. Let's break this into two clear parts: instance-level hardening steps, and restricting public access to those risky stored procedures you listed.

These are the foundational steps to lock down your SQL Server 2016 instance right out of the box:

• Disable unnecessary services and features: SQL Server enables some services by default that you may not need—like SQL Server Browser (if you don’t use named instances for remote access) or SQL Server Agent (if you don’t schedule jobs). Turn these off via SQL Server Configuration Manager to reduce your attack surface. Also, confirm xp_cmdshell is disabled (it’s default, but verify with):

  sp_configure 'xp_cmdshell', 0;
  RECONFIGURE;
  

• Enforce Windows Authentication Mode: Prioritize Windows Authentication over SQL Server Authentication whenever possible. If you must use SQL logins, enable password policies, password expiration, and account locking rules via the instance’s Security properties to enforce strong credential practices.
• Secure the SA account: Rename the SA account to something non-obvious (to avoid brute-force targeting) and set an extremely strong password. If you don’t use SA at all, disable it entirely:

  -- Rename SA
  ALTER LOGIN sa WITH NAME = [YourCustomSAUserName];
  -- Disable SA
  ALTER LOGIN sa DISABLE;
  

• Restrict network access with firewall rules: Create Windows Firewall inbound rules to only allow trusted IP addresses to connect to SQL Server’s default port (1433) or your custom port. This blocks unauthorized network attempts to reach your instance.
• Enable server-level auditing: Set up SQL Server Audit to track critical events like failed login attempts, permission changes, and data access. Create a server audit specification that includes events like FAILED_LOGIN_GROUP and SERVER_PERMISSION_CHANGE_GROUP to monitor for suspicious activity.
• Keep SQL Server updated: Install the latest cumulative updates (CUs) and service packs (SPs) for SQL Server 2016. Microsoft regularly patches security vulnerabilities, so staying current is non-negotiable.
• Minimize sysadmin role membership: Only grant sysadmin privileges to absolutely necessary personnel. Avoid overassigning high-level server roles like serveradmin or securityadmin to non-admin users.
• Disable cross-database ownership chaining: This feature can lead to unintended permission escalation, so confirm it’s disabled (default setting) with:

  sp_configure 'cross db ownership chaining', 0;
  RECONFIGURE;
  

• Enable Transparent Data Encryption (TDE): If you need to protect data at rest, enable TDE on your databases. This encrypts data files, preventing unauthorized access if physical media is compromised.
• Configure login auditing: In the instance’s Security properties, set login auditing to "Both failed and successful logins". This logs all login attempts, helping you detect brute-force attacks or unauthorized access attempts early.

The public role has default access to many system objects, including the risky extended stored procedures (xp_reg*) and OLE automation procedures (sp_OA*) you listed. Here’s how to revoke that access:

1. Verify current permissions: First, check what permissions public has on these objects by running this in the master database:

   USE master;
   GO
   SELECT 
       dp.name AS principal_name,
       o.name AS object_name,
       dp.state_desc,
       dp.permission_name
   FROM sys.database_permissions dp
   JOIN sys.objects o ON dp.major_id = o.object_id
   WHERE dp.grantee_principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'public')
     AND o.name IN (
         'xp_regaddmultistring', 'xp_regdeletekey', 'xp_regdeletevalue', 
         'xp_regenumvalues', 'xp_regread', 'xp_regremovemultistring', 
         'xp_regwrite', 'xp_regenumkeys', 'sp_OACreate', 'sp_OADestroy', 
         'sp_OAGetErrorInfo', 'sp_OAGetProperty', 'sp_OAMethod', 
         'sp_OASetProperty', 'sp_OAStop'
     );
   

2. Revoke EXECUTE permissions in bulk: Instead of revoking permissions one by one, use this script to automate the process for all the listed procedures:

   USE master;
   GO
   DECLARE @ProcName NVARCHAR(128);
   DECLARE proc_cursor CURSOR FOR
   SELECT name 
   FROM sys.objects 
   WHERE name IN (
       'xp_regaddmultistring', 'xp_regdeletekey', 'xp_regdeletevalue', 
       'xp_regenumvalues', 'xp_regread', 'xp_regremovemultistring', 
       'xp_regwrite', 'xp_regenumkeys', 'sp_OACreate', 'sp_OADestroy', 
       'sp_OAGetErrorInfo', 'sp_OAGetProperty', 'sp_OAMethod', 
       'sp_OASetProperty', 'sp_OAStop'
   )
   AND (type = 'X' OR type = 'P'); -- X = extended stored proc, P = stored proc
   
   OPEN proc_cursor;
   FETCH NEXT FROM proc_cursor INTO @ProcName;
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
       EXEC('REVOKE EXECUTE ON ' + QUOTENAME(@ProcName) + ' FROM public;');
       PRINT 'Revoked EXECUTE permission on ' + @ProcName + ' from public.';
       FETCH NEXT FROM proc_cursor INTO @ProcName;
   END
   
   CLOSE proc_cursor;
   DEALLOCATE proc_cursor;
   GO
   

3. Validate the changes: Re-run the permission check query from step 1 to confirm public no longer has EXECUTE access to these procedures.

4. Grant minimal access if needed: If any applications or users require access to these procedures, grant EXECUTE permissions only to specific logins/users (not public) following the principle of least privilege:

   GRANT EXECUTE ON xp_regread TO [YourTrustedAppLogin];
   GO
   

内容的提问来源于stack exchange，提问作者Adam