xP_fileexist

  • 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

  • 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

  • Did you double checked that the account in question have sufficient right? I recomend to do the same (If not already done) as it looks like this account is experiencing privilage issue

  • 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.

  • 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.

  • 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

  • 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 .

  • 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

  • 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 🙁

  • 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

  • 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)

  • 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

  • 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