In many corporate environments, like mine, there is often a reason to separate your web servers onto a separate network that is outside a FireWall separating them from the rest of your Servers.  Often Security teams are very reluctant to open up any extra ports to the "inside world" than are absolutely necessary.  Obviously SQL Server is a vital part of most web applications, so a method of communication between the Web Server and SQL Server is set up.

On one of our applications we have give the users the ability to store files as attachments to records that are kept in a database.  The downside was that the admin tools are on the intranet, but the end result needs to be available to the internet web site.  I've never been a fan of storing Files in a database, so I began looking for a way to keep the images in a single location that was protected and hopefully on the file system.

Since the outside world can see the database, I decided to stream the files out through the port that was already open using CLR.  The only thing we needed to set up was access for the SQL Server Service account to the share where the files (in our case .pdfs) were stored.  Now I needed to set up a Function to call, and a ASP.NET page to call it.

The function turned out to be very short.  Just a single line and an extra import from the Visual Studio Template.  Obviously I can add some error handling, but I'll leave that as a further exercise. :)  When you create the function in SQL there are two things that you need to double check.  First, this procedure must run with EXTERNAL ACCESS permissions and the function should be created with a return type of varbinary(max).  The SQL default uses varbinary(8000) which will throw errors on larger files.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports system.io

Partial Public Class UserDefinedFunctions
  <Microsoft.SqlServer.Server.SqlFunction()> _
  Public Shared Function ReadFile(ByVal fname As SqlString) As SqlBinary
    ' Add your code here
    Return New SqlBinary(File.ReadAllBytes(fname.ToString()))
  End Function
End Class

The actual "meat" of the ASP.NET page ended up being just about as short.  Generally I'd use a Code Behind page, but for this example, the <script> tags are just as easy.  This page can be extended to check permissions using Windows Authentication, a Session Variable, or do a number of other things.  My version just serves up the file.  If you server more files types than .PDFs remember that you'll also need to change the content type in the page.  The code for showing the "Save As..." dialog box is also in here, but commented out.  The current code will display the file in the browser.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

<script runat="server">
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    'Prompt File Download
    'Response.AddHeader("content-disposition", "attachment; filename=fname.pdf")

    'Display in Browser with appropriate MIME Handler
    Response.ContentType = "application/pdf"
    Response.AddHeader("content-disposition", "inline; filename=fname.pdf")

    'Create your Connection and Command Objects   
    Dim conn As New SqlConnection("YourConnectionString")
    Dim cmd As New SqlCommand(String.Format("SELECT dbo.ReadFile ({0}')", YourFileName), conn)

    conn.Open()

    'Stream the contents of the File to the Browser
    Response.BinaryWrite(cmd.ExecuteScalar())
    conn.Close()

    'End the Response so the browser knows the entire file is there.
    Response.End()
  End Sub
</script>

About all you need to do is determine how to choose which file needs to be included.  Sometimes that means sending an ID to SQL and looking up the file name, and other times you'll want to pass in an file name directly.  Hopefully this will provide a relatively simple method for serving up content protected by a firewall on the internet.