Category Archives : Programming

This category is designed for entries which relate to software development


SQL Server Data Tools for Visual Studio 2013 – Report Sizes

Today I started down the path of getting the previously-named “Business Intelligence Studio” (now called SQL Server Data Tools) up and running on my local machine.  I already had  SQL Reporting Services (SSRS) 2014 installed, but had just never gotten around to installing the BI development suite.

I’m going to be honest and state upfront, it’s been a while since I’ve stuck my head into SSIS and SSRS development – about four years or so I’d guess.  Remarkably, on the surface it doesn’t have appeared to change that much.  In my journey I encountered an interesting issue out of the box, so I thought I’d document it here.

Firstly – what are you installing?  I found this quite a decent summary:

There are different versions of these SSDT tools depending on the version of Visual Studio that you are using.

  • SQL Server tooling in Visual Studio 2013—Visual Studio 2013 Express for Web, Express for Windows Desktop, Professional, Premium, and Ultimate include the SSDT tools. You don’t need a separate download. To check for the latest version of SSDT, open Visual Studio 2013 and choose the Tools, Extensions, and Updates menu. Then check the Updates section for Microsoft SQL Server Update for database tooling.
  • SSDT Visual Studio 2012—There is a standalone install experience as well as an integrated install for the Visual Studio Professional, Premium, and Ultimate SKUs.
  • SSDT Visual Studio 2010—This version of SSDT is no longer being updated.

The other version of SSDT is confusingly called SQL Server Data Tools – Business Intelligence (SSDT-BI). Although their names are almost identical, SSDT-BI is a distinctly different toolset than SSDT. SSDT-BI is the replacement for BIDS and it enables the development of Integration Services packages, Analysis Services cubes, and Reporting Services reports.

Both versions of SSDT are no cost downloads for SQL Server users. You can find both SSDT and SSDT-BI at Microsoft SQL Server Data Tools.

Source: http://sqlmag.com/sql-server-2014/sql-server-data-tools-sql-server-2014

Here’s some direct links:

Once you’ve installed the right version (I’m running SSDT – VS 2013), you’ll find it installed as “SQL Server Data Tools”.  Oddly, it also seems to insall the VS 2012 IDE shell, but runs in the VS 2013 IDE shell.

image

So I ran up Visual Studio and started a new Reporting project. 

image

The first thing I did was created a new Report (blank) and then dragged some fields onto the report form.  I also added an image, just for fun.  I had to configure the project’s Target URL (as localhost) and ultimately had to use a browser (as Administrator) to grant my local account credentials to publish and run reports.

Eventually I managed to get past the permissions issues, and almost deployed my almost-blank report until this happened:

“System.Web.Services.Protocols.SoapException: There was an exception running the extensions specified in the config file. —> System.Web.HttpException: Maximum request length exceeded.”

I thought to myself, well, that’s odd.  It’s almost a blank report.  It was the configuration of the SSRS server, which was limiting report sizing to the default (MaxRequestLength where its default value is 4096 KB – 4 MB).  SO I had a look at the debug folder and lo and behold, once encoded this .rdl file would be over the 4 MB threshold, because of the embedded image:

image

Steps to change the http request limit:

    1. Go to the SSRS folder location on the Report Server

    (Example-> C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer)

    2. Open the File web.config in Notepad (or a text editor)

    3. Locate the line <httpRuntime executionTimeout=”9000″/>

    4. Modify it to <httpRuntime executionTimeout=”9000″ maxRequestLength = “16384” />

    5. Restart Reporting Services

Now you should be able to deploy a larger report definition.


Identity Server – An Introduction

Introduction

In recent times, I’ve become very intimately acquainted with OpenID Connect, OAuth2 as well as SAML, JWT, WS-Federation and more.  It’s a complicated world.

Since I dwell amongst the Microsoft ecosystem, I’m very experienced with Active Directory Federation Services (AD FS) which in its latest version supports OAuth2 endpoints as well as the more traditional (and dated) SAML 2.0 and WS-Federation protocols.

AD FS 3.0’s OAuth2 implementation is fairly limited, to be polite.  As modern applications such as Single Page Applications (SPA), WebAPI services and mobile applications advance, security capabilities must scale accordingly.  To augment AD FS there are two additional options – .NET DotNetOpenAuth and Thinktecture IdentityServer of which the current version is Identity Server 3.

Identity Server 3 (ID3) is the platform we’ve selected recently to help expand ADFS authentication capabilities, and the basis for this article.  ID3 already has very decent documentation, but I’ll need to borrow some of it to help frame the introduction in this article.

The following diagram neatly illustrates the role that ID3 plays between users, clients and applications/resources:

OpenID Connect
Source: http://identityserver.github.io/Documentation/docs/overview/terminology.html

The next few headings help to define the roles involved in applying security principles using ID3 and identity providers.  I’ve referenced a limited scope here as in my next article I’m going to paint a scenario and document an interesting solution I designed recently to solve a tricky problem involving ID3 and scope access.

The text below in italics comes direct from the Identity Server documentation.

Identity Server Concepts

OpenID Connect Provider (OP)

IdentityServer is an OpenID Connect provider – it implements the OpenID Connect protocol (and OAuth2 as well).  Different literature uses different terms for the same role – you probably also find security token service, identity provider, authorization server, IP-STS and more.

But they are in a nutshell all the same: a piece of software that issues security tokens to clients.IdentityServer has a number of jobs and features – including:

– authenticate users using a local account store or via an external identity provider
provide session management and single sign-on
manage and authenticate clients
issue identity and access tokens to clients
validate tokens

User

A user is a human that is using a registered client to access his or her data

Client

A client is a piece of software that requests tokens from IdentityServer – either for authenticating a user or for accessing a resource (also often called a relying party or RP). A client must be registered with the OP.Examples for clients are web applications, native mobile or desktop applications, SPAs, server processes etc.

Scope

Scopes are identifiers for resources that a client wants to access. This identifier is sent to the OP during an authentication or token request.

Resource scopes

Resource scopes identify web APIs (also called resource servers) – you could have e.g. a scope named calendar that represents your calendar API.

Access Token

An access token can be validated by a resource.  Clients request access tokens and forward them to an API. Access tokens contain information about the client and the user (if present). APIs use that information to authorize access to their data.

OAuth Concepts

Right, now that we’ve got that cleared up we can take a quick browse through some OAuth2 concepts..  First off here’s a link to the OAuth2 Specification in case you want to read the whole enchilada.  The text below in italics is directly from the OAuth2 specification itself.

What I’m going to be focusing on are Authorization Grants.  First, let’s take a look at how this is all intended to flow:

image
Figure 1 (my edition)

The abstract OAuth 2.0 flow illustrated in Figure 1 describes the interaction between the four roles and includes the following steps:

(A)  The client requests authorization from the resource owner.  The authorization request can be made directly to the resource owner (as shown), or preferably indirectly via the authorization server as an intermediary.

(B)  The client receives an authorization grant, which is a credential representing the resource owner’s authorization, expressed using one of four grant types defined in this specification or using an extension grant type.  The authorization grant type depends on the method used by the client to request authorization and the types supported by the authorization server.

(C)  The client requests an access token by authenticating with the authorization server and presenting the authorization grant.

(D)  The authorization server authenticates the client and validates the authorization grant, and if valid, issues an access token.

1.3.  Authorization Grant

An authorization grant is a credential representing the resource owner’s authorization (to access its protected resources) used by the client to obtain an access token.  This specification defines four grant types —
– authorization code,
– implicit,
– resource owner password credentials, and,
– client credentials
as well as an extensibility mechanism for defining additional types.

1.3.1.  Authorization Code

The authorization code is obtained by using an authorization server as an intermediary between the client and resource owner.  Instead of requesting authorization directly from the resource owner, the client directs the resource owner to an authorization server (via its user-agent as defined in [RFC2616]), which in turn directs the resource owner back to the client with the authorization code.

Before directing the resource owner back to the client with the authorization code, the authorization server authenticates the resource owner and obtains authorization.  Because the resource owner only authenticates with the authorization server, the resource owner’s credentials are never shared with the client.

The authorization code provides a few important security benefits, such as the ability to authenticate the client, as well as the transmission of the access token directly to the client without passing it through the resource owner’s user-agent and potentially exposing it to others, including the resource owner.

1.3.2.  Implicit

The implicit grant is a simplified authorization code flow optimized for clients implemented in a browser using a scripting language such as JavaScript.  In the implicit flow, instead of issuing the client an authorization code, the client is issued an access token directly (as the result of the resource owner authorization).  The grant type is implicit, as no intermediate credentials (such as an authorization code) are issued (and later used to obtain an access token).

When issuing an access token during the implicit grant flow, the authorization server does not authenticate the client.  In some cases, the client identity can be verified via the redirection URI used to deliver the access token to the client.  The access token may be exposed to the resource owner or other applications with access to the resource owner’s user-agent.

Implicit grants improve the responsiveness and efficiency of some clients (such as a client implemented as an in-browser application), since it reduces the number of round trips required to obtain an access token.  However, this convenience should be weighed against the security implications of using implicit grants, such as those described in Sections 10.3 and 10.16, especially when the authorization code grant type is available.

1.3.3.  Resource Owner Password Credentials

The resource owner password credentials (i.e., username and password) can be used directly as an authorization grant to obtain an access token.  The credentials should only be used when there is a high degree of trust between the resource owner and the client (e.g., the client is part of the device operating system or a highly privileged application), and when other authorization grant types are not available (such as an authorization code).

Even though this grant type requires direct client access to the resource owner credentials, the resource owner credentials are used for a single request and are exchanged for an access token.  This grant type can eliminate the need for the client to store the resource owner credentials for future use, by exchanging the credentials with a long-lived access token or refresh token.

1.3.4.  Client Credentials

The client credentials (or other forms of client authentication) can be used as an authorization grant when the authorization scope is limited to the protected resources under the control of the client, or to protected resources previously arranged with the authorization server.  Client credentials are used as an authorization grant typically when the client is acting on its own behalf (the client is also the resource owner) or is requesting access to protected resources based on an authorization previously arranged with the authorization server.

Summary

Hopefully what I’ve presented here makes sense.  I realise that most of the text is a boilerplate lift directly from source, but it’s important to understand the key concepts and terminology before we get stuck into working with identity providers, scopes and clients.

The next article will build upon the info introduced in this article – and hopefully will provide food for thought as you navigate the tricky waters of identity management and security concepts.