ASP.Net MVC: HTTP 401 in /Reports Controller

A customer reported that an MVC application I develop fails when trying to access a page called “Reports”, which unsurprisingly resides under the URL /Reports below application root, implemented by the class ReportsController.

On a different installation, the error does not occur, but that installation is not installed in web site root – rather, the installation which allows /Reports to be executed is a web application inside a web site.

A web search led me to a comment on Stack Overflow, where the same problem had been reported 3 years ago:

Do you by any chance have SSRS installed/hosted on your QA server?

And as it happens, the machine in question had SSRS installed 😉

Time for some background information: When an HTTP request hits a Windows machine, IIS is not the first program to process it. Rather, the HTTP request is first handled by HTTP Server in http.sys. Applications register with the HTTP Server API if they process HTTP requests outside of IIS.

And so does SSRS!

http.sys stores a table mapping URL paths and their registered applications. Entering on the command line

netsh http show urlacl

results in a list of URLs registered with http.sys. The /Reports URL shows up both for ports 80 and 443:

    Reservierte URL            : http://+:80/Reports/
        Benutzer: NT SERVICE\ReportServer
            Abhören: Yes
            Delegieren: No
    Reservierte URL            : https://SomeServer:443/Reports/
        Benutzer: NT SERVICE\ReportServer
            Abhören: Yes
            Delegieren: No

See here for documentation of the netsh http command.

So there we have it: the web root directory /Reports is mapped to SSRS, for all IP addresses (and host names) on port 80 (http), and for a specific host name on port 443 (https).

The solution is now to run Reporting Services Configuration Manager and connect to the Reporting Services

Reporting Services Configuration Manager

Click Advanced, and find the IP Address filter set to “all assigned”

IP Address and Port filter rules

Click Edit and restrict the filter to either IP Address or Host Header Name

Edit HTTP Filter for Reporting Service

Note that you can also change the settings from the command line, using the netsh http commands delete urlacl and add urlacl.

Working around SSRS error “is ambiguous in the namespace”

I am developing a couple of SSRS reports which need to translate UTC dates into timezone-based date values based on a timezone parameter (tzid).

After I found this answer on SO I added System.Core 3.5 to the reports’ References, and added custom Code:

Public Function FromUTC(ByVal d As Date, ByVal tz As String) As Date
    Return (TimeZoneInfo.ConvertTimeBySystemTimeZoneId(d, tz))
End Function

and changed the Expression of each datetime field to

= Code.FromUTC(Fields!MyDateTimeField.Value, Parameters!tzid.Value)

Worked fine.

Then a production system was upgraded to SSRS 2012 (and thus VS SSDT 2010), and report deployment did not work anymore, as VS displayed the error messages when hitting Deploy

The definition of the report ‘/myreport’ is invalid

and opening the report

[rsCompilerErrorInCode] There in an error on line 1 of custom code: [BC30560] “TimeZoneInfo” is ambiguous in the namespace “System”

or, in German

“TimeZoneInfo” ist im Namespace “System” nicht eindeutig.

So there’s no deployment for you, sorryyyyyyy.

A bit of google-fu brought up this question on Social and this issue on Connect, which means that MS is sitting on this bug for half a year now, and the “workaround” seems to be to create your own SSRS assembly, and/or to use Reflection to access the TimeZoneInfo methods, as sketched here.

I did not want to give in so easily, so I tried and verified that the procedure described in Deploying SSRS Reports with PowerShell still works for SSRS 2012.

And a colleague found that you can still deploy to SSRS 2012 using BIDS 2008 if you set the project’s TargetServerVersion to “SQL Server 2008 R2 or later”.

Listing Fonts used in an SSRS Report (.rdl)

When deploying SSRS reports, one needs to check whether the fonts used in a .rdl report are actually installed on the SSRS server, as a developer may select fonts installed by software that is not available on the server (I’m looking at YOU, Arial Narrow).

A font declaration inside an .rdl file looks like this

<Style>
    <FontFamily>Arial Narrow</FontFamily>
    <FontSize>8pt</FontSize>
</Style>

and those elements reside in the

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">

namespace.

First, we load the .rdl as XML and declare the namespace

[xml] $rdl = get-content "c:\path\to\my.rdl"
$ns = @{ r ="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" };

As we need to select all Style elements containing the FontFamily AND the FontSize child elements, we query the XML

$rdl.Report.Body | select-xml "//r:Style[r:FontFamily and r:FontSize]" -namespace $ns

and then select the text values

    | foreach-object { "$($_.Node.FontFamily) $($_.Node.FontSize)" }

This returns a list of font specifications like this

Arial Narrow 8pt
Arial Narrow 8pt
Arial Narrow 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt
Arial Black 8pt

Grouping the list and counting the items

    | Group-Object | select-object name, count

returns

Name                                  Count
----                                  -----
Arial Narrow 8pt                         20
Arial Black 8pt                           6

So this is the script:

[xml] $rdl = get-content "c:\path\to\my.rdl"
$ns = @{ r ="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" };
$rdl.Report.Body 
    | select-xml "//r:Style[r:FontFamily and r:FontSize]" -namespace $ns
    | foreach-object { "$($_.Node.FontFamily) $($_.Node.FontSize)" }
    | Group-Object | select-object name, count

Deploying SSRS Reports with PowerShell

Deploying SSRS Reports can be a painful task. 2012 is a bit better than 2008. It’s even worse if you do not have BIDS installed. And more worse if you use shared data sources. (telling from my recent experience)

Fortunately I found this PowerShell script, but unfortunately it lacks any sort of documentation.

After a bit of googling, and removing the lines

if ([Convert]::ToBoolean($ConnProps.IntegratedSecurity)) {
    $Definition.CredentialRetrieval = 'Integrated'
}

which always caused the error

ForEach-Object : Die IntegratedSecurity-Eigenschaft wurde für dieses Objekt nicht gefunden. Stellen Sie sicher, dass sie vorhanden ist. Bei D:\projects\wms\Deploy-SSRSProject.ps1\Deploy-SSRSProject.ps1:150 Zeichen:19 +     ForEach-Object <<<<  {
+ CategoryInfo          : InvalidOperation: (.:OperatorToken) [ForEach-Object], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFoundStrict,Microsoft.PowerShell.Commands.ForEachObjectCommand

ForEach-Object : Property ‘IntegratedSecurity’ cannot be found on this object; make sure it exists
At D:\projects\wms\Deploy-SSRSProject.ps1\Deploy-SSRSProject.ps1:150 char:19
+     ForEach-Object <<<<  {
+ CategoryInfo          : InvalidOperation: (.:OperatorToken) [ForEach-Object], RuntimeException
+ FullyQualifiedErrorId : PropertyNotFoundStrict,Microsoft.PowerShell.Commands.ForEachObjectCommand

because my project uses SQL Authentication rather than integrated security to log on to the database.

I got the script to run using the following PS code:

$pwd = ConvertTo-SecureString "ThePassWord" -asplaintext -force
$cred = new-object System.Management.Automation.PSCredential "TheUser", $pwd
& ./Deploy-SSRSProject.ps1 -path "C:\path\to\my.rptproj" 
    -configuration "Debug" -credential $cred -verbose

where “Debug” is a configuration defined in the .rptproj.

After running the script, all .rdl files had the current timestamp on http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2fMyProject&ViewMode=Detail.

The shared data source in the Data Sources directory are not being updated because the command line parameter -OverwriteDataSources is not passed to the New-SSRSDataSource function.

Getting Started with Visual Studio 2010 and SSRS

If you wanted to develop for SQL Server 2008 SSRS, you needed to install BIDS 2008, which was essentially the Visual Studio 2008 shell plus some SSRS-related project types.

When migrating one of my SSRS projects to VS2010, I thought the only thing I needed to install additionally was SQL Server Data Tools and everything would be fine.

Oh noes! (If it was, I wouldn’t write this blog…)

I opened a solution containing an .rptproj file, and received the error message

‘name.rptproj’ cannot be opened because its project type (.rptproj) is not supported by this version of the application. To open it, please use a version that supports this type of project.

[Search mode on]

This answer on StackOverflow started my link hunt, which found the following blog posts (sure, there are even more out there), listed in no particular order

• SSDT – Installation confusion (probably the best explanation)
• When SSDT is not SSDT
• SSDT – What’s in a name?

Essentially, the point is, there are two products named SSDT: Business Intelligence and VS support for SQL Server development. If we want to develop for SSRS, we need the BI flavor, which is NOT part of SSDT for VS2010.

SSRS for BI is included in SQL Server 2012, and in SQL Server Express with Advanced Services.

Knowing all that, we can even claim that MSDN said so:

In the SQL Server 2012 installer, developers can install the BIDS tools for Visual Studio 2010 by selecting SSDT during installation.  The BIDS tools will run on Visual Studio 2010 SP1, as SSDT does. The SQL Server 2012 installer will also install SSDT’s prerequisites and a “stub project” that allows SQL Server 2012 users to acquire the latest SSDT version from the web.

In the web acquision [sic! ??] experience, however, the SSDT shell only includes the database projects component. Installing SSDT from the web will not install BIDS tools.

Multilingual Value Formats in SSRS (Part 2)

In my first post on this topic I showed how to create a CultureInfo parameter in a SSRS report, and pass the input value to the report’s Language property.

Now, in a more complicated setting, you may use formats that are not among the pre-defined format strings. In my case, I needed a DateTime format to produce day-of-week + month + date, which, depending on the culture’s format, should show as

• ddd. dd.MM.
• ddd. dd/MM
• ddd. MM/dd
• ddd MM/dd

Solution 2: LanguageId Parameter and Language Dataset

Therefore I created a table Language which contains the following definitions

Id	Name	CultureInfo	ShortDateFormat
1	English (UK)	en-gb	ddd. dd/MM
2	English (US)	en-us	ddd. MM/dd
3	German	de-de	ddd. dd.MM.
4	Japanese	ja-jp	ddd MM/dd

A report parameter @LangId is based on the query

SELECT Id, Name FROM Language ORDER BY Id

which allows the user to select one of the pre-defined languages in the database.

And here’s how to retrieve the other fields of the selected language:

I created another dataset (dsLanguageSettings) in the report which uses the query

SELECT CultureInfo, ShortDateFormat FROM Language WHERE Id = @langid

The query parameter @langid gets its value from the report parameter @LangId (Dataset Properties, Parameters).

Next, I added two more report parameters, @CultureInfo and @ShortDateFormat.

For both parameters, select

• Available Values: Get values from a query
• select dsLanguageSettings as DataSet
• select value and label fields (CultureInfo and ShortDateFormat respectively)
• Default Values: Get values from a query
• select dsLanguageSettings as DataSet
• select value field (CultureInfo and ShortDateFormat respectively)
• Advanced: Automatically determine when to refresh

The trick is the combination of Available Values and Default Values: as the user changes the value of the Language parameter in the report viewer form, the depending queries for @CultureInfo and @ShortDateFormat are refresh again, cause the Available Values to determine the values of the dropdowns, and the (refreshed) Default Values query to set the new value for both parameters.

As stated previously, the selected CultureInfo value is passed on to the report’s Language property.

The @ShortDateFormat can be applied like this:

• in Design mode, select the textbox containing a DateTime value
• right-click, select Text Box properties
• select Number, Category Custom
• click the fx button next to Custom format
• enter: =Parameters!ShortDateFormat.Value
• OK, OK

Done, the selected format will be applied. Of course, this method can be applied for any value in any culture-specific non-standard format.

Multilingual Value Formats in SSRS

SSRS renders DateTime and floating-point values according a given Culture depending on

• the Language definition of the report
• the browser’s language settings
• URL parameters
• the current day of week (and whether DST applies or not)

as stated in this TechNet article.

This is all a bit unreliable for my taste, so I looked for a more deterministic method to generate my reports. (I need to add that the reports I am working on already required a “language” parameter, since the data is stored in several languages, and the language parameter is needed to generate the correct data set.)

Solution 1: add a CultureInfo parameter

I added a text parameter name CultureInfo to the .rdl that requires the user to input a CultureInfo value (such as en-us, or de-de, etc.). For this CultureInfo value to be used as default format provider for the report, we need to assign the report’s Language property:

• select the report by clicking outside the page area in designer mode
• go to the property sheet, select “Language”
• click the dropdown button, select “<Expression…>”
• enter “=Parameters!CultureInfo.Value”

When previewing or running the modified report, the CultureInfo parameter needs to be set, and datetime and float values are displayed according to the culture’s formats.

Querying SSRS Reports, their Properties and Parameters

If you use SSRS’s WSDL interface, you have the ListChildren() and FindItems() methods to retrieve the uploaded report definition files (.rdl) in ReportService (2005), but I am not sure how to retrieve the parameter list.

Of course, there is the LoadReport() method in ReportExecutionService, the return value of which gives you access to the Parameter list, but that’s a different web service (URL and WSDL).

Originally I was looking for a solution that would involve only one of the two web services.

Back to square one: why not query the Report Service database directly? It contains a table [Catalog] which stores all reports (and more) along with their properties and parameters.

The Property and Parameter columns are defined as NTEXT, but their contents is XML, so we need to CAST() to XML because being able to access their contents.

The following TSQL example queries the reports for the language setting and the list of parameters:

with c as (
    select Name, Path, 
        CAST(Property as xml) as Property, 
        CAST(Parameter as xml) AS Parameter, 
        Type, CreationDate, ModifiedDate
    from Catalog
)
select Name, Path, Type, CreationDate, ModifiedDate,
    convert(nvarchar, 
        property.query('/Properties/Language/text()')) as Language,
    convert(nvarchar, p2.p.query('Name/text()')) as PName,
    convert(nvarchar, p2.p.query('Type/text()')) as PDatatype,
    convert(nvarchar, p2.p.query('Nullable/text()')) as PNullable,
    convert(nvarchar, p2.p.query('Prompt/text()')) as PPrompt
from c
outer apply Parameter.nodes('/Parameters/Parameter') as p2(p)
--where Type = 2

Notes:

• Use CROSS APPLY rather than OUTER APPLY to retrieve only report definitions with at least one parameter.
• Filter for Type according to this enum definition.
• Change NVARCHAR to NVARCHAR(length) if XML data is longer than supported by plain NVARCHAR.

Struggling with SSRS 2008 Error rsAccessedDenied on Win7

After successfully deploying a RDL report definition to SSRS 2008 running on Win7, I tried to browse the Report Server web under http://localhost/ReportServer but was greeted by the error message

Die dem Benutzer ‘machine\username’ erteilten Berechtigungen reichen zum Ausführen des Vorgangs nicht aus. (rsAccessDenied)

The permissions granted to user ‘machine\username’ are insufficient for performing this operation. (rsAccessDenied)

Navigating to the Report Manager under http://localhost/Reports/Pages/Folder.aspx gave me the message

Der Benutzer ‘machine\username’ verfügt nicht über die erforderlichen Berechtigungen. Stellen Sie sicher, dass ausreichende Berechtigungen erteilt und die Einschränkungen der Windows-Benutzerkontensteuerung (UAC) behandelt wurden.

User ‘machine\username’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

Of course, the suggested Online “help” had not only no useful information, but really contained *nothing*at*all*.

I found this blog on rcAccessedDenied but at first could not really figure out what it meant. Especially since the described workaround (run browser as administrator, also mentioned by an illustrated DBA tip) did not work for me. At least, I figured out that I needed a “real” administrator account to proceed.

So I started Control Panel, Computer Administration, Local Users and Groups and double-click the Administrator account. I activated the account and set a password.

After this, I managed to log in to the Report Server web using the Administrator’s credentials. There I could assign roles to my usual Windows user, but the user was still unable to connect to both sites.

Using the Administrator login I could at least grant rights to browse the data sources folder under http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2fData+Sources&ViewMode=List and the deployed reports under http://localhost/Reports/Pages/Folder.aspx?ItemPath=%2f[SolutionName]&ViewMode=List.

By the way, I tried running the browser as administrator on another computer (SQL Server 2012, SSRS, Win7) and was immediately able to browse the Reports/Pages/Folder.aspx. I really have no idea what’s going on here 😦

The Amazing FMTONLY Trick

I created a stored procedure to generate data for a report which involved several temp tables. To check the intermediate results, I thought I’d output the results of the various temp tables only during development, but skip the temp tables in production:

DECLARE @dev BIT = 0;    -- set to 1 during development
IF @dev = 1 SELECT * from @temp

This works fine in SSMS.

But trying to set the SP as report DataSource in Report Designer (.rdl Editor), VS offered the columns of the first SELECT statement, ignoring the value of @dev.

As you may know, when you define a data source in a VS IDE tool, the IDE invokes the SQL statement using the SET FMTONLY ON setting to retrieve information on the columns of the data source.

One feature of the FMTONLY mode is how IF statements are processed: BOTH the IF and the ELSE branch are analyzed independent of the result of the IF condition. This explains why the IF … SELECT always returns the column data of every SELECT statement in the SP.

I searched and found this article on SQLServerCentral on how to output only specific data sets in FMTONLY mode. What I did not know was that you can set FMTONLY anywhere inside a T-SQL procedure!

My variant of the code in the referenced article looks like this:

– Detect whether FMTONLY is set (only then the IF(1=0) will be executed):

DECLARE @fmtonlyON BIT = 0;
IF (1=0) SET @fmtonlyON = 1;
SET FMTONLY OFF;

– Create and SELECT temp tables as you like, IF statements will be handled correctly

– Reset FMTONLY where required

IF @fmtonlyON = 1 SET FMTONLY ON;

A comment on the article also mentions that this method allows #Temp tables in SPs without raising the error message

Msg 208, Level 16, State 0, Line {line}
Invalid object name '#TempTable'.

*Yes, the title of this blog was inspired by this great video. Enjoy 😉