July 14, 2008 at 2:03 pm
I am trying to get the xp_fileexist to work for a SQL account. When I run the following command as me with dbo rights ...
Exec Master.dbo.xp_fileexist \\remotefileserver\share1\yomama.jpg
It retursn correctly 1 0 1
When I run it as the SQL user 'YOMAMA', I get...
0 0 0.
What gives?
The account that the SQL services runs as is a domain account and has NTFS "rights" FULL to the directory and the files in that dir.
I know if I create a SPN (Service Principle Name) for it in AD it will work However, I want to try without doing a SPN.
Thanks
July 14, 2008 at 2:32 pm
Does the following link help?
http://www.sqlservercentral.com/articles/Stored+Procedures/xpfileexist/183/
July 14, 2008 at 3:15 pm
No, sorry. That example goes local to the server and it always works. My issue is trying to reach out to a file system on the network via a UNC path.
Thanks
July 14, 2008 at 3:54 pm
The account that SQL runs as has Admin rights to both server and I went granular to the remote filesystem to confirm the NTFS ACLS are correct for the account.
This is I believe the "Third hop issue".
Like I said before, I know setting up an SPN, basically Kerberos, will make it work (done it before) but I'm trying to stay away from Kerberos at the moment.
September 23, 2008 at 1:37 pm
Has anyone been able to resolve this?
I am experiencing the same issue. On sql 2005 sp2, the only way I am able to grant rights to a SQL Login to execute xp_fileexist properly is to add it to the sysadmin role. Obviously I would like to avoid granting such broad permissions.
Any help here will be greatly appreciated.
September 23, 2008 at 2:22 pm
I would gladly entertain suggestions of other ways that I could check for existence of a specific file (.txt) from within a stored procedure.
Thank you kindly,
Wisker
December 11, 2008 at 6:25 pm
I have the same issue with using master.dbo.xp_fileexist on a remote server.
Another method would be great as long as it doesn't indicate a file exists when the target is a directory or a directory exists when the target is a file.
Anyone know what permissions master.dbo.xp_fileexist runs under?
For some reason MS decided to screw up the execution permission of xp_cmdshell. If your an admin it uses the service logon but if your not it uses a proxy account you setup. Seems pretty stupid to me. Beware of using CLR routines for this also as they may work fine if your account allows authentication however if not even adding the impersonation code in the CLR doesn't seem to work.
Actually xp_fileexist doesn't work on wildcards so it's not what I want .
August 28, 2009 at 6:25 pm
Was anyone of you able to find a solution to problem? I cannot give the sql (non-windows) user a sysadmin right and without it xp_fileexist just returns 0,0,0
Any help would be greatly appreciated.
Thanks,
-K
June 9, 2010 at 3:01 am
It returned 0,0,0. I looked many account are there Start with SQLServer2005MS**** , which account we have to give accesss? I tried to give full access to 'Everyone' account but still fails to test connection 🙁
November 25, 2010 at 2:49 am
I have not experimented with this to say that this is the correct answer but it could be, based on other operations with SQL Server 2005 and 2008.
Is your SQL Server service running with a domain account or a local user? If it is a local user you may want to change this to a domain account.
If it is a domain account you can then ensure that account has permission to access the share where your file is located.
The SQL user appears to require sysadmin.
I have been successful in using this XP accessing a share using both a trusted connection and a SQL Server account. The database server is using a domain login to run the SQL Server service.
/Steve
February 2, 2011 at 11:52 am
Here is the answer, from Microsoft:
8.4.3 xp_fileexistThis Stored Procedure can be used to determine whether a particular file exists on disk or
not.
Input: <filename>
Result: 0 / Error number
Permission If the calling user is ‘sa’ this Stored Procedure is executed in the context of
the SQL Server system account. In all other cases the Stored Procedure will
be executed in the context of the calling user (i.e. the Stored Procedure will
impersonate the user). This impersonation will fail for the case that a SQL
login is used and an empty set will be returned.
Syntax: EXECUTE xp_fileexist <filename> [, <file_exists INT> OUTPUT]
Example: For example, to check whether the file boot.ini exists on disk c: or not, run:
EXEC master..xp_fileexist 'c:\boot.ini'
from: (warning, PDF)
April 11, 2011 at 4:05 pm
The problem is that xp_fileexist executes under the security context of the sql user if the user does not have sysadmin role, and the sql user will not have permissions to see the files. If the user has sysadmin it executes under the context of the account that started SQL Server, which likely does have the needed permissions.
A workaround to allow non sysadmin users to exec procs like this correctly without granting them sysadmin is to create a signing certificate which has sysadmin and then add a signature by that certificate to a procedure that executes the xp_fileexist proc. With this method the procedure is executed with the permissions of the certificate and thus with the context of a sysadmin user. This is a method to selectively give non sysadmin users access to sysadmin functionality without granting them sysadmin role. As this exposes sysadmin level privileges to non sysadmin users it should be used judiciously and with careful consideration.
Here is all the code you need to create this. A caveat is that this will only execute with the output variable. If you want to execute with just passing in the filename create another proc that does not include the @File_Exists variable.
-- execute these command as a user with sysadmin role
USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'passw0rd';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'passw0rd';
GO
CREATE CERTIFICATE [cert_administration]
WITH SUBJECT = 'administration signing cert'
GO
CREATE LOGIN [cert_administration] FROM CERTIFICATE [cert_administration]
go
EXEC sp_addsrvrolemember 'cert_administration', 'sysadmin'
go
CREATE PROC sp__fileexist
@FileName varchar(255)
, @File_Exists INT OUTPUT
AS
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = N'passw0rd';
GO
ADD SIGNATURE TO [dbo].[sp__fileexist]
BY CERTIFICATE [cert_administration]
GO
GRANT EXEC ON sp__fileexist TO PUBLIC
GO
-- Then execute the sp__fileexist proc as the non sysadmin user like this.
DECLARE @file varchar(255)
DECLARE @file_exists int
SELECT @file='\\uncpath\filename'
EXEC master.dbo.sp__fileexist @file , @file_exists OUT
SELECT @file_exists
August 16, 2011 at 9:32 pm
This worked fine for me under SQL Server 2005 SE sp2 on Windows 2003 r2 as well as under SQL Server 2008 R2 on Windows 2008 r2 for SQL jobs. In both cases the SQL Agent service is running a domain account that has permission to the files and the job is running under the agent account.
This worked for local files as well as those on a share \\servername\sharename\filename.ext.
Even worked where one of the Windows 2008 R2 servers were setup under NTLM due to a backup app not being able to utilize kerberose.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply