SSRS - access to "master" Sys function xp_fileexist

[Colin Burton]

Hi All.

We are building a suite of reports using SSRS
We have one standalone report server
Our Data is in a separate MS SQL Server
Developing the reports in VS 2022
Most if not all reports are accessed via Stored Procedure on the database server

The issue I have come across is on one report that looks at one or more tables to extract the required product data
That works fine.
Each product has a unique product code.
We have a folder that contains images of each product ( named as Productcode.jpg )

To determine if the image file exists ( which is the point of this report - List all products that do NOT have an associated image file )
I have written a SQL Function as below

ALTER FUNCTION [dbo].[DoesFileExist]
(@file_path  as varchar(500))
RETURNS int
AS
BEGIN
	DECLARE @file_exists int;
	EXEC master.dbo.xp_fileexist @file_path, @file_exists OUTPUT;
	RETURN @file_exists;
END

The stored Procedure calls this function like this

Update #TmpFrames
Set FileExists =(Select [dbo].[DoesFileExist] (filepath) )
-- Now delete records where the frame Image exists
Delete From #TmpFrames Where FileExists = 1

When run from the stored Procedure this works 100 %

However when run on The SSRS or in VS2022 Preview The function seems to be returning a value of 0 indicating the file could not be found, hence the record is not deleted from the reporting table #TmpFrames

We believe this to be a security issue.
the credentials for the datasource are a specific reportingserver user, and that user has been given rights to execute xp_fileexist in the "master" Database

however we cannot get this to work as desired.
We do not really wish to go down the road of writing a CLR function as that has its own security issues
Does any one have any suggestions

Thank you

Colin

 

[fredericofonseca]

likely because of the perms of the user executing it.

try using sys.dm_os_file_exists instead

https://blog.sqlauthority.com/2017/...s-dm_os_file_exists-replacement-xp_fileexist/

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886