Well, back to OC for stop #8....  Well, after making it to the correct California airport, I hopped the shuttle to the hotel...  only to find that they had not reserved a sleeping room for me and no rooms were available.  Great.

Thanks to Melissa (my logistics God) I managed to find a room a couple blocks away and get settled in.

Class in Irvine was really good.  We did some more talking about third party tools other then the SA tool (OfficeWriter) and the tool from Cizer(www.cizer.com) came up.  I've contacted the reps from Cizer and will be meeting with them in Dallas (next stop) to get a copy of the product and chat with them about where it fits.  Look for a preliminary review of the Cizer product in a week or so.

Well, next stop is going to be Dallas, back to Texas for me....  You know all this hopping time zones is really startig to get under my skin....  5 more stops and then home for a break to hang with the family...  not that I'm counting.

Well, to the windy city... Well, Arlington Heights actually, but close enough.

I must say, the break at home was great, but way, way too short.  Still, it was so nice to sleep in my own bed for a change.

The flights from St. John's to Chicago went way too well, so that causes me some concern.... The black clowd I seem to travel with is bound to catch up with me sometime.

So, my first sell-out in Chicago, a full class of 28.  I was lucky enough to get a great group in Chicago who really made me work for it.  Some great questions... and a great mix in the room, everything from PMs, to DBAs, to Power Users, to Developers.

Today's entry is around the questions I got on SQL injection attacks.  SQL injection is both one of the most serious riskes exposed by Reporting Services as well as one of the easiest to mitigate.

Let me start by setting up the issue for you.

This issue occures when the principle of least privilage is violated in a bad way.  In other words, one of the following senarios is present:

- The datasource is set to use integrated security and the user browsing the report is an administrator
- The datasource is set to prompt for credentials, and the user types in administrator level credentials
- The datasource is set to stored credentials, but has the administrator's credentials stored

So the long and the short is, that once these conditions are present, the attacker can then run code of thier choice by simply typing it in to a parameter text box.  Or the attacker can publish a report and convince somebody with the appropriate permissions to run it.

To solve the problem there are a couple things we can do.  First off, Don't allow reports to run with integrated security.  Create a least privilaged account in data source and use it to run your reports.  Then, turn off the ability for the Report Server to use integrated security period.  To do that, you'll want to change the EnableIntegratedSecurity system property.

The other thing you can do is not to trust user input.  There is no way in Reporting Services to validate a textbox between the parameter and the SQL, so place the SQL in a stored procedure where you'll have the chance to check that input before running with it.

Well, that's all for now, back down to Orange County for me....  At least I know the airport.

Well, I made it to Denver, after a detour through Orange County thanks to Expedia.

First off...  To the MCT (and xMCT) gang that happend to be in Denver at the time, thanks for the fun.  I haven't laughed like that in a very, very long time.  Rick, perhaps I'll start myself a new blog category so we can help you out with your door difficulties in future.

As for the class, it was a blast.  A bunch of you have asked how you can get the beta for Service Pack 2.  Well, after a bit of pestering, Microsoft gave me these instructions to share with you:

*snip*

Customers Signup Instructions to Participate in RS2000 SP2 Beta

1.  Log into http://www.beta.microsoft.com
2.  Log in using your passport account (if you do not have one, create one).
3.  If you are not a registered Betaplace user you will get a Guest ID screen.  Enter the following Guest ID (case sensitive), rssp2.
4.  If you are a registered Betaplace user you will go to a different screen than in #3.  Go to the Guest ID and enter the same Guest ID as in #3.
5.  Click on the “SQL Server 2000 Reporting Services SP2” Beta program link.
6.  Follow the instructions on the SQL Server 2000 Reporting Services SP2 page for completing the survey.

The person signing up should be receiving a welcome e-mail about 48 hours after registering.

*snip*

Well, Next Stop is HOME!!! 3 Days of time doing nothing but kicking back with Lorna and Shannon....  I can't wait!

Well, A couple lessons learned on this trip… Regardless of what the nice folks at Expedia may tell you, Orange County is NOT a suitable alternate airport for San Jose….  That mixup made for some interesting last minute flight figuring for sure.  Other than that, the San Jose group was one of the best so far.  Thanks guys, I had a blast.

Getting to the guts, Service Pack 2 for Reporting Services is on the way.  I posted a bug list the last episode, well this time I’m going to fill you in on the what’s new part.  The two largest additions are:

1. A set of SharePoint web parts.  In keeping with Microsoft’s strategy of transforming SharePoint to THE business portal, SP2 will include a set of SharePoint web parts for browsing and viewing reportis.
2. Client Side Print.  One of the biggest features missing was the ability to print directly from the HTML toolbar without having to first export to PDF or TIFF

The other question I was asked was the new report writer (the non Visual Studio one).  If you want to have a look at it, a MS webcast can be found at: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032259394&CountryCode=US

If you want to test your new skills, why not give a skills assessment a shot?  Go to http://www.microsoft.com/sql/reporting/ and click on the assessment

All for now, off to Denver.

Well, My travel luck continues.  Flight went well but the hotel was a little less than I had hoped.... (particularly after the $75 cab ride)

First room - springs stuck literally through the mattress
Second room - AC broken, only heated (set to heat or not).... room hovering around 90
Third room - everything was close enough to working.

However, it was great to meet up with Amber, Dale, Ron and company again.  We need to do it more often.

So, thanks again to the group for working through a less than ideal room (again)... and listening as I shouted over the AC.

So, the big question from that session, how do I add a "select all" to the drop down list box.  Well, it is a two step process.  First you have to add the select all, then you have to make it work.

To add the select all to the drop down, you'll first need to add it to the list.  If you're entering the list, simply add another option with a known value (such as -1)  If the list is dynamic, use a UNION to add the all option for example:

SELECT StateID, StateName FROM StatesLookup UNION (-1, 'All States')

So, after making this change, you'll have yourself an "all" option in your drop down list. (it won't work yet mind you)

The next step will be modifing your query parameter or filter to handle the all option.  If you're using query parameters, you'll probably start with SQL that looks something like this:

SELECT * FROM Sales, Customer WHERE Customer.State = @State

To handle the all option, you'll have to fix the WHERE part of the statement to test for the -1.  The fixed statement will look like this:

 SELECT * FROM Sales, Customer WHERE Customer.State = @State OR @State = -1

To answer a question before it is asked – No, this will not give the world’s best performance.  To get a better performing Query, the best option is to use a stored procedure. (for other reasons too related to SQL injection problems, but that is another story for another blog entry)

If you're using a filter, the logic would be similar, with the exception that you'd apply the OR to the filter expression.

Well, that's all for Houston, off to San Jose

Well the good luck finaly came to an end on the travel side thanks to an airline who shall remain..... DELTA.

Well, the hotel luck didn't work out too well either, with the Church of God youth conference showing up.  Thanks to everybody who tolerated the noise and made it a good class despite the frequent distractions.

Yes. Reporting Services Service Pack 2 is in Beta.  That is the good news.  The bad news is ... this is not a public Beta.  To see the list of fixes in Beta 2 go to: http://support.microsoft.com/default.aspx?scid=kb;en-us;889640

All for now, off to Houston

Well, I had a great time in Phoenix.  My great luck with airlines continued (knock on wood) and I made it in with no problem.  Kathleen and Jim, thanks for the hospitality on the weekend.

As for the class itself, it seems a good time was had by all.  I didn't get many questions to add to this space so if any of the attendees from Phoenix have any, feel free to fire them over.

Rob.

Well, The LA stop has been done for a little while.  Overall it was great.  My thanks to all of you for making the first stop on the tour a memorable one.

As far as questions....

Probably the best third party RDL generator for non-geeks I've found is OfficeWriter from Software Artisans : http://officewriter.softartisans.com/officewriter-250.aspx They have a trial version available, so check it out.

Some of you were after some extra OLAP reporting examples.  There are some available for download on the Reporting Services site at Microsoft: http://www.microsoft.com/sql/reporting/downloads/default.asp

If there were any other things that I said I'd post here and didn't, Email me.

First off, to all of you who attended the roadshow pilot in Seattle, I really enjoyed having the chance to speak to you.  Keith and I thank for your feedback.  Your comments will really help us get the show polished for the road.

As the tour progresses, I'll be posting links, answers to questions asked etc in this space.

If somebody asked a question that I said I'd look up and answer here, and you don't find the answer, ping me and get them up.

First off, here is a link to a SQL Reporting Services Licensing FAQ: http://www.microsoft.com/sql/reporting/howtobuy/faq.asp

Here is the RDL Spec.  Like I said, no secret sauce, but will cure your insomia problems:
http://download.microsoft.com/download/4/7/d/47d7d117-9f91-49ad-98d5-46aa6f3251a8/RDLDec03.pdf

Here is the MS site for the SQL Server Reporting Services Report Packs: http://www.microsoft.com/sql/reporting/default.asp

That is all for now.  Keep checking back as the tour progresses for more.

The complete tour schedule can be found on the workshop website, but I will be in the following cities:

Los Angeles 3-Feb-05 Crowne Plaza Hotel LAX
Phoenix 7-Feb-05 Wyndham Phoenix
Charlotte 10-Feb-05 Embassy Suites Charlotte
Houston 14-Feb-05 Hilton Houston Southwest 
Milpitas (San Jose) 17-Feb-05 Crowne Plaza San Jose / Silicon Valley
Denver 21-Feb-05 Four Points by Sheraton Denver Cherry Creek 
Arlington Heights (Chicago) 28-Feb-05 Sheraton Chicago Northwest
Irvine 3-Mar-05 Residence Inn Irvine John Wayne Airport/Orange County
Dallas 7-Mar-05 Doubletree Hotel Dallas-Campbell Centre
San Francisco 10-Mar-05 Venue TBD 
Bellevue 14-Mar-05 Embassy Suites Hotel Seattle-Bellevue
Manhattan 17-Mar-05 The Westin New York at Times Square
San Diego 21-Mar-05 Wyndham San Diego at Emerald Plaza

See you on the tour!!!

Well, I'm going on Tour!!!

I, along with Dandy Weyn (http://www.dandyman.net) will be touring the US presenting the Microsoft Partner Readiness Business Intelligence Reporting Services workshop.(http://www.msusapartnerreadiness.com/biworkshop.asp)

The agenda for the session is as follows:

DAY 1

• Module 00: Introduction
• Module 01: Reporting Services from 500 Feet
• Module 02: Authoring Basic Reports
• Module 03: Exploring Data Regions
• Module 04: Manipulating Datasets
• Module 05: Managing Content

Day 2

• Module 06: Administering Security
• Module 07: Administering Report Services Components
• Module 08: Programming Reporting Services
• Module 09: SQL Server 2000 Report Packs
• Module 10: Office 2003 BI from 500 Feet
• Module 11: Using Excel PivotTables and PivotCharts
• Module 12: Exploring Office Web PivotTables and PivotCharts

As I move around the country on the tour, I'll be posting answers to questions asked and usefull Reporting Services links here, so keep checking back!

(to be contunued)