SQL Server Reporting Services With Forms Authentication: 2013

Hi Guys,

Being new to Sql Server Reporting Services, I was scratching my head nearly a week for implementing Single sign on with Forms authentication and understanding how it can be implemented. Knowing the pain of facing the failures, even after implementing in the right way I am writing this post to convey my learning so that it would be useful for some one who starts after me and also a reference for me. I would like to include each and every minute part with detailed implementation so tat it would be easy for you guys. SSRS geeks pls forgive me.

I have implemented SSRS forms authentication using SSRS 2012 and VS 2012.

Let me first start explaining my scenario, I was needed to implement Single Sign on to reports from a WPF application, The reports would be deployed in a report server and the WPF application needs to access reports directly instead of asking for login.

I was developing this for a product so I need to develop a prototype for the above scenario and show the Single sign on Implementation. I did not find any other best options for implementing

Lets get started, The below link gives you a clear picture of SSRS Reports services with Forms authentication.

http://msdn.microsoft.com/en-us/library/aa902691.aspx

Lets Start Action :)

SSRS by default supports only Windows authentication mode and if you want to have interaction with other domain or in situations where you could not use windows authentication you have a forms authentication sample provided by Microsoft. The sample can be downloaded here http://msftrsprodsamples.codeplex.com/wikipage?title=SS2008R2!Security%20Extension%20Sample

In the same link they had provided details regarding the deployment of sample to SSRS. Make sure that you are manipulate the code changes provided in the above site.

Note: Make sure that you build the sample with complete version of Visual Studio, I initially build the security extension sample with VS 2012 Dev Express and the deployment was not behaving as expected. However, when performed in professional edition It worked. Cheeky...

I would be repeating the same steps as mentioned in the above mentioned website, except would be pointing out the areas where you would go wrong, since I went wrong :P.

Make sure you perform all the changes that need to be done in code mentioned under before you begin heading, cause the sample would not work even if you miss one particular action.

Hoping you downloaded, and build the sample with key. I will explain regarding the configurations, which is the vital part of Implementation.

Important:

Make back up copies of all your configuration changes before you proceed with changes.

<InstallLocation> is considered as "C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services" below. This is the default location for default instance MSSQLSERVER.

REPORT SERVER FILES MODIFICATION:

To modify the RSReportServer.config file:

RsReportServer.config file can be found <InstallLocation>\ReportServer. Locate the <AuthenticationTypes> element and modify the settings as follows:

</AuthenticationTypes>

<RSWindowsExtendedProtectionScenario>Proxy</RSWindowsExtendedProtectionScenario>

</Authentication>

Locate the <Security> and <Authentication> elements, within the <Extensions> element, and modify the settings as follows:

<UserName>username</UserName>

</AdminConfiguration>

</Configuration>

</Extension>

</Security>

</Authentication>

Locate the <UI> element and update it as follows

<UI>

<loginUrl>/Pages/UILogon.aspx</loginUrl>

</CustomAuthenticationUI>

<ReportServerUrl>http://<server>/ReportServer</ReportServerUrl>

</UI>

Note: If you are running the sample security extension in a development environment that does not have a Secure Sockets Layer (SSL) certificate installed, you must change the value of the <UseSSL> element to False in the previous configuration entry

To modify the RSSrvPolicy.config file:

You will need to add a code group for your custom security extension that grants FullTrust permission for your extension.

You do this by adding the code group to the RSSrvPolicy.config file.

Open the RSSrvPolicy.config file located in the <InstallLocation>\ReportServer directory.

Add the following <CodeGroup> element after the existing code group in the security policy file that has a URL membership of $CodeGen as indicated below and then add an entry as follows to RSSrvPolicy.config:

<CodeGroup

class="UnionCodeGroup"

version="1"

Name="SecurityExtensionCodeGroup"

Description="Code group for the sample security extension"

PermissionSetName="FullTrust">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.Samples.ReportingServices.CustomSecurity.dll"

</CodeGroup>

The modified code looks like below.

<CodeGroup

class="UnionCodeGroup"

version="1"

PermissionSetName="FullTrust">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="$CodeGen$/*"

</CodeGroup>

<CodeGroup

class="UnionCodeGroup"

version="1"

Name="SecurityExtensionCodeGroup"

Description="Code group for the sample security extension"

PermissionSetName="FullTrust">

<IMembershipCondition

class="UrlMembershipCondition"

version="1"

Url="C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\Microsoft.Samples.ReportingServices.CustomSecurity.dll"/>

</CodeGroup>

To modify the Web.config file for Report Server:

Open the Web.config file in a text editor. By default, the file is located in the <InstallLocation>\ReportServer directory.

Locate the <identity> element and set the Impersonate attribute to false. * <identity impersonate="false" /> *

Locate the <authentication> element and change the Mode attribute to Forms.

Add the following <forms> element as a child of the <authentication> element and set the loginUrl, name, timeout, and path attributes as follows:

<forms loginUrl="logon.aspx" name="sqlAuthCookie" timeout="60"

path="/"></forms>

</authentication>

Add the following <authorization> element directly after the <authentication> element

</authorization>

This will deny unauthenticated users the right to access the report server. The previously established loginUrl attribute of the <authentication> element will redirect unauthenticated requests to the Logon.aspx page.

REPORT MANAGER FILES MODIFICATION:

To modify the RSMgrPolicy.config file:

Open the Report Manager policy file, RSMgrPolicy.config, located in the <install>\ReportManager directory.

Locate the following code group in RSMgrPolicy.config and change the PermissionSetName attribute from Execution to FullTrust as follows:

<CodeGroup

class="FirstMatchCodeGroup"

version="1"

PermissionSetName="FullTrust"

Description="This code group grants MyComputer code Execution permission. ">

<IMembershipCondition

class="ZoneMembershipCondition"

version="1"

Zone="MyComputer" /> "}*To use Forms Authentication, you need to modify the Web.config files for Report Manager and Report Server.

To modify the Web.config file for Report Manager:

Open the Web.config for Report Manager. It is located in the <InstallLocation>\ReportManager directory.

Disable impersonation by locating the section <identity impersonate= "true" /> and changing it to the following: <identity impersonate="false" />.

Locate the <authentication> element and change the Mode attribute to Forms. <authentication mode="Forms" />

Add the following keys to the <appSettings> element.

Change the <Server Name> value to the name of the report server and the <Instance Name> value to the name of the instance the report server is associated with.

Example:

Note:

The <Instance Name> for a default instance is RS_MSSQLSERVER. It is mandatory to prefix RS_ <ReportServerName> for the sample to recognize report server instance.

Example: If you are have installed another instance of Reporting server say "SQLEXPRESS", then you would require to mention your ReportServerInstance as "RS_SQLEXPRESS".

Once the configuration changes are done, restarting the report server service is required.

Go to start=>services.msc

Identify the service "SQL Server Reporting Services (MSSQLSERVER)" right click on the service and restart. The mentioned service is the default service created when installed SSRS.

Creating UserAccounts Database:

You could find the below SQL in downloaded solution.

Execute "CreateUserStore.sql" file in SQL Server Management Studio.

Verify User Accounts Database is created.

You must make sure that report service has access to newly created database, follow the steps below to provide access.

Providing Permissions to User Accounts for Report Server Service:

Go to Databases-> UserAccounts->Security->Users

Right Click Users select "New User"

In General:

1.) Select "Windows User" under UserType dropdown.

2.) Enter "NT SERVICE\ReportServer" for Username.

3.) Enter "NT SERVICE\ReportServer" for Password.

4.) Enter "NT SERVICE\ReportServer" for Default Schema.

In Membership:

Check db_owner

Access to NT Service\ReportServer to UserAccounts Database is given.

Debugging the Sample Extension:

For debugging the sample

1.) Make sure you copy .pdb files along with the dll's

2.) VisualStudion-> Debug-> Attach to Process-> ReportingServicesService.exe attach to process.(make sure you attach correct instance)

3.) Open Reporting services configuration and navigate to report manager url keeping break point in GetUserInfo().

4.) You should be able to debug :) cheers :)

Removing the Sample Extension:

While not generally recommended, it is possible to revert back to Windows Authentication after you have tried out the sample.

To revert to Windows security

Restore the following files from your backup copies: Web.config and RSReportServer.config. This should set the authentication and authorization methods for the report server to the default Windows security. This should also remove any entries you made for your extension in the Report Server configuration file.

After the configuration information is removed, your security extension is no longer available to the report server. You should not have to remove any security descriptors that were created while you were running the report server under the sample security extension. The report server automatically assigns the System Administrator role to the BUILTIN\Administrators group on the computer hosting the report server when Windows Authentication is enabled. However, you will have to manually re-apply any role-based security for your Windows users.

Note that reverting back to Windows Authentication after migrating to a different security extension is generally not recommended. If you do, you may experience errors when you attempt to access items in the report server database that have custom security descriptors, but no Windows Authentication security descriptors.

SQL Server Reporting Services With Forms Authentication

Tuesday, October 29, 2013

About Me