XP_DirTree in SQL Server

Question

Variations to this have been asked. I have no problem searching a local directory with the below piece of code.

EXEC MASTER.sys.xp_dirtree 'C:\', 1, 1

When I switch the path to a network location the results are empty.

EXEC MASTER.sys.xp_dirtree '\\Server\Folder', 1, 1

I first thought maybe it was something to do with permissions. I added the SQL Server Service to the ACL list on the shared volume as well as the security group.

Any help or direction to point me in is greatly appreciated or even another way to get a list of files in a directory and sub directories.

[Edited]

Answer 1

The two things to look out for are:

• Make certain that the Log On account for the SQL Server service (the service typically listed as "SQL Server (MSSQLSERVER)" in the Services list) has rights to that network share.

  UPDATE

  The problem ended up being that the O.P. was running the SQL Server service as a local system account. So, the O.P. created a domain account for SQL Server, assigned that new domain account as the Log On As account for the SQL Server service, and granted that domain account the proper NTFS permissions.

  Please note that this might have also been fixable while keeping the SQL Service running as a local system account by adding the server itself that SQL Server is running on to the NTFS permissions. This should usually be possible by specifying the server name followed by a dollar sign ($). For example: MySqlServer01$. Of course, this then gives that NTFS permission to all services on that server that are running as a local system account, and this might not be desirable. Hence, it is still preferable to create a domain account for the SQL Server service to run as (which is a good practice in any case!).

  It sounds like this has been done, so it should be tested by logging onto windows directly as that account and attempting to go to that specific network path.

• Make sure that the Login in SQL Server that is executing xp_dirtree has "sysadmin" rights:

  • This can be done directly by adding the account to the sysadmin server role, or

  • Sign a stored procedure that runs xp_dirtree:

    • Create a certificate in [master]
    • Create a login based on that certificate
    • Add the certificate-based login to the sysadmin server role
    • Backup the certificate
    • Restore the certificate into whatever database has, or will have, the stored procedure that runs xp_dirtree
    • Sign the stored procedure that runs xp_dirtree, using ADD SIGNATURE and the certificate that was just restored
    • GRANT EXECUTE on that stored procedure to the user(s) and/or role(s) that should be executing this.

Just to have it stated, another option is to do away with xp_dirtree altogether and instead use SQLCLR. There is probably sample C# code on various blogs. There are also a few CodePlex projects that have file system functions and might also provide a pre-compiled assembly for those that don't want to deal with compiling. And, there is also the SQL# library that has several filesystem functions including File_GetDirectoryListing which is a TVF (meaning: you can use it in a SELECT statement with a WHERE condition rather than needing to dump all columns and all rows into a temp table first). It is also fully-streamed which means it is very fast, even for 100k or more files. Please note that the FILE_* functions are only in the Full version (i.e. not free) and I am the creator of SQL#, but it does handle this situation quite nicely.