Last week I had written a post on how to determine corrupt binary streams of images stored in a database: The case of the corrupt images. The main reason of that post was to determine the corrupt binary streams in the database before the SSRS reports accessing the inconsistent data while rendering reports and turning green in the face! So if you don’t have control over what is getting stuffed in the database (which is seriously a bad situation to be in), then here is another option for you if you are ready to use Custom Assemblies or Custom Code. I have decided to use a Custom Assembly for illustrating what I mean in this blog post. Ideally the image validation check should happen on the front end application/website from where the image is being uploaded into the database.
I am going to use a simple report which pulls a set of images from a database and displays them. I have two versions of the same report. The first report “Image_NoVerify” doesn’t use the custom assembly where as the “Image” report uses the fnImageValid function defined in VerifyImage.dll to determine the validity of the image.
The source code for the custom assembly is shown below:
/*
This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using System.IO;
using System.Security.Permissions;
namespace VerifyImage
{
public static class Class1
{
public static String fnImageValid(byte[] vImgStream)
{
// The file name to store the image at
String vFilePath = @"C:\1.jpg";
// File system permissions asserted to prevent security exceptions while saving the file
FileIOPermission filePerm = new FileIOPermission(System.Security.Permissions.PermissionState.Unrestricted);
filePerm.Assert();
// Check for nulls
if (vImgStream == null)
return "NULL image";
// Store the image on the filesystem
FileStream fs = new FileStream(vFilePath, FileMode.Create, FileAccess.Write);
try
{
// Zero KB image detection
if (vImgStream.GetUpperBound(0) > 0)
{
fs.Write(vImgStream, 0, vImgStream.GetUpperBound(0));
}
else
{
fs.Close();
return "ZERO KB Image detected";
}
}
catch (Exception)
{
fs.Close();
return "Unable to write image to disk";
}
fs.Close();
// Check image saved on filesystem for validity
try
{
using (FileStream fsRead = new FileStream(vFilePath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (StreamReader sr = new StreamReader(fsRead))
{
try
{
try
{
// Try and check if the seek is possible. If it fails, then we have problems!
fsRead.Position = 0;
fsRead.Seek(0, SeekOrigin.Begin);
}
catch { }
using (Image img = Image.FromStream(fsRead))
{
// IF there are no errors, then report success
img.Dispose();
fsRead.Close();
return "Valid Image";
}
}
catch (Exception)
{
fsRead.Close();
return "Corrupt Image Detected";
}
}
}
}
catch (Exception Ex)
{
return "Exception Caught: " + Ex.Message.ToString() + ":" + Ex.StackTrace.ToString();
}
}
}
}
You would also need to modify the rssrvpolicy.config file to ensure that the assembly doesn’t fail with permission errors when it is used while executing the report. Note that the values mentioned below is to get this working. You might need to make additional changes for making the security permissions more granular to satisfy the security requirements in your environment.
<CodeGroup class=”UnionCodeGroup”
version=”2″
PermissionSetName=”FullTrust”
Name=”MyCodeGroup”
Description=”Code group for VerifyImage extension”>
<IMembershipCondition class=”UrlMembershipCondition”
version=”2″
Url=”C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\VerifyImage.dll”
/>
</CodeGroup>
When the Image report is saved as a PDF file, you will see the following output for a valid image. The text box uses the following expression
to display the output shown in the screen shot on the left:
“=Is Image Valid: ” + Fields!SNO.Value.ToString() + VerifyImage.Class1.fnImageValid(Fields!Document.Value).ToString()”
If the image is inconsistent, then you will see one of the following outputs shown in the screenshots on the right. You can extend this further by displaying the image only if the function fnImageValid returns true. If the function returns a false, then you can use a stock image to let the user know that image is corrupt. This way you can prevent rendering failures.
As you can see from the data retrieved from the ExecutionLogStorage table in the ReportServer database that the image verification check at times can increase the execution time of the report. Since I am using only a few images to create the report, the execution times in the table mentioned below are comparable. However, if the images are larger or the number of images are more, then the report execution time will increase due to additional validation check processing which wouldn’t necessarily be a linear increase in execution time.
Note that invalid image streams when used in SQL Server 2008 Reporting Services and lower releases results in exceptions being thrown by Reporting Services and causes mini-dumps to be generated.
The DLL was placed in the following locations for the above example to work:
C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
I had achieved this using a Post Build event which would automatically copy the latest version of the DLL to the two above mentioned folders. This beats having to manually copy paste the DLL every time I build it.
References:
Custom Code and Assembly References in Expressions in Report Designer (SSRS)
http://technet.microsoft.com/en-us/library/ms159238.aspx
How to use custom assemblies or embedded code in Reporting Services
http://support.microsoft.com/kb/920769
TroubleshootingSQL 