ASP.Net MVC: HTTP 401 in /Reports Controller

January 15, 2016

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

Reporting Services Configuration Manager

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

IP Address and Port filter rules

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

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”

October 15, 2013

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)

June 18, 2013

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

June 17, 2013

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

April 23, 2013

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

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)

March 27, 2013

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

March 27, 2013

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.