I would like to prevent the reading of a specific file using xp_cmdshell in SQL Server, but I also want to allow the file to be read using a Python function in SQL Server. So far, I've tried the following steps, but I haven't been able to solve the issue:
[First Attempt]: I modified the configuration settings to restrict file access with xp_cmdshell, but it also affected the Python function.
[Second Attempt]: I adjusted the file's access permissions, but this allowed both xp_cmdshell and the Python function to read the file.
[Third Attempt]: I attempted to read the file using Python with sp_execute_external_script, but I encountered an "Access is denied" error.
How can I configure SQL Server to prevent reading a specific file with xp_cmdshell while still allowing it to be read by a Python function? What permissions and steps should I consider?
1> xp_cmdshell 'type c:\\inetpub\\wwwroot\\web.config';
2> go
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Access is denied.
NULL
(2 rows affected)1> EXEC sp_execute_external_script
2> @language =N'Python',
3> @script= N'import os; os.system("type c:\\inetpub\\wwwroot\\web.config")'
4> go
STDERR message(s) from external script:
Access is denied.
1条答案
按热度按时间olhwl3o21#
The Python language extension and xp_cmdshell run under different security contexts.
xp_cmdshell runs as the SQL Server service account (when run by a sysadmin), or the configured xp_cmdshell proxy account (otheriwise).
The Python language extension runs under the Launchpad service and isolated in an AppContainer sandbox.
The security for the Launchpad process to access files is explained here:
"If your Python or R scripts need access to any other directory, you need give either Read & execute and/or Write permissions to the NT Service\MSSQLLaunchpad service user account and ALL APPLICATION PACKAGES on this directory."
SQL Server on Windows: Isolation changes for Machine Learning Services
So break inheritance for the folder containing the file, and add permissions only for aministrators, NT Service\MSSQLLaunchpad, and ALL APPLICATION PACKAGES.
Of course this doesn't really protect the file contents from a sysadmin, who could simply run sp_execute_external_script to get the file contents.