easyROLES the fastest way to set up SSAS roles

Installation guide

General prerequisites

These are the basic things that need to exist in your landscape before you begin:

  1. A working SQL server with active SQL and SSAS instances;
  2. IIS server with .NET Framework 4.0 or newer.

The SQL Server will be hosting easyROLES database. The Web server will hold the application itself, read data from the database and update your SSAS cube security as well.

There is nothing to be installed on the client computers. See users guide for details.

Web server configuration

Extract the contents of easyROLES installation package into the desired destination folder.

In the destination folder, locate file called Web.config. Open it with Notepad or other text editor. In the very end of the file you will find a connection string that is used to connect to the SQL Server. Change SQL-SERVER.Fully-Qualified-Domain-Name.com,1433 to what ever SQL server fully qualified domain name you have. 1433 is the default port number. Use the port to which your SQL server instance is set to. Every instance has it’s own port number.

<add name=”easyROLESDBContext” connectionString=”Data Source=SQL-SERVER.Fully-Qualified-Domain-Name.com,1433;Initial Catalog=easyROLES;Integrated Security=True” providerName=”System.Data.SqlClient” />

You need to have the port number in the connection string to enforce TCP/IP connectivity to your SQL Server as Kerberos requires it.

In IIS manager, create a new application under the Default Web Site.

Use easyroles as alias for the application and point to the folder to which you have extracted the files to.

Make sure Windows Authentication and ASP.NET Impersonation are the only authentication methods enabled for this application.


This is required for propagating the authenticated user tokens if your web server is not on the same server as your SQL Server database and/or the SSAS service. If you have never set up Kerberos before it could be a bit tricky at first. As it is a topic by its own, we have outlined the details of this setup in a separate blog post. You can find it here.

Database connectivity

The web server needs to be able to connect to the SQL Server database and Analysis Services instances. Therefore appropriate drivers are necessary. You can use MS SQL Server feature pack installation package to find the necessary components.

The following drivers need to be present on the web server:

Microsoft SQL Server Native Client
Download the latest version (2012) from Microsoft here.

Microsoft SQL Server ADOMD.NET
Microsoft SQL Server Analysis Management Objects
Download the latest version (2014) from Microsoft here.

SQL Server setup

Create a new database called easyROLES on your database server. Every user who will be using easyROLES needs  to have read/write access to this database. It is advised to create a dedicated Active Directory group and grant this group read/write permissions to the DB.

In the root of the installation package you will find a file called easyRoles_CreateFull.sql. This file contains a SQL script that will create all the necessary tables and functions in the easyROLES database.

This script can be executed from a command prompt by typing:

sqlcmd -S myServer\instanceName -i <Path_for extracted files>\easyRoles_CreateFull.sql


Tweet about this on TwitterShare on LinkedInShare on Google+Share on FacebookShare on StumbleUponEmail this to someone

Comments are closed.

We want you to be comfortable with your purchase and therefore offer 30 day 100% money back guarantee.