SQL Server: 2012

After spending 2 days scratching my head on how to implement forms authentication for SSRS 2012, I finally decided to use the forms authentication sample provided for SSRS 2008 R2 although the official Microsoft page says that it only works with SSRS 2008 R2 and earlier versions which was worth a try. Most of the steps are same as in SQL Server 2008 R2.

Given below are the steps taken for implementing the forms authentication sample in SQL Server 2012.

Building the sample -

Step 1 - Download the Security Extension Sample from and install the same on your system from the link given here Link

Step 2 - The sample will be installed in the same folder where you have installed your SQL Server. for example - <SQL Server Installation Folder> \100\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample, you can see that there is one solution file for VB and one for C#. I will be using the solution file for C# in this post. Open the solution you might get a warning symbol next to Microsoft.ReportingServices.Interfaces, in that case open Right Click on the Reference node and say Add Reference which opens a dialog box, the click on browse and go to <ReportingService Directory>/ReportServer/bin and click on add. Now you can see the warning symbol disappears.

Step 3 - In the AuthenticationExtension.cs and AuthenticationUtilities.cs files replace the localhost in connection string with the <servername>\RS_<instancename> (remember to prefix RS_ to the instance name).

Step 4 - Open the file AuthenticationUtilities.cs and replace the reportserverinstance with RS_<reportserver instance name>.

If you dont prefix RS_ for the instance name you might get invalid namespace error when you restart the reportserver.

Step 5 - In the AuthenticationUtilities.cs go the line where the wmiNameSpace has been declared and replace {0} with the RS_<report server instance name> and replace V10 with V11 so the string should like @"\root\Microsoft\SqlServer\ReportServer\RS_<instancename>\v11"

Step 6 - After making all these changes now you are ready to build the solution, but you would require a keyfile to complete this operation. So go to the VisualStudio command prompt and navigate to the Samples folder and execute the command "sn -k SampleKey.snk". If you navigate to the folder in the windows explorer you should be able to see the SampleKey.snk folder in it. Now right click on the project and click on build. You should be able to see the message "Build Succeeded" and now you are ready to deploy the sample.

Deploying the sample -

Step 1 - Go to the Solution folder/bin/debug and you can find the files Microsoft.Samples.ReportingServices.CustomSecurity.dll, Microsoft.Samples.ReportingServices.CustomSecurity.pdb files. Copy these 2 files into <SQL Server Reporting Services Path>\ReportServer\bin directory and <SQL Server Reporting Services Path>\ReportManager\bin directory.

Step 2 - Copy Logon.aspx in the solution file into the and <SQL Server Reporting Services Path>\ReportServer directory.

Step 3 - Copy UILogon.aspx from and <SQL Server Reporting Services Path>\ReportManager\Pages.

Modifying ReportServer and ReportManager configuration files -

Step 1 - Modifying ReportServer.config file located in <SQL Server Reporting Services Path>\ReportServer

a) Open the ReportServer.config file and add replace the <Authentication> tag with the code given below

<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>
</Authentication>

b) Replace the Security tag with the code given below

<Security>
<Extension Name="Forms"
Type="Microsoft.Samples.ReportingServices.CustomSecurity.Authorization,
Microsoft.Samples.ReportingServices.CustomSecurity" >
<Configuration>
<AdminConfiguration>
<UserName>username</UserName>
</AdminConfiguration>
</Configuration>
</Extension>
</Security>
<Authentication>
<Extension Name="Forms"
Type="Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension,
Microsoft.Samples.ReportingServices.CustomSecurity" />

c) Locate the UI tag and replace it with the code given below -

<UI>
<CustomAuthenticationUI>
<loginUrl>/Pages/UILogon.aspx</loginUrl>
<UseSSL>True</UseSSL>
</CustomAuthenticationUI>
<ReportServerUrl>http://<server>/ReportServer</ReportServerUrl>
</UI>

* Remember to place your url in the ReportServerUrl tag and set UseSSL tag to false if you dont use SSL.

Step 2 - Modifying the RSSrvPolicy.config file located in the <SQL Reporting Services Path>\ReportServer

a) Add the below code to the existing policy which has the URL Membership of $CodeGen

Step 3 - Modifying RSMgrPolicy.config file located in <SQL Reporting Services Path>\ReportManager folder.

You should locate the below code in the file and set PermissionSetName to "FullTrust" which has been shown in bold.

Step 4 - Modifying the Web.config file located in the <SQL Reporting Services Path>\ReportServer folder

a) Locate the authentication mode tag and replace it with the code given below

b) Add the below code to below the Authentication Mode tag to restrict access to the users who dont have access rights

Step 5 - Modifying the Web.Config file located in the <SQL Reporting Services Path>\ReportManager folder.

a) set <identity impersonate ="false" />

b) set <authentication mode="forms">

c) Go to appSettings and add the 2 keys given below to it

Creating Database -

Go to <SQL Server Install directory>\100\Samples\Reporting Services\Extension Samples\FormsAuthentication Sample\cs\FormsAuthentication\Setup you can see 2 scripts, use the CreateUserStore.sql file and create database and tables which will be used for forms authentication.

Setting up admin access to the ReportManager

Step 1 -Open the ReportManager url and add a username and password and click register.

Step 2 - Open <SQL server reporting services install directory>\RSReportServer.config file

locate the Secuirty tag and you will see the tag Username tag in it and it has a dummy value as of now. Replace this with the username which you just registered.

this will give that particular user complete access to the ReportServer as well as ReportManager.

So we have a the forms authentication up and running on your report server. If you get any error like unable to load Microsoft.Samples.ReportingServices.CustomSecurity.dll in the log files, restart your system and you should be able to see the login page for the reportserver or reportmanager. Working on creating my own security extension instead of the one provided in the sample. Will post it if I am successful. If you have any problem while implementing this comment on this post, will try my best to resolve it.

SQL Server

How do you rate this blog

Saturday, September 29, 2012

Forms Authentication in SSRS 2012