使用SQL xp_cmdshell或是xp_fileexist指令時，無法存取遠端資源

您可以參閱以下網址：

http://msdn.microsoft.com/en-us/library/ms190359.aspx

 

預設，使用者執行此預存程序時，執行身分會是SQL Server的服務啟動帳戶，您必須讓此啟動帳戶擁有遠端存取資源的權限。

 

您可以使用以下方式檢查：

方式1：

使用xp_cmdshell 'whoami.exe'語法：

 

方式2 透過服務管理員，檢查SQL Service啟動帳戶：

1.       開始 > 執行，輸入services.msc

 

2.       找到SQL Server服務啟動帳戶，右鍵 > 內容

 

3.       在登入頁籤中，您可以看到啟動帳戶

 

三、  使用SQL 2008 xp_fileexist指令時，無法執行

幫您做了一下Research，同xp_cmdshell時，執行的帳戶亦是SQL服務啟動帳戶，您必須確認該帳戶具有遠端存取的權限：

 

若您使用者因非system administrator，您可以參閱以下workaround，讓一般使用者可以透過SQL 2005/2008的Impersonate方式，執行此指令：

The design of xp_fileexist has been changed in SQL Server 2005. In SQL Server 2005, when the xp_fileexist is called, it will check the security context. If you are the member of sysadmin, the stored procedure will return the correct result. However, when you are not the member of sysadmin, the stored procedure will return False no matter what the file exist or not. This change is by design. Xp_fileexist is an non-document stored procedure. That means SQL Server developer does not expect the end user to use this kind of stored procedure. Due to the security concern, the security has been enhanced and the usage of xp_fileexist (including some other stored procedure of the same kind) has been restricted for system administrator of SQL Server only.

create proc [dbo].[sp_file_exists]

( @filename nvarchar(128) )

with execute as 'dbo'

as

begin

exec master..xp_fileexist @filename

end