Blog – Page 81 – Prologika

How Windows UAC Got Me Again

March 2, 2010/0 Comments/in Blog/by Prologika - Teo Lachev

User Access Control (UAC) is a feature of Windows Vista, Windows 7, and Windows Server 2008 that prevents you from running applications with administrator rights even if you are a local admin on the box. Although I have to admit I hate UAC, I am convinced that it’s better to have it around especially after my home desktop got infected a few months ago with a rootkit virus so badly that I had to restore the disk from a backup. So, UAC is a necessary evil. But since it’s relatively new, not many application developers test for UAC and this can get you as it happened in the following story.

A coworker complained that Reporting Services SharePoint integration doesn’t work on a distributed environment where SharePoint is installed on the front end Windows 2008 server and the SharePoint configuration database is installed on a separate database server. As usual, my first stop for troubleshooting SharePoint integration with Reporting Services was to examine the log file which gets generated after installing the Reporting Services Add-in for SharePoint, as I explained in this blog. On Windows Server 2008, the log file is located in the Temp folder of the user running the add-in setup, such as C:\Users\<user>\AppData\Local\Temp. To make the quest even more interesting, the AppData folder is actually hidden by default on Windows Server 2008 and Windows Vista/7 (another security gotcha), so you have to use Folder and Search Options to enable hidden files so you can see it. Are you having fun yet?

In this case, the log file showed an error:

User: SYSTEM

Installing Report Server feature.

Calling copyappbincontents command.

copyappbincontents command completed successfully.

Adding Report Server feature to farm.

Install error: The EXECUTE permission was denied on the object ‘proc_putObject’, database ‘IWSharePointCluster_Config’, schema ‘dbo’.

So, SharePoint can’t connect to the database server. What’s interesting is that the log file reported that the user is SYSTEM and not the logon of the interactive user. Luckily, Prash Shirolkar from the SSRS team blogged about this and provided a workaround for this issue which will get fixed in R2 RTM. Gotta love UAC…

New Reporting Services Data Providers

February 28, 2010/0 Comments/in Blog/by Prologika - Teo Lachev

Two new data providers will be included in the SQL Server 2008 R2 release of Reporting Services, Microsoft SQL Azure and Microsoft SQL Server Parallel Data Warehouse, that will let you report from SQL Azure and SQL Server 2008 R2 Parallel Data Warehouse (code named Madison) databases respectively. I decided to take the Microsoft SQL Azure data provider for a spin. As you probably know by now, SQL Azure is a part of the Microsoft Windows Azure SaaS cloud offering. SQL Azure lets you host your SQL Server relational databases in the Microsoft data centers that provide scalability and fault tolerance services. This is great for companies of all sizes which don’t have resources or expertise to purchase and configure expensive hardware and software in order bo build scalable and highly available solutions. IMO, SQL Azure will be increasingly important, especially after Reporting Services and Analysis Services become available on SQL Azure although Microsoft doesn’t say yet when this will happen. At this point, you can only use Reporting Services as a client to author reports from SQL Azure-hosted databases.

Strictly, speaking if your database is hosted in SQL Azure, you can use the SQL Server or OLE DB providers (already included in Reporting Services) to send queries to SQL Azure. Just like on-premise solutions, SQL Server Azure clients retrieve data using the native SQL Server Tabular Data Stream (TDS). However, if you decide to use the native SQL Server providers, you will run into some issues with retrieving SQL Azure metadata. Hence, Microsoft added the SQL Azure data provider. To test it out, I followed these steps:

I signed up for SQL Azure. If you MSDN Universal subscriber, you can sign up to test SQL Azure for eight months for free.
I deployed the AdventureWorksDW2008R2 and AdventureWorksLT2008R2 databases to my SQL Azure server. The Adventure Works database deployment scripts for SQL Azure are available on codeplex. They will create AdventureWorksDWAZ2008R2 and AdventureWorksLTAZ2008R2 database on your SQL Azure server.
Installed SQL Server 2008 R2 Release Candidate which is not publicly available.
Created a new report in BIDS. As usual, I added a data source and selected the Microsoft SQL Azure provider.
In the Connection Properties dialog box, I entered the name of my SQL Azure server and my standard security credentials (Windows security is not supported on SQL Azure). For some reason, the database dropdown is not automatically populated so I had to enter the name of the database AdventureWorksDWAZ2008R2 manually.
Once connectivity has been taken care of, life is as usual. The Graphical Query Designer (toggle the Edit as Text button if the generic query designer starts first) shows the database schema.
One thing that surprised me was the performance of data retrieval. Even when I opted to get all data from FactInternetSales, which has thousands of rows, the query executed pretty fast. One caveat though with this approach is that you have open port 1433 on your corporate network because this is the port SQL Server uses for client connections. This is not required if your application is installed on Windows Azure and connects to the SQL Azure database on the server.

Beware Hidden Calculated Members

January 18, 2010/0 Comments/in Blog/by Prologika - Teo Lachev

Here is another one of those “by design” issues (see the Not All Calculated Members Are Born Equal blog for another recent gotcha) that are not documented but can sure waste you precious time figuring out. The issue was first submitted on connect.microsoft.com by Greg Galloway almost a year ago where it was left in an investigation phase (what can we do to get the SSAS team actually review the connect cases and post findings?). Then, I submitted a similar issue and official support case since I was 100% sure it was a bug and I needed a closure. Alas, Microsoft came back to declare it “by design”:

“…per feedback from several AS 2005 Beta customers, for compatibility with pre-existing AS 2000 behavior, AS 2005 RTM (and later releases) was changed to not include hidden calculated members in cell calculations and scopes (except when a hidden calculated member is explicitly referenced in the calculation scope).”

The issue affects hidden calculated members and scope assignments where a scope assignment simply ignores hidden calculated members if they are not explicitly referenced by the script. You can easily repro the issue by making the following changes to the Adventure Works script:

/*– Allocate equally to quarters in H2 FY 2005 ————————*/

CREATE
MEMBER
CURRENTCUBE.[Measures].[Visible Quota] AS
null,

FORMAT_STRING = “Currency”,

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Sales Targets’ ;

CREATE
MEMBER
CURRENTCUBE.[Measures].[Invisible Quota] AS
null,

FORMAT_STRING = “Currency”,

VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = ‘Sales Targets’ ;

Scope

(

[Date].[Fiscal Year].&[2005],

[Date].[Fiscal].[Fiscal Quarter].Members

);

this = (ParallelPeriod

(

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember

), [Measures].[Sales Amount Quota]) * 1.35;

Here, I created two identical calculated members with the only difference that the first one is visible and second one hidden. Then, execute the following query:

SELECT {[Measures].[Sales Amount Quota], [Measures].[Visible Quota], [Measures].[Invisible Quota]} on 0

FROM [Adventure Works]

WHERE [Date].[Fiscal].[Fiscal Quarter].&[2005]&[1]

And here are the results (surprise!):

The Visible Quota member is populated as it should be but Invisible Quota refuses to budge as the script simply ignores it. If the script scopes on the Invisible Quota member explicitly or you make it visible, then we get the expected results. Since the issue is by design and a fix won’t be available anytime soon, you should be very careful when you use hidden calculated members and expect them to be affected by scope assignments. I use often helper calculated members which I hide since they shouldn’t be visible to the end users. This issue hit me really hard after QA reported wrong results and your humble correspondent had to debug a cube with some 1000+ lines of code.

Report Builder 3.0 Edit Sessions

January 18, 2010/0 Comments/in Blog/by Prologika - Teo Lachev

When Report Builder 2.0 shipped , it introduced local and server preview modes. If the report uses local references only, such an embedded data source(s), Report Builder 2.0 executes the query and renders the report locally just like the BIDS Report Designer. However, when the report uses a server reference, such as shared data source(s) or subreports that were saved on the server, Report Builder 2.0 would transparently upload and process the report on the server just like the Report Builder 1.0.

Report Builder 3.0 improves the performance of server preview mode by introducing edit sessions. An edit session is implicitly created when the user runs the report for the first time. Behind the scenes, Report Builder 3.0 calls the new CreateReportEditSession API to instruct the server to create an edit session. The server caches the report dataset(s) in the ReportServerTempDB database (not in memory) and uses the cached data for subsequent previews. In this respect, you can think of edit sessions as the *.data files that the BIDS Report Designer generates to cache the report data in order to speed up report preview. As long as you don’t make changes to the report that affect the data, such as changes to the dataset itself, data source, or parameters, the cached copy of the data is used by the report. This means that the user may not see changes that might have taken place in the database for the duration of the edit session. To make sure that the latest data is used, click the Refresh button on the Report Builder 3.0 toolbar. Refreshing the report discards the edit session and executes the database queries.

The administrator can configure two new server properties to manage edit sessions:

EditSessonCacheLimit – By default, the data cache can hold up to five datasets per report and user. Note that if the dataset is parameterized, each parameter combination counts as one dataset. If you use many different combinations of parameter values, the report might need more cached dataset copies. The administrator can allow this by increasing the EditSessonCacheLimit setting.
EditSessionTimeout – By default, an edit session lasts 7200 seconds (2 hours). The servers resets the session every two hours when the user previews the report.

Report Builder 3.0 edit sessions are a performance-related enhancement that makes server-side report preview more efficient.

Report Builder 3.0 Enhancements in R2 and SharePoint 2010 Mode

January 13, 2010/3 Comments/in Blog/by Prologika - Teo Lachev

A couple of Report Builder enhancements in SharePoint 2010 integration mode caught my attention. First, Report Builder 3.0 now launches by default when the Open in Report Builder action is initiated inside SharePoint 2010. Previously, Report Builder 1.0 would be launched.

Second, if the report includes a drillthrough action to another report, the action now works when the action is initiated from Report Builder 3.0 preview mode. As it turns out, a drillthrough action is forwarded directly to SharePoint. Previously, a report drillthrough action will result in an error because the Report Builder would attempt to resolve the action locally.

ReportService2010 Endpoint

January 13, 2010/0 Comments/in Blog/by Prologika - Teo Lachev

Here is a little gem for developers programming Reporting Services. SQL Server 2008 R2 brings a new web service endpoint, ReportService2010, which merges the functionalities of the ReportService2005 and ReportService2006 endpoints. The new endpoint can manage objects on a report server that that are configured for either native or SharePoint integrated mode. Previously, the ReportService2005 endpoint was used for managing objects on a report server that is configured for native mode and the ReportService2006 endpoint was used for managing objects on a report server that is configured for SharePoint integrated mode. This required code changes and retesting if the report server was re-configured in a different mode. These endpoints are now deprecated but still supported. The ReportService2010 endpoint includes the functionalities of both endpoints. When unsupported APIs in ReportService2010 are invoked, such as invoking the CreateLinkedReport API in SharePoint mode which doesn’t support linked reports, the endpoint will return an error that the operation is not supported.

Virtual Adventures

January 7, 2010/0 Comments/in Blog/by Prologika - Teo Lachev

Scenario: Mount base and differencing VHD disks using Windows Virtual PC on Windows 7 x64.

Dude, did you run out of BI stuff to talk about? I didn’t but I think other folks may benefit from my experience especially MCTs who have discovered that Windows 7 hasn’t happened yet to the Microsoft Official Curriculum and resorted to all sorts of hacks to get Virtual Server 2005 or Lab Launcher working with Windows 7. In the process, I’ve learned a lot about the virtualization technology that goes beyond just mounting disks. OK, I have a hidden agenda to write this blog as well – I want to document these steps.

So, I am preparing to teach 6326A – Implementing and Maintaining Microsoft SQL Server 2008 Reporting Services in an instructor-led environment where I have to install the required software on the student’s machine. After downloading the trainer resources, I have discovered that Microsoft provides 6236A-NY-SQL-01.vhd disk which requires a base disk Base08A.vhd. Reading the setup guide included in the course revealed that Microsoft wants me to install Virtual Server 2005 on both the instructor and students machines.

Gotcha 1: Virtual Server 2005 doesn’t run on Windows 7. Browsing the MCT private groups I came across posts discussing a hack around this limitation but I didn’t want go that way. Not to mention that Microsoft Lab Launcher, which apparently is intended to simplify the VHD deployment to student machines, doesn’t run on Win 7 as well. A double-gotcha which picked up my interest and stirred my hacking instincts to get the whole thing working with Virtual PC. After all, I can’t control what OS the students run or the training center has installed.

OK, but this is just VHD disk, isn’t it? So, it should run on Windows Virtual PC, right? So, I created a new virtual machine and attempted to mount 6236A-NY-SQL-01.vhd but I was greeted with the following informative error:

Cannot attach the virtual hard disk to the virtual machine. Check the values provided and try again.

Then I mounted Base08A.vhd and I was able to boot the VM only to find out that it includes the Windows Server 2008 OS only. Where is the cool SQL Server BI stuff? This is the part where I realized that my virtual knowledge needs brushing up. Enter differencing disks. This of course is not explained in the setup document, so don’t try to find it there. I figured that Base08A.vhd is the base disk that has the OS only while the 6236A-NY-SQL-01.vhd is the differencing disk that has the rest (SQL Server 2008 + Reporting Services samples and Adventure Works database) installed. To prove my hypothesis, I used the Sun’s VirtualBox. I use VirtualBox for my personal VM work because it supports x64 guess OS, which Virtual PC doesn’t support (shame). I used the Disk Manager to add both the base and difference images and … lo and behold, the VM booted up and I was able to verify that SQL Server 2008 and rest of the goodies are installed.

But I didn’t want to force the students to install VirtualBox so I continued hacking my way to Windows Virtual PC. In a Eureka moment, I examined the Windows Event log and saw the following message:

The parent virtual hard disk ‘C:\Program Files\Microsoft Learning\Base\Base08A.vhd’ for the differencing virtual hard disk ‘C:\VPC\3263\6236A-NY-SQL-01.vhd’ does not exist. Please reconnect the differencing virtual hard disk to the correct parent virtual hard disk.

So, the base disk path is hardcoded in the differencing image. The following commands confirmed this.

Went to command prompt and entered (the actual commands are in bold):
C:\>Diskpart

DISKPART> select vdisk file=”c:\vpc\6236\6236A-NY-SQL-01.vhd”

DiskPart successfully selected the virtual disk file.
DISKPART> detail vdisk

Device type ID: 2 (VHD)
Vendor ID: {EC984AEC-A0F9-47E9-901F-71415A66345B} (Microsoft Corporation)
State: Added
Virtual size: 64 GB
Physical size: 9 GB
Filename: c:\vpc\6236\6236A-NY-SQL-01.vhd
Is Child: Yes
Parent Filename: C:\Program Files\Microsoft Learning\Base\Base08A.vhd

Associated disk#: Not found.
I moved the based image (Base08A.vhd) to C:\Program Files\Microsoft Learning\Base. With a baited breath, I repeated the process of creating a new virtual machine in Windows Virtual PC pointing to the existing 6236A-NY-SQL-01.vhd but I got the first error:
Cannot attach the virtual hard disk to the virtual machine. Check the values provided and try again.

No more details in the Windows event log. At this point, I posted a question to the Windows 7 Virtualization discussion forum and I was clued in the disk virtual size (not physical size) could be the issue since Virtual PC doesn’t support disks larger than 127 GB. Although after attaching the 6236A-NY-SQL-01.vhd using the Windows 7 Disk Manager I could see that the virtual size is 64 GB, I compacted the base disk using these steps. That didn’t help and the hour was getting late. So I threw the white towel on the differencing disk approach (for now) and decided to merge the differencing disk into the base disk.
Assuming the commands in step 1), I executed the following commands:
DISKPART>detach vdisk

DiskPart successfully detached the virtual disk file.
DISKPART>merge vdisk depth=1
100 percent complete
(after a long wait …)
DiskPart successfully merged the differencing chain.
Now that I had the disks merged I was able to create a new virtual machine successfully using the base disk image (Base08A.vhd) which combined both the original and differencing disks. Since the differencing disk was merged, I could delete 6236A-NY-SQL-01.vhd.

Gotcha 2: After booting the virtual machine, I was able to log in using the pre-defined Student account. However, attempting to run the SQL Server Management Studio, I got an error that the SQL Server 2008 evaluation period has expired. I attempted to reset the Windows Server 2008 (guest OS) system date, but after a few seconds the date would revert to the present date. More binging the Internet solved the issue.

Shut down the VM.
Open the vmc file in Notepad.
Chang the last six digits to a year ago (070109)
<time_bytes type=”bytes”>57002400060004070109</time_bytes>
Further down the file, disable guest-to-host OS time synchronization:
<host_time_sync>
<enabled type=”boolean”>false</enabled>
<frequency type=”integer”>15</frequency>
<threshold type=”integer”>10</threshold>
</host_time_sync>
Boot VM. Now change the system date to 07-01-09.
Success!

Most Requested Features

December 21, 2009/0 Comments/in Blog/by Prologika - Teo Lachev

You can use the Microsoft Connect website to find most requested features. Unfortunately, the search doesn’t let you specify a product so the search results may be related to other products. For example, searching on reporting services may bring in results from Analysis Services and reporting. Nevertheless, it was quite interesting to find the top voted suggestions. For example, the following query shows the top suggestions for Reporting Services (flip to the Suggestions tab):

https://connect.microsoft.com/SQLServer/SearchResults.aspx?KeywordSearchIn=2&SearchQuery=%22reporting%22+AND+%22services%22&FeedbackType=1&Scope=0&SortOrder=10&TabView=0&wa=wsignin1.0

Reporting Services-Recognize multiple result sets returned from a stored procedure – (50 votes)
Merging / Linking datasets on report level (50 votes) – No 6 on my SSRS Top 10 wish list.
SQL Reports should support stylesheets (43 votes) – No 9 on my list.
Support for XML Paper Specification (XPS) Output Format (29 votes) – I am personally surprised about this one.
Reporting Services Security Using Membership and Roles (29 votes), and so on

Running ReportViewer Under Trusted Account

December 16, 2009/0 Comments/in Blog/by Prologika - Teo Lachev

Scenario: You have an Internet-facing web application that uses the Visual Studio ReportViewer control to render reports. The application takes care of authenticating the users. You want all report requests to the report server to go under a single (trusted) Windows account. There are at least two approaches to implement this scenario:

Basic Authentication

You can configure ReportViewer for basic authentication by implementing the Microsoft.Reporting.WebForms.IReportServerCredentials interface, as follows:

[Serializable]

public class MyReportServerCredentials : IReportServerCredentials

{

public MyReportServerCredentials()

{

}

public WindowsIdentity ImpersonationUser

{

get

{

return null;

}

public ICredentials NetworkCredentials

{

get

{

return new NetworkCredential(

ConfigurationManager.AppSettings[“reportUser”],

ConfigurationManager.AppSettings[“reportPassword”]

, “<your domain here>” – comment out this line if a domain account is used as a trusted account

);

}

public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority)

{

authCookie = null;

user = null;

password = null;

authority = null;

return false;

}

Specifically, you need to implement the NetworkCredentials property of IReportServerCredentials. In my case, I read the trusted account credentials from web.config. Once IReportServerCredentials is implemented, you need to configure ReportViewer to use it. This takes one line of code:

reportViewer.ServerReport.ReportServerCredentials = new MyReportServerCredentials();

The advantage of using IReportServerCredentals if flexibility. You can retrieve the account credentials from any place, such as security service, configuration file, database, etc. In addition, the impersonation is scoped for ReportViewer only. Other network calls made by the applications are unaffected.

Process Identity

The issue with IReportServerCredentals is the application must deal with the credentials of the trusted account, including the password. For added level of security, I try my best to avoid handling passwords and use Windows authentication whenever I can. Therefore, my preferred approach is to change the process identify of the web application. One way to do this is to change the identity of the IIS application pool the web application is running under, as follows:

If you don’t have a domain account to use as a trusted account, create a new domain account.
On the web server, add the account to the IIS_WPG local Windows group to give it the necessary rights to be used as an application pool account.
In the IIS Manager, expand the server name, right-click the Application Pools folder, and click New Application Pool.
Give the new pool a name. In the pool Properties page, click the Identity tab, select the Configurable option, and enter the credentials of the trusted account.
Open the web application properties and change its application pool to the pool you just created.
If present, remove <identity impersonate=”true”/> element from the application web.config file so the network calls are made under the process identify instead of the anonymous user account, such as IUSR_<MACHINE_NAME>.
At this point, if you run the web application, you may get a permission error because the domain account doesn’t have the necessary rights to specific folders, such as the web application folder and the temp folder. Using Windows Explorer, grant the domain account (or IIS_WPG group) rights to these folders. The error message should include the path of the folder you need to grant access to.
Finally, use the Report Manager to grant the necessary rights to the trusted account, such as Browser, to the required folders.

Infragistics Silverlight for Analysis Services

December 9, 2009/0 Comments/in Blog/by Prologika - Teo Lachev

Don Demsak pointed out another Silverlight-based control from Infragistics, which is currently in a CTP phase. There are actually two controls: XamWebDataChart and XamWebPivotGrid. I played a bit with it and it looks great. I liked the Excel-like paradigm with the metadata pane on the right and the PivotChart and PivotTable synchronized. As I’ve been saying – give me a Silverlight-based Excel Pivot and I am happy.

See my previous blog about the Intelligencia for Silverlight which is another Siliverlight-based control for Analysis Services.