保护SQL Server外围区域

In a previous article, we have discussed about the top 10 security factors that you should take into consideration in order to secure your SQL Server instances. In this article I will try to deep dive into one of those factors, that is SQL Server Surface Area.

在上一篇文章中 ，我们讨论了为了保护SQL Server实例而应考虑的十大安全因素。 在本文中，我将尝试深入探究其中一个因素，即SQL Server Surface Area 。

First, we need to define the terms Surface Area and Surface Area Reduction. SQL Server Surface Area is a term that is used for characterizing all those Database Engine features and components of SQL Server which when enabled, can provide additional functionality. This additional functionality however, comes with a security cost. This cost is the increase of the potential targets for malicious attacks that could take place, in order for the attacker to gain unauthorized access to a SQL Server instance and its databases. Disabling or at least “properly” setting up these features and components is called Surface Area Reduction.

首先，我们需要定义术语表面积和表面积减少 。 SQL Server Surface Area是一个术语，用于表征SQL Server的所有那些数据库引擎功能和组件，这些功能和组件在启用后可以提供附加功能。 但是，此附加功能会带来安全成本。 代价是增加了可能发生的恶意攻击的潜在目标，以使攻击者获得对SQL Server实例及其数据库的未经授权的访问。 禁用或至少“适当”设置这些功能和组件称为“ 减少表面积” 。

The major surface area features in SQL Server that have a direct or indirect effect to your instance’s security are:

对您的实例的安全性具有直接或间接影响SQL Server的主要外围功能是：

• Ad Hoc Distributed Queries
  临时分布式查询
• CLR Enabled
  启用CLR
• Cross DB Ownership Training
  跨数据库所有权培训
• Database Mail XPs
  数据库邮件XP
• Ole Automation Procedures
  OLE自动化程序
• Remote Access
  远程访问
• Remote Admin Connections
  远程管理员连接
• Scan for Startup Procs
  扫描启动程序
• SQL Mail XPs
  SQL Mail XPs
• Trustworthy
  值得信赖
• xp_cmdshell
  xp_cmdshell

Another point that could be considered as part of SQL Server’s Surface Area is the “sa” built-in SysAdmin login. SQL Server lets you use Mixed Mode authentication, that is Windows Authentication along with SQL Server authentication. Even though it is recommended to use Windows authentication only, in the case where you are using Mixed Mode, the built-in SysAdmin user (sa) gets automatically created (you set the password during SQL Server’s setup).

可以认为是SQL Server外围应用程序的一部分的另一点是“ sa”内置的SysAdmin登录名。 SQL Server允许您使用混合模式身份验证，即Windows身份验证和SQL Server身份验证。 即使建议仅使用Windows身份验证，在使用混合模式的情况下，也会自动创建内置的SysAdmin用户（sa）（在SQL Server的安装过程中设置密码）。

临时分布式查询 (Ad Hoc Distributed Queries)

With “Ad Hoc Distributed Queries” server configuration option, you can allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE functions. This option is disabled by default.

使用“临时分布式查询 ”服务器配置选项，您可以使用OPENROWSET和OPENDATASOURCE函数允许临时分布式查询。 默认情况下禁用此选项。

The recommendation is if you do not really need this option, do not enable it. In case you need to run a distributed query, there are other ways/workarounds to perform it. For example, you could use an SSIS package and other similar solutions.

建议如果您确实不需要此选项，请不要启用它。 如果您需要运行分布式查询，则可以使用其他方法/替代方法来执行它。 例如，您可以使用SSIS包和其他类似的解决方案。

启用CLR (CLR Enabled)

With “CLR Enabled” server configuration option, you specify whether user assemblies can be ran by SQL Server or not. This option is disabled by default.

使用“ 启用CLR ”服务器配置选项，可以指定是否可以通过SQL Server运行用户程序集。 默认情况下禁用此选项。

Creating and running .NET assemblies in SQL Server allows to broadly extend SQL Server’s functionality. However, if not managed properly, it can introduce additional security risks. For example, if this option is really required by your operations and its use is allowed by your Organization’s security policies, you should only allow CLR assemblies with “SAFE” permissions and not with “EXTERNAL_ACCESS” or “UNSAFE_ACCESS”. For more info, please check this MSDN article.

在SQL Server中创建和运行.NET程序集可以广泛扩展SQL Server的功能。 但是，如果管理不当，则会带来其他安全风险。 例如，如果您的操作确实需要此选项，并且组织的安全策略允许使用此选项，则应仅允许具有“ SAFE ”权限的CLR程序集，而不应允许具有“ EXTERNAL_ACCESS”或“ UNSAFE_ACCESS”权限的CLR程序集。 有关更多信息，请检查此MSDN文章 。

跨数据库所有权链接 (Cross DB Ownership Chaining)

With “cross db ownership chaining” server configuration option, you can either enable or disable cross db ownership for all databases in a SQL Server instance. A simple example of cross db ownership chaining, is having a view or stored procedure in Database A referencing one or more tables in Database B. This could potentially expose data outside the boundaries of a database. That’s why cross db ownership chaining server configuration option is disabled by default. In the case where you really need to use cross-database ownership chaining, then you can consider setting it up only for the individual databases that exchange information between them instead for all databases in the instance. For more info please refer to this MSDN article.

使用“ 跨数据库所有权链接 ”服务器配置选项，您可以为SQL Server实例中的所有数据库启用或禁用跨数据库所有权。 跨数据库所有权链接的一个简单示例是，在数据库A中具有一个视图或存储过程，该视图或存储过程引用了数据库B中的一个或多个表。这可能会暴露数据库边界之外的数据。 这就是为什么默认情况下禁用跨数据库所有权链接服务器配置选项的原因。 如果您确实需要使用跨数据库所有权链，则可以考虑仅为在它们之间交换信息的单个数据库设置它，而不是为实例中的所有数据库设置它。 有关更多信息，请参阅此MSDN文章 。

数据库邮件XP (Database Mail XPs)

With “Database Mail XPs” server configuration option, you can enable or disable Database Mail. This option is disabled by default. This option, when enabled, allows the user to send mails via SQL Server’s database engine. This creates the risk of having data sent outside to a remote host via mail.

使用“ 数据库邮件XPs ”服务器配置选项，您可以启用或禁用数据库邮件 。 默认情况下禁用此选项。 启用此选项后，允许用户通过SQL Server的数据库引擎发送邮件。 这带来了通过邮件将数据发送到外部主机的风险。

In case you need to use Database Mail’s functionality, you could consider other, more secure, alternatives such secure ftp, enterprise monitoring software that among other can send mail notifications, and other similar approaches.

如果您需要使用数据库邮件的功能，则可以考虑使用其他更安全的替代方法，例如安全ftp，可以发送邮件通知的企业监视软件以及其他类似方法。

OLE自动化程序 (Ole Automation Procedures)

With “Ole Automation Procedures” server configuration option, you can specify whether OLE Automation objects can be instantiated within Transact-SQL batches or not. If this option is enabled, it essentially enables the user to execute external functions and this could be a security risk. Examples of OLE Automation object are objects that expose the IDispatch interface. This feature is disabled by default.

使用“ Ole Automation Procedures ”服务器配置选项，您可以指定是否可以在Transact-SQL批处理中实例化OLE Automation对象 。 如果启用此选项，则实际上使用户能够执行外部功能，这可能会带来安全风险。 OLE Automation对象的示例是公开IDispatch接口的对象。 默认情况下禁用此功能。

As with other surface area components/features if this feature is not absolutely required and allowed by your Organization’s security policies, you should not enable it.

与其他外围组件/功能一样，如果组织的安全策略并非绝对要求并允许此功能，则不应启用它。

远程访问 (Remote Access)

With “remote access” server configuration option, when enabled, you can execute stored procedures from local or remote servers on which instances of SQL Server are running. This functionality can be abused in order to launch Denial of Service (DoS) attacks on remote servers by sending huge workload processing requests. Furthermore, note that this feature is deprecated and will be removed in the next version of SQL Server. To this end, it is not recommended to use it in new development work. Also, if it is not required by any operation, then you should consider disabling it (if for example your SQL Server instance does not interact with any other SQL Server instance).

启用“ 远程访问 ”服务器配置选项后，您可以从运行SQL Server实例的本地或远程服务器执行存储过程。 可以滥用此功能，以通过发送大量工作负载处理请求在远程服务器上发起拒绝服务（DoS）攻击。 此外，请注意，此功能已被弃用，并将在下一版本SQL Server中删除。 为此，不建议在新的开发工作中使用它。 另外，如果任何操作都不需要它，则应考虑禁用它（例如，如果您SQL Server实例不与任何其他SQL Server实例交互）。

远程管理员连接 (Remote Admin Connections)

With “remote admin connections” server configuration option, you can enable client applications on remote computers to use the Dedicated Administrator Connection (DAC) to the SQL Server instance. The DAC can be used in order to perform troubleshooting and other diagnostic procedures on a SQL Server instance, even in the case where the instance is not in a stable state.

使用“ 远程管理员连接 ”服务器配置选项，可以使远程计算机上的客户端应用程序能够使用到SQL Server实例的专用管理员连接 （DAC）。 即使在实例处于不稳定状态的情况下，也可以使用DAC来对SQL Server实例执行故障排除和其他诊断过程。

There are different opinions regarding whether this feature should be enabled for all SQL Server instances or not. The basic recommendation is that it should be enabled for clustered SQL Server instances. Note that this feature, is disabled by default.

对于是否应为所有SQL Server实例启用此功能，存在不同的意见。 基本建议是应为群集SQL Server实例启用它。 请注意，默认情况下禁用此功能。

扫描启动程序 (Scan for Startup Procs)

With “scan for startup procs” server configuration option, you can enable SQL Server to scan for automatic execution of stored procedures at SQL Server startup time. This option is disabled by default. If enabled, it creates the risk of having potentially malicious code be executed during SQL Server’s startup.

使用“ 扫描启动过程 ”服务器配置选项，可以使SQL Server在SQL Server启动时扫描存储过程的自动执行。 默认情况下禁用此选项。 如果启用，则存在在SQL Server启动期间执行潜在恶意代码的风险。

You should avoid using this feature for security reasons. If, however, you need this or similar functionality, you should consider implementing it in a more controlled manner.

出于安全原因，应避免使用此功能。 但是，如果需要此功能或类似功能，则应考虑以更可控的方式实施它。

SQL Mail XPs (SQL Mail XPs)

“SQL Mail XPs” was the predecessor of Database Mail XPs. SQL Mails XPs feature was deprecated and removed in SQL Server 2012 and later. If you are using SQL Server 2008 R2 or earlier, then like in the case of Database Mail XPs, this feature, when enabled (it is disabled by default), creates the risk of having data sent outside to a remote host via mail.

“ SQL Mail XPs ”是Database Mail XPs的前身。 SQL Mails XPs功能已弃用，并且在SQL Server 2012及更高版本中已删除。 如果您使用的是SQL Server 2008 R2或更早版本，则与“数据库邮件XP”的情况一样，启用此功能（默认情况下处于禁用状态）会带来将数据通过邮件发送到远程主机的风险。

If you need to have this or similar functionality, you could consider other, more secure alternatives (i.e. secure ftp, enterprise notification software, etc.).

如果您需要此功能或类似功能，则可以考虑其他更安全的选择（例如安全ftp，企业通知软件等）。

值得信赖 (Trustworthy)

“TRUSTWORTHY“ is not a SQL Server surface area feature but a database property. A very important one. This property is turned off by default. If turned on for a database, it tells SQL Server to fully trust the specific database and its contents. This creates the risk of having malicious code (i.e. in the form of CLR assemblies with EXTERNAL_ACCESS or UNSAFE permission settings) contained in a database and that could be executed and thus affect the entire SQL Server instance. It is recommended not to turn on this database property for security reasons. If for any reason you consider turning TRUSTWORTHY on (even though you should avoid that), there is a Support KB article that discusses certain guidelines for using the TRUSTWORTHY database setting in SQL Server.

“ TRUSTWORTHY ”不是SQL Server 外围功能，而是数据库属性。 一个非常重要的。 默认情况下，此属性是关闭的。 如果为数据库打开，它将告诉SQL Server完全信任特定数据库及其内容。 这带来了将恶意代码（即具有EXTERNAL_ACCESS或UNSAFE权限设置的CLR程序集形式）包含在数据库中并且可能被执行并因此影响整个SQL Server实例的风险。 出于安全原因，建议不要打开此数据库属性。 如果出于任何原因考虑要启用TRUSTWORTHY（即使您应该避免这样做），也有一篇Support KB 文章讨论了在SQL Server中使用TRUSTWORTHY数据库设置的某些准则。

xp_cmdshell (xp_cmdshell)

With the “xp_cmdshell” server configuration option, you can control whether members of the SysAdmin fixed server role can execute the extended stored procedure xp_cmdshell and thus interact with the Operating System onto which SQL Server instance is installed.

使用“ xp_cmdshell”服务器配置选项，您可以控制SysAdmin固定服务器角色的成员是否可以执行扩展存储过程xp_cmdshell ，从而与安装SQL Server实例的操作系统进行交互。

If you enable this feature, along with the functionality that is offered, you also create the risk of having someone that maliciously gained SysAdmin access on your SQL Server instance, to also gain access to the Operating System of the database server and thus be able to execute OS commands. So, unless it is required and allowed by your Organization’s security policies, you should not enable this feature.

如果启用此功能以及所提供的功能，则还存在使他人恶意获取您SQL Server实例上的SysAdmin访问权限的风险，从而也可以访问数据库服务器的操作系统，从而能够执行操作系统命令。 因此，除非组织的安全策略要求并允许，否则不应启用此功能。

sa用户 (sa User)

Regarding “sa” built-in SysAdmin user (in case you are using “Mixed Mode” as Authentication Mode), after you ensure that there are other authorized persons that have SysAdmin access on the SQL Server instance and after you make sure that if you remove or rename “sa” will not create any problem, then you should consider disable or at least renaming the “sa” login.

对于“ sa”内置SysAdmin用户（如果您使用“混合模式”作为身份验证模式），请确保在SQL Server实例上还有其他具有SysAdmin访问权限的授权人之后，并确保删除或重命名“ sa”不会造成任何问题，那么您应该考虑禁用或至少重命名“ sa”登录名。

使用DBA Security Advisor保护SQL Server的安全 (Securing SQL Server with DBA Security Advisor)

The surface area of SQL Server is just a part of what needs to be secured towards securing your entire SQL Server instances. There are also many other factors that you need to take into consideration such as: authentication, authorization, auditing, password policies, and much more. Also, the SQL Server hardening process must be repetitive and not just a one-time process. Furthermore, the existence of multiple SQL Server instances within Organizations makes the whole hardening project more complex.

SQL Server的外围区域只是确保整个SQL Server实例安全所需的一部分。 您还需要考虑许多其他因素，例如：身份验证，授权，审核，密码策略等等。 此外，SQL Server强化过程必须是重复的，而不仅仅是一次性过程。 此外，组织内多个SQL Server实例的存在使整个强化项目变得更加复杂。

Having all the above in mind and within the context of my initiative SQLArtBits, I have developed DBA Security Advisor, an enterprise tool which assesses single or multiple SQL Server instances for vulnerabilities using a rich set of security checks. After assessing the SQL Server instances, DBA Security Advisor explains what the detected risks are and provides useful recommendations as well as remediation scripts and methods.

考虑到以上所有内容，并在我的主动SQLArtBits上下文中，我开发了DBA Security Advisor ，这是一种企业工具，它使用一组丰富的安全检查来评估单个或多个SQL Server实例的漏洞。 在评估SQL Server实例之后，DBA Security Advisor解释检测到的风险是什么，并提供有用的建议以及补救脚本和方法。

The main features of DBA Security Advisor (Enterprise Edition) include:

DBA Security Advisor（企业版）的主要功能包括：

• • Server-Level Accesses
    服务器级访问
  • Surface Area
    表面积
  • Authentication and Authorization
    认证与授权
  • Auditing
    稽核
  • Password Policies
    密码政策
  • Miscellaneous
    杂
  • Advanced
    高级
• Scan single and multiple SQL Server instances
  扫描单个和多个SQL Server实例
• Generate security reports
  生成安全报告
• Provide recommendations for the detected security risks
  为检测到的安全风险提供建议
• Provide remediation scripts or methods for the detected security risks
  提供检测到的安全风险的补救脚本或方法
• Rich set of display options
  丰富的显示选项
• Rich set of export options
  丰富的出口选择
• Report history
  报告历史
• Information about connected instances
  有关连接实例的信息
• Program customizations
  程序定制
• Program options
  程序选项

You can download the datasheet for more information on the available features.

您可以下载数据表以获取有关可用功能的更多信息。

Let’s see a simple example of using DBA Security Advisor. For this example, consider a scenario which features two SQL Server instances named “SQL2K14” and “SQL2K16”. As the Enterprise Edition of DBA Security Advisor allows scanning multiple SQL Server instances at the same time, let’s connect on both instances:

让我们看一个使用DBA Security Advisor的简单示例。 对于此示例，请考虑具有两个名为“ SQL2K14”和“ SQL2K16”SQL Server实例的方案。 由于DBA Security Advisor的企业版允许同时扫描多个SQL Server实例，因此让我们在两个实例上进行连接：

After connecting the SQL Server instance(s), DBA Security Advisor provides some basic information about the connected instances. In case you are looking for more information, you can use the “Connected Instance(s) Information” function.

连接SQL Server实例后，DBA Security Advisor提供有关已连接实例的一些基本信息。 如果您需要更多信息，可以使用“连接实例信息”功能。

Now let’s choose from the list of security checks which checks we want to run against our SQL Server instances:

现在，从安全检查列表中选择我们要针对我们SQL Server实例运行的检查：

For this example, we are just going to run the below two “Surface Area” security checks:

对于此示例，我们将运行以下两个“ Surface Area”安全检查：

• Cross DB Ownership Chaining
  跨数据库所有权链接
• Ole Automation Procedures
  OLE自动化程序

After running the above security checks we get the below report:

运行上述安全检查后，我们将获得以下报告：

As you can see in the above screenshot (Figure 5), in the generated report’s page, in “Display Options”, there is a link labeled “Suggest Remediation Scripts”. If you click that link, as the name implies, you will get remediation scripts or methods for the detected security risks. Let’s conclude this example with Figure 6, which shows the remediation script for the detected security risk along with the recommendation:

如您在上面的屏幕快照（图5）中所看到的，在生成的报告页面的“显示选项”中，有一个标记为“建议的补救脚本”的链接。 如果单击该链接，顾名思义，您将获得针对检测到的安全风险的补救脚本或方法。 让我们用图6结束该示例，该图显示了针对检测到的安全风险的补救脚本以及建议：

No you have all the information you need in order to review the detected security risks, the recommendations, as well as the remediation scripts or methods, and finally decide how you want to handle the findings of the assessment.

不，您没有您需要的所有信息，以查看检测到的安全风险，建议以及补救脚本或方法，并最终决定如何处理评估结果。

As you can see from the above example, DBA Security Advisor has many interesting features that can help you secure your SQL Server instances much easier instead of manually performing this important process. With a rich set of security checks, a powerful recommendation engine, and support for assessing multiple SQL Server instances, it is an interesting solution when it comes to securing your SQL Server instances.

从上面的示例可以看出，DBA Security Advisor具有许多有趣的功能，可以帮助您更轻松地保护SQL Server实例的安全，而不必手动执行此重要过程。 凭借丰富的安全检查集，强大的推荐引擎以及对评估多个SQL Server实例的支持，当涉及到保护SQL Server实例时，这是一个有趣的解决方案。

Let’s conclude this discussion by mentioning that, even though different security assessments and tools detect certain items as potential security risks, there are cases where your business operations and essentially the underlying applications might require some of those items/features to be enabled. In such cases, you must make sure that you take all the necessary security precautions when you enable such a feature. Similarly, whenever you read security assessment reports and plan your actions, if a feature marked as security risk is required by an application, you must make sure that you will proceed, if possible, to the necessary application changes in cooperation with the application’s developers, and certify that the application will be able to operate properly prior to disabling the specific feature/item which have been identified as a security risk.

让我们在讨论结束时提到，尽管不同的安全评估和工具将某些项目检测为潜在的安全风险，但在某些情况下，您的业务运营以及本质上是基础应用程序可能需要启用其中的某些项目/功能。 在这种情况下，启用此功能时，必须确保采取所有必要的安全预防措施。 同样，每当您阅读安全评估报告并计划操作时，如果应用程序需要标记为安全风险的功能，则必须确保在可能的情况下与应用程序开发人员合作进行必要的应用程序更改，并在禁用已被确定为安全风险的特定功能/项目之前，证明应用程序将能够正常运行。

翻译自: https://www.sqlshack.com/securing-sql-server-surface-area/