ASP.NET and Images on a Network Share
The school district I work for had an idea for an application. The need comes from several angles. First of all, lunch counts are all done by paper. Attendance is taken on paper, then it is sent to the Health Room, where tallies are made, and then everything is recorded into our Student Management System (SMS for short), and our Food Service app. Most (if not all) of our elementary class rooms will have Smart Boards next year. So we decided to write an app that would allow the students to "check in" in the morning on the smart board. The kids can go up to the board and select their lunch choice, and do attendance all at once.
Getting the app to work was the easy part. Our SMS keeps all of the students photos on a drive. I wanted to use those photos directly off of the SMS server, rather than copy them locally. So, I shared the folder that contained the pictures. Then I mapped a drive on the server, and voila. Everything didn't work.
Apparently, there are security restrictions that prevent a web app from using files on a network share. It took a lot of iterations to get where I wanted. I can post details if of the process if need-be.
Here's what I ended up with.
In SQL 2005, there is a little gem. OpenRowSet.
SELECT BulkColumn as 'Photo' FROM OPENROWSET(BULK N'p:\test.jpg', SINGLE_BLOB) AS Photo
This returns a single column with blob datatype. So, I can then bring it into an image handler and go crazy.
Well, I mapped my drive, and then ran that command, and got an access denied error. I realized that SQL Server runs under the local system account ("NT AUTHORITY\SYSTEM"). The mapped drive didn't exist under that identity. So, I then found a reference somewhere (I forget the link) that says you can map a drive to the system account if you schedule a command to run with the AT command.
So, I created a batch file that mapped the drive with the remote machine's authentication:
net use p: \\10.10.xx.xx\pics remotemachinepassword /user:remotemachinename\remotemachineuser
Then, I scheduled the AT command to run the batch file:
at 20:00 c:\mapit.bat
This forced the system local account to have the mapped drive. I then ran my SQL in my app that pulled the images over, and PERFECT!
So, using a little SQL Server magic and a mapped drive to the system local user, you can in fact, use a resource from a remote machine in an ASP.NET app.
I want to test the mapped drive without the SQL Server stuff. I'll get to that on Monday.