{"id":84375,"date":"2019-05-29T14:03:47","date_gmt":"2019-05-29T14:03:47","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84375"},"modified":"2022-04-24T21:15:08","modified_gmt":"2022-04-24T21:15:08","slug":"introduction-to-sql-server-security-part-6","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/devops\/data-privacy-and-protection\/introduction-to-sql-server-security-part-6\/","title":{"rendered":"Introduction to SQL Server Security \u2014 Part 6"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. Introduction to SQL Server Security \u2014 Part 1<\/a><\/li>\n
  2. Introduction to SQL Server Security \u2014 Part 2<\/a><\/li>\n
  3. Introduction to SQL Server Security \u2014 Part 3<\/a><\/li>\n
  4. Introduction to SQL Server Security \u2014 Part 4<\/a><\/li>\n
  5. Introduction to SQL Server Security \u2014 Part 5<\/a><\/li>\n
  6. Introduction to SQL Server Security\u00a0\u2014 <\/span>Part 6<\/a><\/li>\n
  7. \u00a0<\/li>\n<\/ol>\n\n

    SQL Server offers a wide range of tools for ensuring that your environment remains secure and that privacy is protected. In the last article in this series, I covered some of the system views and functions that can help you safeguard that environment.<\/p>\n

    In this article, I continue the discussion by providing an overview of five other important features: SQL Server Configuration Manager, server-level facets, the sp_configure<\/code> system stored procedure, the SQL Vulnerability Assessment tool, and the SQL Data Discovery & Classification tool. As with any SQL Server tools, the better you understand how to use these features, the more effectively you can protect your databases and the data they contain.<\/p>\n

    SQL Server Configuration Manager<\/h2>\n

    SQL Server Configuration Manager is a Microsoft Management Console snap-in that lets you manage the services, network protocols, and network connectivity configurations associated with a SQL Server instance. Through this tool, you can start, pause, resume, or stop services, or you can view or change service properties. You can also enable or disable connection protocols, force protocol encryption, or configure SQL Server to listen on a specific port, pipe, or network protocol.<\/p>\n

    Figure 1 shows SQL Server Configuration Manager with the SQL Server Services<\/em> node selected. You can navigate through any of the nodes, as well as drill into specific services or configurations, where you can view or modify property settings.<\/p>\n

    <\/p>\n

    Figure 1. Viewing services and configurations in SQL Server Configuration Manager<\/p>\n

    To access a component\u2019s properties, double-click the listing in the right pane. For example, one of the services shown in Figure 1 is SQL Server (SQLSRV16),<\/em> which is a running instance of SQL Server 2016. When I double-click the service, the SQL Server (SQLSRV16) Properties<\/em> dialog box appears, as shown in Figure 2.<\/p>\n

    <\/p>\n

    Figure 2. Viewing service details in SQL Server Configuration Manager<\/p>\n

    The options available in the Properties<\/em> dialog box depend on the selected service or configuration. In this case, the dialog box opens to the Log On<\/em> tab, where you can start, stop, pause, and restart the service. You can also change the service account or update the password.<\/p>\n

    The other tabs provide additional options. For example, on the Service<\/em> tab, you can set the service to start automatically, specify that the service must be started manually, or disable the service altogether.<\/p>\n

    SQL Server Configuration Manager is a good place to start for handling services, network protocols, and network connectivity configurations all in one interface. The interface is simple to use and is a lot easier than wading through all a system\u2019s services to find those specific to SQL Server.<\/p>\n

    SQL Server Configuration Facets<\/h2>\n

    In SQL Server Management Studio (SSMS), you can configure several server-wide facets related to SQL Server security. A facet is a collection of logical properties that apply to a specific area of management.<\/p>\n

    To access the server-level facets, right-click the SQL Server instance name in Object Explorer and then click Facets<\/em>. When the View Facets<\/em> dialog box appears, select the Surface Area Configuration<\/em> facet from the Facet<\/em> drop-down list. The main window displays the properties associated with the selected facet, as shown in Figure 3.<\/p>\n

    <\/p>\n

    Figure 3. Accessing the Surface Area Configuration facet<\/p>\n

    As the name suggests, the Surface Area Configuration facet provides quick access to surface area configuration settings. Here you can enable or disable features as necessary, the idea being that you should disable any unnecessary features to reduce the surface area. For example, you can configure the SqlMailEnabled<\/em> property, which supports legacy applications that exchange email messages with the database engine.<\/p>\n

    Another useful facet is Server Security,<\/em> which is shown in Figure 4. Here you can enable or disable security-related properties that apply at the server-level, such as the CrossDBOwnershipChainingEnabled <\/em>property, which controls cross-database ownership chaining. Currently, the property is set to False<\/em>, so chaining is not permitted.<\/p>\n

    <\/p>\n

    Figure 4. Accessing the Server Security facet properties<\/p>\n

    In the View Facets<\/em> dialog box, you can also access a number of other facets, such as Server Audit<\/em> and Server Configuration<\/em>. Facets provide a quick and easy way to adjust settings from within SSMS. However, you can also use the sp_configure<\/code> stored procedure to set database engine options, which provides more flexibility for controlling SQL Server settings.<\/p>\n

    SQL Server sp_configure Stored Procedure<\/h2>\n

    The sp_configure<\/code> system stored procedure lets you view or modify server-wide configuration settings. When used to modify a setting, the stored procedure is often executed in conjunction with a RECONFIGURE<\/code> statement, which applies the new setting immediately to the server environment\u2014if the setting is dynamic<\/em>. If it\u2019s not dynamic, the new setting does not take effect until the SQL Server service has been restarted.<\/p>\n

    You can use the sys.configurations<\/code> system view to determine whether a setting is dynamic. The view also returns other important information about configuration settings. The following SELECT<\/code> statement uses the sys.configurations<\/code> view to return details about the server-wide configuration settings available to the current SQL Server instance:<\/p>\n

    USE master;\r\nGO\r\nSELECT * FROM sys.configurations;<\/pre>\n
    Figure 5 shows part of the results returned by the SELECT<\/code> statement on my system, a local instance of SQL Server 2017. The statement returns a total of 77 rows.<\/p>\n
    <\/p>\n
    Figure 5. Viewing all server-wide configuration options<\/p>\n
    In addition to the name and description of each setting, the sys.configurations<\/code> view returns several other columns, which have implications when using the sp_configure<\/code> stored procedure:<\/p>\n