What is the best method to check for a file from a SQL Server 2005 stored procedure?

Question

We used the "undocumented" xp_fileexist stored procedure for many years in SQL Server 2000 and did not experience any problems with it. In 2005, it seems that they changed the behavior a bit to always return 0 if the executable user account is not a system administrator. It seems to return zero if the SQL Server service is running under the LocalSystem account and you are trying to check the file on the network.

I would like to get away from xp_fileexist. Does anyone have a better way to check for a file in a network location from a stored procedure?

Answer 1

Perhaps the CLR stored procedure is what you are looking for. They are commonly used when you need to interact with the system in some way.

Answer 2

You will need to mark CLR as EXTERNAL_ACCESS in order to access the System.IO namespace, however, along the way, this is not a bad way.

SAFE is the default permission set, but it is very restrictive. Using the SAFE parameter, you can only access data from the local database to execute the computational logic of that data. EXTERNAL_ACCESS is the next step in the permission hierarchy. This option allows you to access external resources such as the file system, Windows Event Viewer, and web services. This access to resources is not available in SQL Server 2000 and earlier. This set of permissions also restricts operations, such as pointer access, that affect the reliability of your assembly. The UNSAFE permission set implies complete build trust and thus does not impose Code Security Access restrictions. This parameter is comparable to how the advanced stored procedure function works — you think that all the code is safe. However, this option restricts the creation of unsafe assemblies for users who have sysadmin permissions. Microsoft recommends avoiding creating unsafe assemblies as much as possible.

Answer 3

I still think that the CLR procedure might be the best choice. So, I accept this answer. Nevertheless, either I am not so bright, or it is very difficult to implement. Our SQL Server service runs under a local account because, according to Mircosoft, this is the only way to get an iSeries server running a 64-bit instance of SQL Server 2005. When we change the SQL Server service to start with a domain account, the xp_fileexist command works great for files located on the network.

I created this CLR stored procedure and built it with the permission level set to External, and signed it:

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.Security.Principal; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void FileExists(SqlString fileName, out SqlInt32 returnValue) { WindowsImpersonationContext originalContext = null; try { WindowsIdentity callerIdentity = SqlContext.WindowsIdentity; originalContext = callerIdentity.Impersonate(); if (System.IO.File.Exists(Convert.ToString(fileName))) { returnValue = 1; } else { returnValue = 0; } } catch (Exception) { returnValue = -1; } finally { if (originalContext != null) { originalContext.Undo(); } } } } 

Then I ran the following TSQL commands:

 USE master GO CREATE ASYMMETRIC KEY FileUtilitiesKey FROM EXECUTABLE FILE = 'J:\FileUtilities.dll' CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY FileUtilitiesKey GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin ALTER DATABASE database SET TRUSTWORTHY ON; 

Then I deployed the CLR-stored copy to my target database from Visual Studio and used this TSQL to execute from SSMS registered with Windows Authentication:

 DECLARE @i INT --EXEC FileExists '\\\\server\\share\\folder\\file.dat', @i OUT EXEC FileExists 'j:\\file.dat', @i OUT SELECT @i 

Whether I tried a local file or a network file, I always get 0. I can try again later, but now I will try to go a different path. If someone has some light to shed, that would be very appreciated.

Answer 4

@Paul, this code looks as if it should work. Have you tried putting some traces in this method to make sure that Convert.ToString(fileName) not confusing the path anyway?