easyROLES the fastest way to set up SSAS roles

Setting up Kerberos authentication

Setting up Kerberos authentication can sometimes give headaches. It is really not hard (or scary as that puppy might seem at first) to get Windows to use this method of authentication and after the initial setup is done your users will definitely be thankful for not having to type in their credentials every time they want to use some application. The only catch with the setup is that it basically gives no constructive feedback and all turns into a guessing game when, for some reason, passing credentials does not function the way it should. In this tutorial we will look at how to set up Windows authentication for a typical easyROLES installation, but the logic is the same whether you would do this for Sharepoint or Cognos or any other service.

Create service accounts

All of the Windows services (web server, SQL server etc) that will be passing further (delegating) and receiving end-user credentials need to run on your servers using domain user accounts. If your SQL server is not yet running on some AD account, then create one for SQL Server services and another one to be used by the IIS Application Pool.

In the examples below you will find these two accounts used:

Domain\AppPoolUser
Domain\SQLSvcUser

Register SPNs

A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service. In order for Kerberos to function for easyROLES, we need to create the SPNs listed below (they go in pairs- one for server name and one for server name with fully qualified domain name in it). Your Active Directory administrator should be able to run these commands.

One pair of SPNs for HTTP service:
setspn -A HTTP/WEB-SERVER Domain\AppPoolUser
setspn -A HTTP/WEB-SERVER.Fully-Qualified-Domain-Name.com Domain\AppPoolUser

One pair for SSAS instance of your SQL Server (in this example running under instance alias ‘SSAS’):
setspn -A MSOLAPSvc.3/SQL-SERVER:SSAS Domain\SQLSvcUser

setspn -A MSOLAPSvc.3/SQL-SERVER.Fully-Qualified-Domain-Name.com:SSAS Domain\SQLSvcUser

And one more pair for SQL server itself (3500 being the port number for TCP/IP connections to the DB instance):
setspn -A MSSQLSvc/SQL-SERVER:3500 Domain\SQLSvcUser

setspn -A MSSQLSvc/SQL-SERVER.Fully-Qualified-Domain-Name.com:3500 Domain\SQLSvcUser

After registering the SPNs ‘Delegation’ tab will appear on the users profile in AD. Set trust for delegation on AppPoolUser account as this is the web service account that will pick up our end-users credentials and pass them further to the DB/SSAS instances.

Finish IIS setup

Assuming that IIS is already hosting you easyROLES files, edit the file:
C:\Windows\System32\inetsrv\config\applicationHost.config

Find section related to the Windows authentication of easyROLES folder.

Replace whole of the default windowsAuthentication tag with the following:

<windowsAuthentication enabled=”true” useKernelMode=”true” useAppPoolCredentials=”true”>
<providers>
<clear />
<add value=”Negotiate” />
<add value=”NTLM” />
</providers>
</windowsAuthentication>

The application pool that is running easyROLES application needs to run as Domain\AppPoolUser. In IIS Manager find the AppPool running easyROLES application. Right-click, select advanced settings. Set identity to Domain\AppPoolUser and make sure Load Users profile is set to true. Click OK. Restart the AppPool.

Correct connection strings

The remaining task is to write your connection strings correctly. In context of easyROLES the only thing you need to worry about is the connection to your database. So in order to reach it and authenticate correctly, edit the connection string in the end of web.config file in the easyROLES installation folder. Here is an example:

 <add name=”easyROLESDBContext” connectionString=”Data Source=SQL-SERVER.Fully-Qualified-Domain-Name.com,3500;Network Library=DBMSSOCN;Initial Catalog=easyROLES;Integrated Security=SSPI;Trusted_Connection=True;” />

If your database is running on a specific instance of SQL server, you will need to provide the name of the instance in the connection string. However, Kerberos only works via TCP/IP protocol, so you will still need to provide the port number in the connection string. Your Data Source, therefore, should look simmilar to this:

SQL-SERVER.Fully-Qualified-Domain-Name.com\InstanceName,3500

Testing

The best way to test this is to try and access your application via Internet Explorer. You should always access the application via a URL which contains a fully qualified name of the web server and the web server should be added among the trusted sites of your browser. So in scope of our example, this would be the URL:

http://WEB-SERVER.Fully-Qualified-Domain-Name.com/easyROLES

You can either add the above or just http://WEB-SERVER.Fully-Qualified-Domain-Name.com as a trusted site in IE.

Screenshot showing how to add a trusted site into internet explorer settings

At this point you should already be able to access easyROLES and manage SSAS security via the Active Directory account which you used to log on to your computer with. No extra login prompts, no delays or other complications. If you continue to get error messages, don’t hesitate to ask for help or just drop a comment here and we will answer ASAP.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

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