Chapter 1 Securing Your Server and Network (14): Restricting Functionality--xp_cmdshell and OPENROWSET

原文出处：http://blog.csdn.net/dba_huangzj/article/details/38656615，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

Last article: http://blog.csdn.net/dba_huangzj/article/details/38489765

Preface:

For security reasons, some features are disabled when SQL Server is installed, and since 2008, all sensitive options can be managed by a "aspect" called the "Perimeter Application Configurator", which was in the form of an independent tool at 2005 and canceled in 2008.

implementation:

1. In SQL Server Management Studio (SSMS), right-click the Servers node and select aspects:

2. In the View Side dialog box, select the perimeter configuration:

Original source: http://blog.csdn.net/dba_huangzj/article/details/38656615

3. Set the properties of "adhocremotequeriesenabled", "oleautomationenabled" and "xpcmdshellenabled" to false:

You can use the following statement to query these "aspects" information:

SELECT * from 
sys.system_components_surface_area_configuration 
WHERE component_name in 
( 
    ' Ole Automation procedures ', 
    ' xp_cmdshell ' 
);

In addition to the perimeter Configuration Manager, you can manage these by using "policy Management, PBM", which is described in chapter seventh.

4. You can also use T-SQL Check Status:

EXEC sp_configure ' show advanced options ', 1; 
Reconfigure; 
EXEC sp_configure ' Ad Hoc distributed Queries '; 
EXEC sp_configure ' Ole automation procedures '; 
EXEC sp_configure ' xp_cmdshell ';

Original source: http://blog.csdn.net/dba_huangzj/article/details/38656615

5. The above results, Run_value 1 is enabled, 0 is disabled, if you need to disable these, you can use the following statement, remember to use the RECONFIGURE command to make the changes take effect:

EXEC sp_configure ' Ad Hoc distributed Queries ', 0; 
EXEC sp_configure ' Ole automation procedures ', 0; 
EXEC sp_configure ' xp_cmdshell ', 0; 
Reconfigure;

principle:

Ad hoc distributed queries allow strings that connect to the target data source within a T-SQL statement, using the Openrowset/opendatasource keyword to access the remote database through OLE DB, as follows:

SELECT a.* 
from OPENROWSET (' sqlncli ', ' server=server2; Trusted_connection=yes ', 
' SELECT * from AdventureWorks.Person.Contact ') as A;

The permissions for this method are based on the type of authorization, and if SQL Server authentication is used, the permissions are the account permissions of the SQL Server service and, if Windows is authenticated, permissions are the permissions of the Windows account.

The OLE Automation program (OLE Automation Procedures) is a system stored procedure that allows T-SQL code to use an OLE Automation object and then run outside of the SQL Server context, such as sp_OACreate for instantiating an object and manipulating the object. The following code shows how to delete a folder by using the OLE Automation program:

EXEC sp_configure ' show advanced options ', 1; 
Reconfigure; 
EXEC sp_configure ' role automation procedures ', 1; 
Reconfigure; 
Go 
DECLARE @FSO int, @OLEResult int; 
EXECUTE @OLEResult = sp_OACreate ' Scripting.FileSystemObject ', @FSO 
OUTPUT; 
EXECUTE @OLEResult = sp_OAMethod @FSO, ' DeleteFolder ', NULL, ' C:\ 
sqldata '; 
SELECT @OLEResult; 
EXECUTE @OLEResult = sp_OADestroy @FSO;

Only members of the sysadmin server role can use these programs.

xp_cmdshell extended stored procedures allow the use of T-SQL to access the underlying operating system, such as:

exec xp_cmdshell ' DIR c\*.* ';

Restricting the permissions of these programs can protect the security of the server to a certain extent.

MORE:

Original source: http://blog.csdn.net/dba_huangzj/article/details/38656615

To allow non-sysadmin logins to use xp_cmdshell, you can encapsulate it into stored procedures and execute AS. If you want them to run arbitrary commands, you must define a proxy account:

EXEC sp_xp_cmdshell_proxy_account ' domain\user ', ' User password ';

You can query with the following statement:

SELECT * from 
sys.credentials 
WHERE name = ' # #xp_cmdshell_proxy_account # #;

The following statements can be used to remove:

EXEC Sp_xp_cmdshell_proxy_account NULL;

In addition, you cannot prohibit members of the sysadmin from using xp_cmdshell. Members of the sysadmin role can be enabled even if disabled.

Next: http://blog.csdn.net/dba_huangzj/article/details/38657111