I've been using DotNetNuke for A LOT of projects lately. Mostly small projects, but a couple of larger ones that I can't really discuss as of yet...
On one of these larger projects we are using MS SQL Reporting Services, and of course we want it integrated into DNN. The challenge we ran into was with accessing the SQL Reporting Services. Since we aren't using the ADSI provider for authnetication, and the site is not running under an impersonated identity, how do we go about accessing reports on the SQL Reporting Services server?
It was actually much simpler than I orginally thought it would be. I searched and found a few options that referred to Forms Authentication with SRS, but it didn't seem very elegant.
Ultimately the solution we chose to implement was to provide the ability to enter a username/password/domain to be used with a given report module. Then we created a generic user on the SRSS for accessing the report with.
Of course the SQL Reporting Services “visualizer“ for the DNN Reports module is considered extremely experimental, but we found it has suited our purpose quite well after these modifications...
There were only a couple of changes required, and I'll walk you through them now...
Each Visualizer for the DNN Reports module has it's own subset of user controls and code-behind files. We will be modifying only those related to the SQL Reporting Services visualizer.
First we must add the entries to the settings module to store the username/password/domain for our user.
Edit the Settings.ascx file stored in [DNNRoot]\DesktopModules\Reports\Visualizers\Reporting Services
While it doesn't really matter where you add the code, we chose to add ours to the end of the file above the final closing TABLE tag:
Report Server Auth:
|
|
Username:
|
|
Password:
|
|
Domain:
|
|
The code above will display the labels and textboxes for the three pieces of data (username, password, and domain) that we are interested in collecting.
Next we must edit the code-behind file for Settings.ascx, which is Settings.ascx.vb. There are two places to make changes in this file, the first is in the LoadSettings() sub-routine, and the second is in the SaveSettings() sub-routine.
In the LoadSettings() sub-routine we are looking for the section of code that determines whether the Report is local or on the server, of course for this we are only interested if the report is being run from the server. Find the following piece of code:
If radLocal.Checked Then
ctlReport.Url = SettingsUtil.GetDictionarySetting(Of String)(VisualizerSettings, ReportsController.SETTING_RS_LocalReportFile, Null.NullString)
ElseIf radServer.Checked Then
txtServerUrl.Text = SettingsUtil.GetDictionarySetting(Of String)(VisualizerSettings, ReportsController.SETTING_RS_ServerReportUrl, Null.NullString)
txtServerReportPath.Text = SettingsUtil.GetDictionarySetting(Of String)(VisualizerSettings, ReportsController.SETTING_RS_ServerReportPath, Null.NullString)
End If
And before the final 'End If' you will insert the following lines of code:
txtServerUsername.Text = SettingsUtil.GetDictionarySetting(Of String)(VisualizerSettings, “ServerUsername“, Null.NullString)
txtServerPassword.Text = SettingsUtil.GetDictionarySetting(Of String)(VisualizerSettings, “ServerPassword“, Null.NullString)
txtServerDomain.Text = SettingsUtil.GetDictionarySetting(Of String)(VisualizerSettings, “ServerDomain“, Null.NullString)
These lines are used to retrieve the three settings from DotNetNuke for our Module, and assign those values to our textboxes we previously added.
Next we want to add the code to save our settings, so we need to look for the SaveSettings() sub-routine. Towards the end of the sub-routine look for the end of the try..catch statement, inside the try catch we want to add the following lines of code:
VisualizerSettings(“ServerUsername“) = txtServerUsername.Text
VisualizerSettings(“ServerPassword“) = txtServerPassword.Text
VisualizerSettings(“ServerDomain“) = txtServerDomain.Text
These of course store the values from our text boxes into DNN settings for our module.
Last but not least, we must edit the actual Visualizer file itself, appropriately named Visualizer.vb. In the DataBind() sub-routine you will see the IF..THEN statements that check to see whether the settings show the report is local or on a server, once again, we are only interested in applying our new settings if the report is on a server.
Look for the line:
ElseIf String.Equals(sMode, "Server", StringComparison.OrdinalIgnoreCase) Then
and then look for it's matching End If statement. Before the End If, insert the following lines:
Dim sUsername As String = SettingsUtil.GetDictionarySetting(Of String)(Report.VisualizerSettings, ReportsController.SETTING_RS_ServerUsername, String.Empty)
Dim sPassword As String = SettingsUtil.GetDictionarySetting(Of String)(Report.VisualizerSettings, ReportsController.SETTING_RS_ServerPassword, String.Empty)
Dim sDomain As String = SettingsUtil.GetDictionarySetting(Of String)(Report.VisualizerSettings, ReportsController.SETTING_RS_ServerDomain, String.Empty)
If Not String.IsNullOrEmpty(sUsername) Then
ReportViewer.ServerReport.ReportServerCredentials = New ReportCredentials(sUsername, sPassword, sDomain)
End If
The above lines retrieve the settings from DNN into 3 variables, and then check the first variable to see if it is empty. If it's been left empty, then we will assume the user wants to use the pre-existing Windows Authentication model, and we will do nothing. If however the string is not empty we create a new ReportCredentials object, and then assign it to the ReportServerCredentials property of the ServerReport object that was already created.
That's IT! Like I said before, the solution is quite simple, and while probably not very elegant was better for our project than some of the alternative solutions.