TechEd 2006 Europe will take place in Barcelona, Spain (7 -10 November 2006). Barcelona is one of the cities I always wanted to visit and I hope I will be able to make it. Summer could have been much better for a fiesta, of course …
This article presents an excerpt from Teo’s book Applied Microsoft Analysis Services 2005. Learn how to author “smart” reports using Reporting Services (SSRS), Analysis Services (SSAS), and SQL Server CLR stored procedures. Get the reports demonstrated in this article by downloading the sample code.
Microsoft has just published an excerpt from my book “Applied Microsoft Analysis Services 2005” on TechNet. Learn how to integrate SSRS and SSAS to build smart reports using data mining and CLR stored procedures.
One of the most outstanding SQL Server 2005 feature is CLR stored procedures. A CLR stored procedure can encapsulate complex .NET code, process data, and pipe the results back to your report. The advantage of using CRL stored procedures is that from a reporting perspective things are very simple. The report just needs to call the CLR stored procedure, as it would call a regular SQL Server stored procedure.
One of my reporting requirements called for passing some XML metadata in the form of a serialized-to-XML object. The stored procedure had to deserialize the object before it can use it. In my case, the object used a custom XML serialization/deserialization by implementing IXmlSerializable interface. My first attempt to deserialize the object from the CLR stored procedure failed abysmally with the following exception:
Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information. —> System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
Initially, I couldn’t understand what was going on since I’ve given my assembly UNSAFE rights, which means that it should be able to do anything. Luckily, googling on this exception brought me to the Vineet’s post which hinted that for security and reliability reasons, SQL Server does not support loading assemblies dynamically. After some experimenting, I was able to fix the issue by following these steps:
1. Sgen the object assembly to produce a pre-generated XmlSerializers assembly.
2. Deploy both the object and XmlSerializers assembly to SQL Server with UNSAFE access. Strangely, I had to deploy also System.Drawing .NET assembly because SQL Server was asking for it.
3. In my CLR database project in VS.NET, add a reference to both the object assembly and the XmlSerializers assembly.
4. Inside the CLR stored procedure, instead of deserializing the object directly, I had to use the XML serializer from the XmlSerializers assembly.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void csp_MyStoredProcedure(SqlXml metadata)
{
// Deserialize the object using the XmlSerializers serializer
FooSerializer serializer = new FooSerializer ();
Foo f = (Foo)serializer.Deserialize(metadata.CreateReader());
. . .
}
So, you need to go through a few development, deployment, and management hoops just to deserialize an object in a CLR stored procedure. Not sure if security is a good justification for this effort though. Moreover, reflecting on the XmlSerializers assembly revealed that it is just a thin wrapper on top of the object assembly. One would say that you never have too much secuirty although, in this case, it certainly feels that way. Are we getting paranoid with security?
Recently, I’ve come across a question on the RS public newsgroup about how to use the new RS 2005 XML extension to report off relative nodes in an XML document. The attached sample demonstrates this scenario. For example, given this document:
<bookstore xmlns=”http://example.books.com“>
<book genre=”autobiography” publicationdate=”1981″ ISBN=”1-861003-11-0″>
<title>The Autobiography of Benjamin Franklin</title>
<author>
<first-name>Benjamin</first-name>
<last-name>Franklin</last-name>
</author>
<date>09-06-1956</date>
<samplechapters>1 3 4</samplechapters>
<price alternative=”discount”>5.99</price>
<price>8.99</price>
</book>
<book genre=”autobiography” publicationdate=”1972″ ISBN=”0399105573″>
<title>The Moon’s a Balloon</title>
<author>
<first-name>David</first-name>
<last-name>Niven</last-name>
</author>
<date>09-06-1974</date>
<samplechapters>4 5</samplechapters>
<price alternative=”discount”>1.94</price>
<price>2.57</price>
</book>
</bookstore>
The following query fetches the book/title, book/author/first-name, and book/date elements, as well as book/@publication date.
bookstore/book {title, author{first-name}, @publicationdate, date}
In my opinion, it could have been nice if you were able to use XPATHs to bring the elements needed instead of this pseudo syntax that relies on curly brackets. Another potential area of improvement is supporting XML schemas to cast the column types automatically according to the defined schema types. As it stands, the XML extension returns all columns as of string date type. However, you can explicitly cast the column type to another type. For example, assuming that the publication attribute returns a valid date:
bookstore/book {title, author{first-name}, @publicationdate(date), date}
will cast the publicationdate column to a date data type.
Perhaps, you’ve heard that Excel 12 will support publishing and executing Excel spreadsheets on the server through a server-based technology (currently dubbed Excel Services). My first reaction when I’ve heard about this was “who on earth would ever want to do this?” However, after reading the David Gainer’s excellent blog (will anyone compile these great notes into a book?) I got my “Eureka” and I have to report a mind shift. David is a Group Program Manager for Microsoft Excel. It is always great to see Microsoft engineers actively sharing first-hand knowledge so we can get the news straight from the horse’s mouth.
Here is what I think about Excel Services based on the David’s notes (I still have to try out my hypothesis). I find the Excel Services model very similar to the Reporting Services architecture. The Excel user authors the spreadsheet using Excel. Then, she can publish the spreadsheet manually or programmatically to the server (through Web service interfaces). The Excel team took the easy way out and decided to leverage SharePoint as a server repository which in this case fulfills the role of the RS report catalog. A published spreadsheet can be viewed in the browser. A special renderer is used on the server side to render the spreadsheet in HTML (IE 6+ only), just like a RS report can be rendered in HTML. Although the spreadsheet is rendered read-only, some interactivity is supported, i.e. the end user can change the PivotTable filter. Behind the scenes, when the user interacts with the spreadsheet, Excel calls down to a Web service to refresh the data (assuming data is retrieved from a data source).
Similar to RS, the user can pass parameters to the published spreadsheet to control the results of the calculations. Finally, a spreadsheet (or portion of it) can be secured using the SharePoint Windows-based security. So, in a nutshell, we are going to get Excel-based Reporting Services.
Is this a good thing? I think so, at least for a couple of reasons:
1. You can integrate server-based spreadsheets into SharePoint-based dashboards.
2. More interestingly, you could publish PivotTable spreadsheets that source data from SSAS 2005 cubes and disseminate them across the enterprise.
The second scenario needs more clarification. The Excel author could manually or programmatically create a PivotTable report connected to an SSAS cube. Then, she could publish the spreadsheet to the server (think of it as a canned report). Once this is done, the end user could request the spreadsheet in the browser. The end-user will be able to interact with the report or open it locally in Excel 12 to gain access to all PivotTable features (subject to security restrictions). Unfortunately, Excel Services will not support full interactivity. For example, the users cannot add or remove PivotTable fields (what a bummer?). In addition, not all SSAS features will be supported, e.g. actions and server drillthrough are not supported.
Despite its limitations, I believe Excel Services will enable new integration scenarios especially in the area of financial reporting. This comes to no surprise considering the fact that one of the most common requirements for financial reporting I hear nowadays is “make it work like Excel” 🙂
SSAS 2005 can be configured for HTTP access, e.g. for Internet reporting. Here are two whitepapers with step-by-step instructions for configuring Windows Server 2003 and Windows XP for HTTP access with SSAS 2005.
There is an extremely nasty new exploit that targets a vulnerability in Windows Fax and Picture viewers (WMF). Usually, I don’t pay much attention to the latest on spyware since IE always asks you to confirm download. But this one is really bad. No IE warnings or security toolbar. If you havigate to a rogue website that uses the exploit, it will bypass the IE security settings. By the time resident anti-spyware and virus shields catch it, it is too late and your machine will be infected, as shown here. For the time being, the workaound is to disable the viewers:
regsvr32 /u shimgvw.dll
Also, do yourself a favor and, if you are not doing this on a regular basis, enable Windows XP System Restore and create a restore point.
Microsoft has isued the following advisory about the new threat.
Microsoft Security Advisory (912840)
Vulnerability in Graphics Rendering Engine Could Allow Remote Code Execution.
Microsoft is investigating new public reports of a vulnerability in Windows. Microsoft will continue to investigate the public reports to help provide additional guidance for customers.
Microsoft is aware of detailed exploit code that could allow an attacker to execute arbitrary code in the security context of the logged on user when visiting a Web site, which contains a specially crafted Windows Metafile (WMF) image. An attacker would have no way to force users to visit a malicious Web site. Instead, an attacker would have to persuade them to visit the Web site, typically by getting them to click a link that takes them to the attacker’s Web site.
Customers are encouraged to keep their antivirus software up to date. The Microsoft Windows AntiSpyware (Beta) can also help protect your system from spyware and other potentially unwanted software. We will continue to investigate these public reports.
Upon completion of this investigation, Microsoft will take the appropriate action to help protect our customers. This will include providing a security update through our monthly release process or providing an out-of-cycle security update, depending on customer needs.
Microsoft encourages users to exercise caution when they open e-mail and links in e-mail from untrusted sources. For more information about Safe Browsing, visit the Trustworthy Computing Web site.
We continue to encourage customers to follow our Protect Your PC guidance of enabling a firewall, applying software updates and installing antivirus software. Customers can learn more about these steps at the Protect Your PC Web site.
Customers who believe they may have been affected by this issue can contact Product Support Services. You can contact Product Support Services in the United States and Canada at no charge using the PC Safety line (1 866-PCSAFETY). Customers outside of the United States and Canada can locate the number for no-charge virus support by visiting the Microsoft Help and Support Web site.
Mitigating Factors:
· In a Web-based attack scenario, an attacker would have to host a Web site that contains a Web page that is used to exploit this vulnerability. An attacker would have no way to force users to visit a malicious Web site. Instead, an attacker would have to persuade them to visit the Web site, typically by getting them to click a link that takes them to the attacker’s Web site.
· An attacker who successfully exploited this vulnerability could gain the same user rights as the local user. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.
· By default, Internet Explorer on Windows Server 2003, on Windows Server 2003 Service Pack 1, on Windows Server 2003 with Service Pack 1 for Itanium-based Systems, and on Windows Server 2003 x64 Edition runs in a restricted mode that is known as Enhanced Security Configuration This mode mitigates this vulnerability where the e-mail vector is concerned although clicking on a link would still put users at risk. In Windows Server 2003, Microsoft Outlook Express uses plain text for reading and sending messages by default. When replying to an e-mail message that is sent in another format, the response is formatted in plain text. See the FAQ section of this vulnerability for more information about Internet Explorer Enhanced Security Configuration.
General Information
Purpose of Advisory: To provide customers with initial notification of the publicly disclosed and exploited vulnerability. For more information see the “Suggested Actions” section of the security advisory for more information.
Advisory Status: Under Investigation
Recommendation: Review the suggested actions and configure as appropriate.
References | Identification |
CVE Reference | |
CERT Reference | |
Microsoft Knowledge Base Article |
This advisory discusses the following software.
Related Software |
Microsoft Windows 2000 Service Pack 4 |
Microsoft Windows XP Service Pack 1 |
Microsoft Windows XP Service Pack 2 |
Microsoft Windows XP Professional x64 Edition |
Microsoft Windows Server 2003 |
Microsoft Windows Server 2003 for Itanium-based Systems |
Microsoft Windows Server 2003 Service Pack 1 |
Microsoft Windows Server 2003 with SP1 for Itanium-based Systems |
Microsoft Windows Server 2003 x64 Edition |
Microsoft Windows 98, Microsoft Windows 98 Second Edition (SE), and Microsoft Windows Millennium Edition (ME) |
Note Microsoft Windows Server 2003 Service Pack 1 and Microsoft Windows Server 2003 x64 Edition also refer to Microsoft Windows Server 2003 R2.
What is the scope of the advisory?
Microsoft is aware of a new vulnerability report affecting the Graphics Rendering Engine in Microsoft Windows. This vulnerability affects the software that is listed in the “Overview” section.
Is this a security vulnerability that requires Microsoft to issue a security update?
We are currently investigating the issue to determine the appropriate course of action for customers. We will include the fix for this issue in an upcoming security bulletin.
What causes the vulnerability?
A vulnerability in the way that specially crafted WMF images are handled could allow arbitrary code to be executed.
What might an attacker use the vulnerability to do?
An attacker who successfully exploited this vulnerability could take complete control of the affected system. In a Web-based attack scenario, an attacker would host a Web site that exploits this vulnerability. An attacker would have no way to force users to visit a malicious Web site. Instead, an attacker would have to persuade them to visit the Web site, typically by getting them to click a link that takes them to the attacker’s site. It could also be possible to display specially formed Web content by using banner advertisements or by using other methods to deliver Web content to affected systems.
How could an attacker exploit the vulnerability?
An attacker could host a malicious Web site that is designed to exploit this vulnerability through Internet Explorer and then persuade a user to view the Web site.
I am reading e-mail in plain text, does this help mitigate the vulnerability?
Yes. Reading e-mail in plain text does mitigate this vulnerability where the e-mail vector is concerned although clicking on a link would still put users at risk.
Note In Windows Server 2003, Microsoft Outlook Express uses plain text for reading and sending messages by default. When replying to an e-mail message that is sent in another format, the response is formatted in plain text.
I have software DEP enabled on my system, does this help mitigate the vulnerability?
Yes. Windows XP SP2 also includes software-enforced DEP that is designed to reduce exploits of exception handling mechanisms in Windows. By default software-enforced DEP applies to core operating system components and services. This vulnerability can be mitigated by enabling DEP for all programs on your computer.
For additional information about how to “Enable DEP for all programs on your computer”, see the product documentation.
Microsoft has tested the following workaround. While this workaround will not correct the underlying vulnerability, it will help block known attack vectors. When a workaround reduces functionality, it is identified in the following section.
To un-register Shimgvw.dll, follow these steps:
1. Click Start, click Run, type “regsvr32 -u %windir%\system32\shimgvw.dll” (without the quotation marks), and then click OK.
2. A dialog box appears to confirm that the un-registration process has succeeded. Click OK to close the dialog box.
Impact of Workaround: The Windows Picture and Fax Viewer will no longer be started when users click on a link to an image type that is associated with the Windows Picture and Fax Viewer.
To undo this change, re-register Shimgvw.dll by following the above steps. Replace the text in Step 1 with “regsvr32 %windir%\system32\shimgvw.dll” (without the quotation marks).
· Microsoft encourages users to exercise caution when they open e-mail and links in e-mail from untrusted sources. For more information about Safe Browsing, visit the Trustworthy Computing Web site.
· Customers in the U.S. and Canada who believe they may have been affected by this possible vulnerability can receive technical support from Microsoft Product Support Services at 1-866-PCSAFETY. There is no charge for support that is associated with security update issues or viruses.” International customers can receive support by using any of the methods that are listed at Security Help and Support for Home Users Web site.
· All customers should apply the most recent security updates released by Microsoft to help ensure that their systems are protected from attempted exploitation. Customers who have enabled Automatic Updates will automatically receive all Windows updates. For more information about security updates, visit the Microsoft Security Web site.
· Protect Your PC
We continue to encourage customers follow our Protect Your PC guidance of enabling a firewall, getting software updates and installing ant-virus software. Customers can learn more about these steps by visiting Protect Your PC Web site.
· For more information about staying safe on the Internet, customers can visit the Microsoft Security Home Page.
· Keep Windows Updated
All Windows users should apply the latest Microsoft security updates to help make sure that their computers are as protected as possible. If you are not sure whether your software is up to date, visit the Microsoft Update Web site, scan your computer for available updates, and install any high-priority updates that are offered to you. If you have Automatic Updates enabled, the updates are delivered to you when they are released, but you have to make sure you install them.
Resources:
· You can provide feedback by completing the form by visiting the following Web site.
· Customers in the U.S. and Canada can receive technical support from Microsoft Product Support Services. For more information about available support options, see the Microsoft Help and Support Web site.
· International customers can receive support from their local Microsoft subsidiaries. For more information about how to contact Microsoft for international support issues, visit the International Support Web site.
· The Microsoft TechNet Security Web site provides additional information about security in Microsoft products.
Disclaimer:
The information provided in this advisory is provided “as is” without warranty of any kind. Microsoft disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall Microsoft Corporation or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if Microsoft Corporation or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.
Revisions:
· December 28, 2005: Advisory published
Thanks for Marco Russo’s blog, I’ve learned that Microsoft has released an updated version (version 1.5) of the Excel 2002/2003 Add-in for SQL Server Analysis Services. I discussed in details how version 1.0 could be used to generate ad hoc reports from SSAS cubes in my book Applied Microsoft Analysis Services 2005. Although version 1.0 was compatible with SSAS 2005, it was unaware of the new UDM features. For example, it couldn’t differntiate between attribute and multi-level hierarchies. The updated bits target both UDM and SSAS 2000 although not all of the new UDM features (e.g. drillthrough actions) are supported.
Easily overlooked, the version 1.5 of the Excel Add-in represents the first Microsoft OLAP browser that targets UDM. It is not perfect, but it is better than nothing. We can only expect the Excel/UDM integration to improve in Excel 12. So, I feel good about Excel analytics. Unfortunately, I don’t share the same feeling when it comes to integrating UDM with custom applications. In the past, Microsoft Office Web Components (OWC) has fulfilled the role of such a pluggable control. Unfortunately, its COM-based architecture has made it technologically obsolete. At this point, it is not clear what Microsoft plans for OWC and integrating UDM with custom applications.
In my opinion, it is optimistic to expect that all organization will embrace Excel as an OLAP browser. For example, if you develop BI applications, I doubt you will be willing to tell your customers that they need to launch Excel to whip out some cool interactive BI reports. So, I had a dream in which the MS Santa brings me a .NET-based control (let’s call it OlapViewer) which developers can use to integrate .NET applications with UDM. Similar to the ReportViewer VS.NET control, this control could ship with VS.NET. A successor to OWC, the OlapViewer would fully support UDM. It would allow developers to add business intelligence features to custom applications. For example, the developer could programmatically add measures and dimensions to the report. When the end user is done generating the report, she could save the report definition for a later retrieval. How about creating calculated members programmatically? Or, converting the OLAP report definition to a Reporting Services report? Intercepting and possibly changing the MDX query statement?
Alas, for now, this is only a dream. Hopefully, we will see it materialized sometimes next year either from Microsoft or third parties. “The future unknown is”, as the Jedi Master Yodda used to say.
