SQL Server实例的十大安全注意事项

culuo4781

于 2020-07-23 06:54:38 发布

阅读量498

点赞数

原文链接：https://www.sqlshack.com/top-10-security-considerations-sql-server-instances/

版权

SQL Server is one of the world’s leading data platforms. It is being broadly used hosting millions of databases. These databases store data. This data are each organization’s most valuable asset. It is with this data that organizations run their everyday operations and processes. This fact makes it a necessity to efficiently secure your SQL Server instances, in order to protect your databases and consequently your data. This article suggests a list with the top 10 security considerations based on which you can efficiently secure your SQL Server instances.

SQL Server是世界领先的数据平台之一。它广泛用于托管数百万个数据库。这些数据库存储数据。这些数据是每个组织最有价值的资产。借助这些数据，组织可以运行其日常运营和流程。这个事实使得有必要有效地保护SQL Server实例，以保护数据库，从而保护数据。本文为您提供了一个列出前10个安全注意事项的列表，您可以根据这些列表有效地保护SQL Server实例。

Physical environment

物理环境

Securing the physical environment of your database server is crucial. Imagine having your SQL Server instance hardened to the maximum security level but leaving the physical location of the database server with weak security. This would truly be a contradiction. As such, you need to limit the physical access to your physical database server. To achieve this, you must establish the proper procedures to be followed along with adequate controls in order only authorized personnel to have physical access to the servers.
保护数据库服务器的物理环境至关重要。想象一下，将您SQL Server实例强化到最大安全级别，但是使数据库服务器的物理位置的安全性很弱。这确实是一个矛盾。因此，您需要限制对物理数据库服务器的物理访问。为此，您必须建立要遵循的适当程序以及适当的控制措施，以便仅授权人员才能对服务器进行物理访问。
Operating system

操作系统

Securing the Operating System onto which SQL Server is installed is also important. If the Operating System has not been secured, a potential attacker could for example, access your SQL Server instance’s data and log files thus gaining access to your data. To secure the Operating System you can follow the below guidelines:

保护安装了SQL Server的操作系统也很重要。如果尚未保护操作系统，则潜在的攻击者可能会例如访问SQL Server实例的数据和日志文件，从而获得对数据的访问权限。要保护操作系统，您可以遵循以下准则：
- Keep it up to date with the latest patches and service packs (after you verify that they won’t affect the operation of the database server in any way by properly testing them).
  使它与最新的补丁程序和Service Pack保持最新（在通过适当的测试来验证它们不会以任何方式影响数据库服务器的运行之后）。
- Follow the least-privilege principle for service accounts (you can easily do this by following Microsoft’s recommendations – see MSDN article: Configure Windows Service Accounts and Permissions).
  遵循服务帐户的最低特权原则（您可以通过遵循Microsoft的建议轻松进行此操作-请参阅MSDN文章：配置Windows服务帐户和权限）。
- Follow the least-privilege account for any other accounts as well.
  对于其他任何帐户，也要遵循最低特权帐户。
- Restrict access to SQL Server’s physical files by setting the proper folder and file security permissions.
  通过设置适当的文件夹和文件安全权限，限制对SQL Server物理文件的访问。
Network
网络

All data within an organization travel through the network. You have your database servers, application servers, clients, Storage Area Network (SAN) and the list goes on. The network must be secured in order to restrict access to resources from unauthorized sources, as well as not allow data to flow to unauthorized destinations. In order to achieve this level of security, you need to properly configure the firewalls. For example, put a firewall between the database server and the Internet. When it comes to SQL Server, you need to follow Microsoft’s recommendations in order to properly configure the Windows firewall for SQL Server. These recommendations can be found in this MSDN article.
组织内的所有数据都通过网络传播。您拥有数据库服务器，应用程序服务器，客户端，存储区域网络（SAN），并且列表继续。必须保护网络安全，以限制从未经授权的来源访问资源，并且不允许数据流向未经授权的目的地。为了达到此安全级别，您需要正确配置防火墙。例如，在数据库服务器和Internet之间放置防火墙。对于SQL Server，您需要遵循Microsoft的建议，以便为SQL Server正确配置Windows防火墙。这些建议可以在此MSDN文章中找到。
Application
应用

When it comes to database security, it’s not only about securing your SQL Server instances. It also has to do with securing the application which connects to the SQL Server instance. The reason is that data flows take place between your SQL Server instance and your application, so you need to make sure that all three entities participating to this communication have been secured. Typically, there are three entity types participating to such data flow:

在数据库安全方面，不仅要保护您SQL Server实例。它还与保护连接到SQL Server实例的应用程序有关。原因是数据流发生在SQL Server实例和应用程序之间，因此您需要确保参与此通信的所有三个实体均已安全。通常，有三种实体类型参与此类数据流：
- Database Server and Instance
  数据库服务器和实例
- Clients (i.e. Application server or direct client connections)
  客户端（即应用服务器或直接客户端连接）
- Network connection
  网络连接
To secure your application, you can perform the below:

为了保护您的应用程序，可以执行以下操作：
- Do not expose user passwords in code or in external files (i.e. file with connection strings) that are used by the application. Use encrypted connection strings instead.
  不要在应用程序使用的代码或外部文件（即带有连接字符串的文件）中公开用户密码。请改用加密的连接字符串。
- Prefer using Windows Authentication for application service accounts that connect to your SQL Server instance instead of Mixed Mode (username/password). With Windows Authentication, the client application does not send any passwords to SQL Server thus making the process more secure.
  对于连接到SQL Server实例而不是混合模式（用户名/密码）的应用程序服务帐户，最好使用Windows身份验证。使用Windows身份验证，客户端应用程序不会向SQL Server发送任何密码，从而使过程更加安全。
- Establish an encrypted connection to your SQL Server instance. With an encrypted connection, even if a potential attacker intercepts the network traffic between your client and the SQL server instance, he or she will not be able to read the data because it will not be just clear text but rather encrypted data packets. SQL Server 2016 introduced the Always Encrypted feature which provides you with an additional encryption option when it comes to data traffic between clients and SQL Server instances.
  建立与SQL Server实例的加密连接。使用加密的连接，即使潜在的攻击者拦截了客户端和SQL Server实例之间的网络流量，他或她也将无法读取数据，因为它不仅是纯文本，而是加密的数据包。 SQL Server 2016引入了始终加密功能，当涉及客户端和SQL Server实例之间的数据通信时，该功能为您提供了额外的加密选项。
SQL Server instance: Surface Area

SQL Server实例：外围应用

Now that all “external” factors have been discussed, let’s focus on the top factors for securing your SQL Server instance and its hosted databases. The first thing to check is your SQL Server instance’s surface area. Surface area configuration in SQL Server allows stopping or disabling components and features that are not used. By disabling these features, you limit the surface of SQL Server which is subject to potential attacks. Note that if you need to enable one or more of these features, you also need to take all the necessary security precautions.

现在已经讨论了所有“外部”因素，让我们集中讨论保护SQL Server实例及其托管数据库的主要因素。首先要检查的是SQL Server实例的表面积。 SQL Server中的外围应用配置允许停止或禁用未使用的组件和功能。通过禁用这些功能，可以限制受到潜在攻击SQL Server的表面。请注意，如果需要启用这些功能中的一项或多项，则还需要采取所有必要的安全预防措施。

You can see the available surface area configuration options by executing the following T-SQL script:

您可以通过执行以下T-SQL脚本来查看可用的表面积配置选项：
```
 
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure;
GO
sp_configure 'show advanced options',0;
GO
RECONFIGURE;
 
```
Below you can see a screenshot that illustrates the (partial) output of the above script on a SQL Server 2016 instance. Note that in SQL Server 2016 there are available 76 surface area configuration options through sp_configure (when ‘show advanced options’ is set to 1).

在下面，您可以看到一个屏幕快照，该屏幕快照说明了上述脚本在SQL Server 2016实例上的（部分）输出。请注意，在SQL Server 2016中，通过sp_configure提供了76个表面积配置选项（“显示高级选项”设置为1时）。

Major surface area options that should be disabled, unless there is a justified need to enable them, are:

除非有合理的需要启用它们，否则应禁用的主要表面积选项是：
If for any reason you need to enable any of the above surface area configuration options then you must make sure that you follow the recommended security precautions prior to enabling them.

如果出于任何原因需要启用任何上述表面积配置选项，则必须确保在启用它们之前遵循建议的安全预防措施。

For more information please refer to the following MSDN article.

有关更多信息，请参考以下MSDN文章。
SQL Server instance: Server-Level and Database Permissions

SQL Server实例：服务器级别和数据库权限

You need to be very careful with server-level permissions as they grant access on the instance-level. The available fixed server roles in SQL Server 2016 are:

您需要非常小心服务器级别的权限，因为它们会授予实例级别的访问权限。 SQL Server 2016中可用的固定服务器角色为：
- sysadmin
  系统管理员
- serveradmin
  服务器管理员
- securityadmin
  安全管理员
- processadmin
  流程管理员
- setupadmin
  setupadmin
- bulkadmin
  批量管理员
- diskadmin
  磁盘管理员
- dbcreator
  dbcreator
- public
  上市
Make sure that you include only the necessary logins to memberships on the above roles. For example, never assign the SysAdmin role to BUILTIN\Administrators windows group. Machine administrators must not have access to the SQL Server instance. Now, if you want to grant access to SQL Server to a person that he or she is also a machine administrator, you can do this individually for the specific login and not the BUILTIN\Administrators group.

确保仅包含上述角色的成员资格所需的登录名。例如，永远不要将SysAdmin角色分配给BUILTIN \ Administrators Windows组。机器管理员必须不能访问SQL Server实例。现在，如果您要授予自己也是计算机管理员的人对SQL Server的访问权限，则可以针对特定的登录名而不是BUILTIN \ Administrators组单独进行此操作。

The following MSDN article describes what exactly each one of the above server roles allows. Please take into consideration this article whenever you assign a server-role to a SQL Server user.

以下MSDN文章描述了上述每个服务器角色所允许的确切含义。将服务器角色分配给SQL Server用户时，请考虑本文。

In addition to server roles, you should also be careful when you provide access to SQL Server databases. For example, grant “db_owner” access only when needed because among other, a db_owner can drop the database. You can review the fixed-database roles for SQL Server on the following MSDN article.

除了服务器角色之外，在提供对SQL Server数据库的访问权限时也应小心。例如，仅在需要时才授予“ db_owner”访问权限，因为db_owner尤其可以删除数据库。您可以在以下MSDN文章上查看SQL Server的固定数据库角色。
SQL Server instance: Authentication and Authorization

SQL Server实例：身份验证和授权

When installing SQL Server, one of the settings you can set is the Authentication Mode. You can select Windows Authentication Mode or SQL Server and Windows Authentication Mode combined (also known as “Mixed Mode”). Windows Authentication Mode is more secure because with Windows Authentication, SQL Server validates the user’s credentials using the Windows principal token in the Operating System. This makes the whole process more secure because the authentication is made using the Kerberos security protocol.

安装SQL Server时，可以设置的设置之一是Authentication Mode 。您可以选择Windows身份验证模式或SQL Server和Windows身份验证模式结合使用（也称为“混合模式”）。 Windows身份验证模式更安全，因为使用Windows身份验证，SQL Server使用操作系统中的Windows主体令牌来验证用户的凭据。由于使用Kerberos安全协议进行身份验证，因此使整个过程更加安全。

If, for any reason, you do not want to set your SQL Server instance’s authentication mode to “Windows Authentication Mode” only, then at least try for the majority (if not all) of the logins you create to use Windows Authentication.

如果出于某种原因而不想将SQL Server实例的身份验证模式仅设置为“ Windows身份验证模式”，则至少尝试使用创建的大多数（如果不是全部）登录名来使用Windows身份验证。

SQL Server gives you the option to change the authentication mode at any moment via your SQL Server instance’s properties (from SSMS, right-click on the instance, select “Properties” and then click on the “Security” tab).

SQL Server为您提供了随时通过您SQL Server实例的属性更改身份验证模式的选项（在SSMS中，右键单击该实例，选择“属性”，然后单击“安全性”选项卡）。

Note: Even though you can change the Server authentication mode at any time, you need to take consideration the current logins you have on your instance and check if they could be affected prior to making any changes to the Server authentication mode.

注意：即使您可以随时更改服务器身份验证模式，但在对服务器身份验证模式进行任何更改之前，都需要考虑实例上的当前登录名，并检查它们是否会受到影响。

Other security considerations that fall under the authorization category are:

授权类别下的其他安全注意事项包括：
- Public database role
  公共数据库角色
- Guest user permissions
  来宾用户权限
- Orphaned users
  孤儿
Public Database Role
Every database user in SQL Server is automatically assigned the ‘Public’ database role. However, beyond its default behavior, this role must not be given any additional permission on any database in order to avoid allowing access to unauthorized users.

公共数据库角色
SQL Server中的每个数据库用户都会被自动分配“公共”数据库角色。但是，除默认行为外，不得为任何数据库授予该角色任何其他权限，以避免允许未经授权的用户访问。

Guest User Permissions
The Guest user is a special SQL Server database user which exists in order to permit access to a database for logins that are not mapped to a specific database user. However, because any login can use a database via this user, it is recommended to disable this user, after of course you verify that it is not required for a specific database. If for any reason you do not want to disable the Guest user, you can consider revoking (if applicable) the CONNECT permission. Read this Microsoft Support article for more information.

来宾用户权限
来宾用户是一种特殊SQL Server数据库用户，存在该用户是为了允许未映射到特定数据库用户的登录名访问数据库。但是，由于任何登录都可以通过该用户使用数据库，因此建议您禁用该用户，当然，在您确认特定数据库不需要该用户之后。如果出于任何原因您不想禁用来宾用户，则可以考虑撤消（如果适用）CONNECT权限。阅读此Microsoft支持文章以获取更多信息。

Orphaned Users
Orphaned users are database users that are not assigned to any login. Unless the involved databases are Contained Databases, these users create a risk because potential attackers might get access to them and inherit their permissions on the database. The recommended practice is after verifying that these users are not used in any way, to first disable them (REVOKE CONNECT) and eventually remove them. More info on orphaned users can be found on the following MSDN article.

孤儿
孤立的用户是未分配任何登录名的数据库用户。除非所涉及的数据库为“ 包含数据库” ，否则这些用户会带来风险，因为潜在的攻击者可能会访问它们并继承其对数据库的权限。建议的做法是在确认没有以任何方式使用这些用户之后，首先禁用它们（REVOKE CONNECT）并最终将其删除。在以下MSDN文章中可以找到有关孤立用户的更多信息。
SQL Server instance: Password Policies
SQL Server实例：密码策略

As mentioned in Point 7 (Authentication and Authorization), when Windows Authentication Mode is used, then password policy enforcement (i.e. complexity validation for strong passwords), support for account lockout and password expiration are some of security features that are applied by default for the logins that use this mode.

如第7点（身份验证和授权）所述，使用Windows身份验证模式时，密码策略实施（即强密码的复杂性验证），对帐户锁定和密码过期的支持是默认情况下为使用此模式的登录名。

If you want to apply the same complexity and expiration policies used in Windows for SQL logins (that use a username and password) as well, then you need to make sure that Password Policy and Password Expiration options are set for these logins as well.

如果还要对Windows登录使用相同的复杂性和过期策略（使用用户名和密码），则需要确保也为这些登录设置了“ 密码策略”和“ 密码过期”选项。

Note: For logins which are used as service accounts, it is not recommended to set their passwords to expire as such thing could possibly disrupt the connection of the respective applications to the databases if the password expired. Instead of setting these passwords to expire, you can choose a very strong password in the beginning, do not share it with anyone, and still if you want to change the password, you can do it in a more controlled manner in coordination with the application owners.

注意：对于用作服务帐户的登录名，不建议将其密码设置为过期，因为如果密码过期，则可能会破坏相应应用程序与数据库的连接。您可以在开始时选择一个非常强壮的密码，而不与任何人共享，而不必将这些密码设置为过期，并且即使您想更改密码，也可以与应用程序配合以更可控的方式进行操作拥有者。

More information about the SQL Server Password Policy can be found on this MSDN article.

可以在此MSDN文章上找到有关SQL Server密码策略的更多信息。
SQL Server instance: Patching

SQL Server实例：修补

It is essential that you keep your SQL Server instances up to date by installing the latest service pack and critical cumulative patches. However, prior to applying any patch or service pack, you need to verify that it will not create any issues for your current SQL Server setup. To this end, always test service packs and patches on a Test environment first and proceed to Production only after you verify that everything works properly. You can visit Microsoft’s Update Center for Microsoft SQL Server in order to find information about the latest service packs and updates for all the available SQL Server versions.

通过安装最新的Service Pack和重要的累积修补程序，使SQL Server实例保持最新是至关重要的。但是，在应用任何补丁程序或Service Pack之前，您需要验证它不会对当前SQL Server安装程序造成任何问题。为此，请始终先在测试环境中测试Service Pack和补丁程序，然后在确认一切正常后再进行生产。您可以访问Microsoft的Microsoft SQL Server更新中心，以查找有关所有可用SQL Server版本的最新Service Pack和更新的信息。
SQL Server instance: Encryption
SQL Server实例：加密

If you have databases that store sensitive data, SQL Server provides a number of encryption options that you can use.

如果您具有存储敏感数据的数据库，则SQL Server提供了许多可以使用的加密选项。

The encryption options currently available are:

当前可用的加密选项是：
Additionally you can encrypt the file system/disk with EFS and BitLocker.

另外，您可以使用EFS和BitLocker加密文件系统/磁盘。
This article suggested the top 10 security considerations for your SQL Server installation. Sure, there are many other additional options and features that can be tuned in order to contribute towards more secure SQL Server instances, but this article aimed at providing the most significant security factors based on years of experience of working with SQL Server and proven security best practices.

本文提出了SQL Server安装的十大安全注意事项。当然，还有许多其他可选选项和功能可以进行调整，以有助于实现更安全SQL Server实例，但是本文旨在根据多年使用SQL Server的经验以及公认的最佳安全性提供最重要的安全因素。实践。

Securing your SQL Server instances should be an ongoing process. You should regularly check your SQL Server instances and databases and keep them up to date and healthy in order to provide a secure, non-disruptive database service to the data applications they support.

保护您SQL Server实例应该是一个持续的过程。您应定期检查SQL Server实例和数据库，并使其保持最新状态和健康状态，以便为其所支持的数据应用程序提供安全，无中断的数据库服务。

翻译自: https://www.sqlshack.com/top-10-security-considerations-sql-server-instances/

culuo4781

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL Server实例的十大安全注意事项

SQL Server is one of the world’s leading data platforms. It is being broadly used hosting millions of databases. These databases store data. This data are each organization’s most valuable asset. It i...
复制链接

扫一扫