SQL Pass presentation: Management, Configuration, Security
It's been a little while since my last post. I wanted to provide additional information related to my presentation to day at SQL PASS. The talk was extremely well attended - the room was full and standing room only. I hope you got what you were looking for out of the talk - feel free to drop me a line with comments.
Unfortunately, due to a technical glitch (err. blue screen of death! Yikes!), we could not record the demos. So I wanted to provide to you some of the demo materials so you can try to recreate the demos.
Demo 1: Configuration
Just install a report server instance with the files only option. Then in the start menu navigate to SQL Server, find Configuration Tools and run the Reporting Services configuration manager. You should be able to step through the tool to get the same experience as I had.
Demo 2: Security
Start with the report server from Demo 1. You'll need to create a local, low-privilege account (I called it Demo) on your computer. Run one browser (call it browser 1) window as yourself and point it to the report manager virtual directory you just created. See that you can create folders. Run another browser (call it browser 2) as the demo account and try to access the same virtual directory. You should see nothing. In browser 1, navigate to Home, click properties, click security, and then add a role assignment for the user "Demo" and assign them the browser role. Refresh browser 2, you should see access. If you play around with various item types, you should see that you in browser 1 can see many properties not visible to the user Demo in browser 2. In browser 1, you can drill into the various role definitions, look at task, try creating a new role, etc.
In this demo I also covered that users only need access to the item you're using, even if it depends on another item. So a user only needs to be able to see a report and does not need any permission on the shared data source it uses.
Demo 3: Report and Model Management
Kind of wide ranging demo - started looking at properties for a report. Talked about custom properties, and property extraction from RDL. Spent some time demo'ing model item security and drill through reports.
I'll post the actual files after I get back from vacation, but the prep work for the demo is as follows:
Open the adventure works model sample in model designer. Navigate to the SalesOrder entity. In the list of fields, right click and add a new filter. Define the filter on the OrderDate year property so that you're filtering only years greater than or equal to 2003. Save, then rename the filter to "NewOrders". Do the same steps but create a filter for less than 2003 and name it "OldOrders". For each filter update the properties to ensure it is nullable (the order date is nullable by default for some reason in the sample, so this is necessary). Next click on the OrderDate entity and in the properties window expand the SecurityFilters options. Add the NewOrders and OldOrders filters to the list. Save and deploy your model to the report server.
Ok, now in Management Studio, connect to RS and open the properties for the adventure works sample model. In Model Item Security, first assign permission to "Everyone" at the root. On the SalesOrder entity, select the OldOrders filter and set a custom policy on it to only include you.
Now create a report using report builder that uses the SalesOrder entity. Save the report to the report server. In Browser 1 view the report, see that you can see all the sales orders. In Browser 2, view the report and see that you can see only the new orders.
For the drill thorough report, I create a basic report on the products entity using report builder - I included the name, the product id and some other miscellaneous information. I saved the report to the file system. I opened the file in report designer as an existing item. I then added some pictures, changed the color. I published the report to the report server (I did all of this before the demo!). Then in the demo, I showed a simple report built on the ProductSubcategories entity. I showed the default drill through experience. Then, in management studio I again when to the properties of the adventure works model, chose drill through reports, chose the Product entity, and set the multiple instance report to the customized report I published. Then I again when to the original report and drilled through.
Demo 4 (Yes still more demos!): Schedules and Subscriptions
I had these all prepared. I simply used the Employee Sales Summary sample report for the data-driven subscription and the Product Catalog sample report for the standard subscription. The key thing is I needed to update the shared data source they reference to store my credentials before creating the subscriptions and to use the credentials as windows credentials.
The d-d subscription was easy to create because I use the adventure works shared data source with the modification above. For the query, I used the Sales.vSalesPeople view in a simple query. I then used the email address and employee id fields to customize the reports being delivered.
Since I didn't have an SMTP server running, ahead of time I switched the email delivery provider to use a pickup directory. I also shortcutted the delivery of e-mails by using the trick of starting the Agent Jobs manually thorough management studio.
Demo 5: (yup, keeping on trucking) Scripting, I think
I wanted to show properties being updated, so I chose the problem of updating printer margins and page settings on a linked report. The script I created was simple:
Dim Reset as Boolean = False
Sub Main()
Dim sourceReport As String
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim pageProperties() As [Property] = GetPageProperties()
If (Not Reset) Then
sourceReport = rs.GetReportLink(LinkedReport)
Console.WriteLine("Linked to: {0}", sourceReport)
Dim sourceProperties As [Property]()
Dim curProperty As [Property]
sourceProperties = rs.GetProperties(sourceReport, pageProperties)
pageProperties = sourceProperties
For Each curProperty In sourceProperties
Console.WriteLine("Property: {0} Value:{1}", curProperty.Name, curProperty.Value)
Next
End If
rs.SetProperties(LinkedReport, pageProperties)
End Sub
Function GetPageProperties() As [Property]()
Dim PageHeight As [Property] = New [Property]
Dim PageWidth As [Property] = New [Property]
Dim TopMargin As [Property] = New [Property]
Dim BottomMargin As [Property] = New [Property]
Dim LeftMargin As [Property] = New [Property]
Dim RightMargin As [Property] = New [Property]
PageHeight.Name = "PageHeight"
PageWidth.Name = "PageWidth"
TopMargin.Name = "TopMargin"
BottomMargin.Name = "BottomMargin"
LeftMargin.Name = "LeftMargin"
RightMargin.Name = "RightMargin"
Dim pageProperties() As [Property] = {PageHeight, PageWidth, TopMargin, BottomMargin, LeftMargin, RightMargin}
Return pageProperties
End Function
and I ran it with the following command line
rs.exe -i "C:\Documents and Settings\lukaszp\Desktop\SetLinkReportPageProperties.rss" -s https://localhost/reportserver -v LinkedReport="/Link to Landscape"
Of course ahead of time, I create a simple report that had 2 text boxes on the opposite ends of a page with the correct portrait page height/width set in the properties of the report. I published that and created a linked report at the root named "Link to Landscape".
In the demo, I showed the print control preview to show how the original had the right settings and the linked report had the wrong settings. I then ran the script again to show how it all worked afterwards, kinda cool.
Demo 6: (Ok I promise to finish the talk eventually!) Execution Log
Ok, at this point I was way behind schedule, but no one was leaving. So I went light here. I showed the database table in the report server database named ExecutionLog and described the fields. I discussed but did not show the Integration Services package - it is in the samples that ships with 2005. I then showed the sample execution log reports.
Quick and dirty demo to end, but I needed to cover scale out and encryption keys ;-)
Hope you liked the talk as much as I liked giving it.
Take care and good luck,
Lukasz
P.S. happy Thanks Giving