What is the best method for checking if a file exists from a SQL Server 2005 stored procedure?

Question

We used the "undocumented" xp_fileexist stored procedure for years in SQL Server 2000 and had no trouble with it. In 2005, it seems that they modified the behavior slightly to always return a 0 if the executing user account is not a sysadmin. It also seems to return a zero if the SQL Server service is running under the LocalSystem account and you are trying to check a file on the network.

I'd like to get away from xp_fileexist. Does anyone have a better way to check for the existence of a file at a network location from inside of a stored procedure?

Answer 1

Maybe a CLR stored procedure is what you are looking for. These are generally used when you need to interact with the system in some way.

Answer 2

You will have to mark the CLR as EXTERNAL_ACCESS in order to get access to the System.IO namespace, however as things go that is not a bad way to go about it.

SAFE is the default permission set, but it’s highly restrictive. With the SAFE setting, you can access only data from a local database to perform computational logic on that data. EXTERNAL_ACCESS is the next step in the permissions hierarchy. This setting lets you access external resources such as the file system, Windows Event Viewer, and Web services. This type of resource access isn’t possible in SQL Server 2000 and earlier. This permission set also restricts operations such as pointer access that affect the robustness of your assembly. The UNSAFE permission set assumes full trust of the assembly and thus imposes no "Code Access Security" limitations. This setting is comparable to the way extended stored procedures function—you assume all the code is safe. However, this setting does restrict the creation of unsafe assemblies to users who have sysadmin permissions. Microsoft recommends that you avoid creating unsafe assemblies as much as possible.

Answer 3

I still believe that a CLR procedure might be the best bet. So, I'm accepting that answer. However, either I'm not that bright or it's extremely difficult to implement. Our SQL Server service is running under a local account because, according to Mircosoft, that's the only way to get an iSeries linked server working from a 64-bit SQL Server 2005 instance. When we change the SQL Server service to run with a domain account, the xp_fileexist command works fine 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 these 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 CLR stored proc to my target database from Visual Studio and used this TSQL to execute from SSMS logged in 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 try a local file or a network file, I always get a 0. I may try again later, but for now, I'm going to try to go down a different road. If anyone has some light to shed, it would be much appreciated.

Answer 4

@Paul, that code looks like it should work. Have you tried putting some tracing in that method to make sure Convert.ToString(fileName) isn't somehow hosing the path?