Chapter 1 Securing Your Server and Network (14): restricted functions-xp_mongoshell and OPENROWSET, openrowset

Chapter 1 Securing Your Server and Network (14): restricted functions-xp_mongoshell and OPENROWSET, openrowset
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38489765

 

Preface:

 

For security reasons, some functions are disabled when SQL Server is installed. starting from 2008, all sensitive options can be managed by a [aspect] called the [peripheral application configurator, this feature appeared in the form of an independent tool at 2005, and was canceled at 2008.

 

Implementation:

 

1. in SQL Server Management Studio (SSMS), right-click the Server node and choose aspect ]:

 

2. In the view aspect dialog box, select the peripheral application configurator ]:

Source: http://blog.csdn.net/dba_huangzj/article/details/38656615

3. Set the attributes of AdHocRemoteQueriesEnabled, OleAutomationEnabled, and XPCmdShellEnabled to False:

 

You can use the following statement to query the [aspect] information:

SELECT * FROM sys.system_components_surface_area_configuration WHERE component_name IN (     'Ole Automation Procedures',     'xp_cmdshell' );

 

In addition to the peripheral Configuration Manager, you can also use [policy management, PBM] to manage these items, which will be described in Chapter 7.

 

4. You can also use the T-SQL to check the 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';

Source: http://blog.csdn.net/dba_huangzj/article/details/38656615

 

5. In the above results, run_value is enabled for 1, and 0 is disabled. If you need to disable these, you can use the following statement. Remember to use the RECONFIGURE command to make the change 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 query allows you to use a string to connect to the target data source in a T-SQL statement. You can use the OPENROWSET/OPENDATASOURCE keyword to access the remote database through OLEDB, as shown below:

SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=SERVER2;Trusted_Connection=yes;', 'SELECT * FROM AdventureWorks.Person.Contact') AS a;

The permissions in this method are based on the authorization type. If you use SQL Server for identity authentication, the permissions are the account permissions of SQL Server. If you are using Windows identity authentication, the permissions are the permissions of Windows accounts.

OLE automation procedures is a system stored procedure that allows T-SQL code to use OLE automation objects and then run outside the context of SQL Server, such as sp_OACreate used to instantiate an object and operate on it. The following code deletes a folder 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_mongoshell extended stored procedures allow access to underlying operating systems using T-SQL, such:

exec xp_cmdshell 'DIR c\*.*';

 

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

 

More:

Source: http://blog.csdn.net/dba_huangzj/article/details/38656615

To allow non-sysadmin login to use xp_mongoshell, You can encapsulate it into the stored procedure and execute. If you want them to run any command, you must define a proxy account:

EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\user','user password';

The following statement can be used for query:

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

The following statement can be used to remove an ECS instance:

EXEC sp_xp_cmdshell_proxy_account NULL;

 

In addition, you cannot prohibit sysadmin members from using xp_cmdshell. The sysadmin role can be enabled even if it is disabled.

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