Okay, I'm pretty excited about this, cuz' it solves a big problem I had. Its
not pure Fusebox, so I marked it OT...
Here's the scenario. I've got a Fusebox app, that amongst other functions,
generates a fairly complex Crystal Reports report, using Crystal Reports 7
running on the webserver. That's not the 'free' Crystal Reports that ships
with CF. The server would print a CR report to a specific printer when
certain conditions were met, in this case, when a travel authrization was
approved. Pretty straightforward.
Well, heh, a new requirement crept its way into the scope. I had to have
this same made available to the individual clients! Now, if you've tried
integrating the Crystal Reports interface directly with a web app, it is a
booger.
For those of ya who don't know, with CR 7 (can't speak for CR 8), if you
call a CR report directly, as in www.foo.com/report.rpt , if the Crystal
Reports Web Server, and Image Server are running, the CR webserver will
attempt to install an ActiveX component that is a mini version of the
Crystal Reports Viewer. A Java version of the viewer is available for those
"No ActiveX" folks, and even a plain HTML with or without frames viewer can
be done; although these really are lame.
Well, the cool thing about the ActiveX version of the viewer is that there
is a toolbar with cool functions such as export to Word/Excel/Text, a print
button, etc. Not to mention, the report stays in the format you designed it
in, instead of being chopped up into an HTML table or something.
Okay, so whats the problem here? As configured, if you try to pull up a
report such as www.foo.com/report.rpt , the user will get hit with a
password authentication for the ODBC connection. Argh! You can pass the
username and password in the url, such as
www.foo.com/report.rpt?user=dumb&password=idiot , but what sort of serious
developer would pass an ODBC username and password in a URL?
If that ain't bad enough, if you want to pull up only a specific record in a
report, such as "SELECT * from Purchases where ID = #my_id#", you need to
pass the SQL paramater in the URL. Again, bad bad bad!
SoOoOo, here is what I came up with after a bit of experimenting...
This is for an NT environment, folks. That would be IIS 4, CF 4.5 Ent.Ed, MS
SQL 7...
I set the Crystal Reports Web Server and the Crystal Reports Image Server
services to run under a specific NT account name.
I created a login on MS SQL 7 for this same NT account.
I created a role in my database for this login, and made sure the role had
only SELECT permissions.
In Crystal Reports, I set the ODBC options to "Use Trusted Connection".
Now, when somebody visits www.foo.com/report.rpt , the permissions to the
ODBC connection are granted implicitly, since the CR service is running
under an NT account which has matching permissions in SQL 7. Wah-Lah, the
client has a nice report pop up in their browser!
Now, I've got a small fire to put out after this msg, but I'm gonna take it
to the next step. Its pretty lame that you have to put the sql syntax in the
URL, such as this real world example:
www.foo.com/reports/testreport.rpt?sf={reports.ID}+%3d+35 would retrieve
record # 35 from the Reports table. Now, I still need to pass the statement
to the CR server, but anybody could pick apart the syntax of the URL and
read somebody else's report by manually changing the 35 to whatever.
My plan is... In my database table, I'm going to create a new field thats a
UUID, and that field will be auto-generated when a new record is added. I'll
have a CF query that selects the UUID from the table when the ID = the ID I
want. Then, I'll change the call to the Crystal Report page to use the UUID
instead of the plain ID. That'll keep any nosey guessers out.
I'm not sure about how Crystal Reports would handle a URL hack such as the
infamous :DROP TABLE attack, but at least I could keep out nosey folks.
Also, this is deployed in our intranet, so its not as susceptible as a WWW
site...
Anyhow, that's it. Whoo that was a lot to type.
Alan McCollough
Web Programmer
Allaire Certified ColdFusion Developer
Alaska Native Medical Center
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
