There must be a way to do this. I simply need a way to expose the IP address (or computer name - but I think IP is easier to get) of the user who is viewing a report on an SQL 2008 (not r2) report server. Here is what I have already tried:
Writing code in the report code block to get the IP address such as:
Public Function GetClientIP() As String
Dim sReturn As String = ""
Dim ipHost As System.Net.IPHostEntry = System.Net.Dns.GetHostEntry(System.Net.Dns.GetHostName())
For i As int32 = 0 To ipHost.AddressList.Length - 1
If (Not ipHost.AddressList(i) Is Nothing AndAlso ipHost.AddressList(i).ToString().Trim() <> "" AndAlso ipHost.AddressList(i).ToString().Length() <= 15) Then
sReturn = ipHost.AddressList(i).ToString()
End If
Next i
Return sReturn
End Function
At first this gave me all kinds of security permission errors and I was able to get around it by changing the PermissionSet for Report_Expressions_Default_Permissions to FullTrust in rssrvpolicy.config:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Report_Expressions_Default_Permissions"
Description="This code group grants default permissions for code in report expressions and Code element. ">
<IMembershipCondition
class="StrongNameMembershipCondition"
version="1"
PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100512C8E872E28569E733BCB123794DAB55111A0570B3B3D4DE3794153DEA5EFB7C3FEA9F2D8236CFF320C4FD0EAD5F677880BF6C181F296C751C5F6E65B04D3834C02F792FEE0FE452915D44AFE74A0C27E0D8E4B8D04EC52A8E281E01FF47E7D694E6C7275A09AFCBFD8CC82705A06B20FD6EF61EBBA6873E29C8C0F2CAEDDA2"
/>
</CodeGroup>
And after all that I found out that it is returning the server IP address instead of the client! That's not what this or this say.
I tried different ASP.net variables such as HttpContext.Current.Request.ServerVariables["REMOTE_ADDR"] which
In fact I wrote a quick script to grab all the server variables and display it on the report screen, but none of it contains the client IP address!
Public Function GetServerVariables() AS String
Dim sReturn as String = ""
for i as int32 = 0 to System.Web.HttpContext.Current.Request.ServerVariables.Count -1
sReturn &=System.Web.HttpContext.Current.Request.ServerVariables.Keys(i).ToString & ": " & System.Web.HttpContext.Current.Request.ServerVariables.Item(i).ToString() & " - "
next i
Return sReturn
End Function
I tried creating a custom assembly in vb.NET which basically runs the exact same code as the GetClientIP() function above but I was not able to resolve the permission errors with that one after a couple days of trying. I eventually gave up on this because around the same time I got the report code from step (1) working and since it was returning the server IP address I assumed that the custom assembly would return the same thing.
So there we have it. Sorry about the long-winded explanation but I wanted to be as thorough as possible. And for those who want to know WHY I need the users IP address, it is actually another complicated story and I will explain it in another post if someone really wants to know.
Bottom line is, I need the IP address or computer name of the computer that is running the report in their browser. The username or any other details will not suffice unless they can be used to lookup the IP address somehow.
I would love for someone to give me a simpler solution to finding the users ip address when running a report, but here is a working solution that I was able to hack together in a couple weeks.
First of all, I had no luck with custom assemblies or the report code block, as all I got was the servers ip address instead of the client. Instead, I decided to play with jquery in the report manager aspx pages to do the dirty work.
Here are the steps as straightforward as I can put them:
1) Create a new file called GetClientIPAddress.aspx in the Program Files\Microsoft SQL Server Reporting Server\MSRS10.Instancename\Reporting Services\ReportManager\Pages directory. The content of the GetClientIPAddress.aspx is one line as follows:
<%=Request.ServerVariables["REMOTE_ADDR"]%>
This is the page that will actually get the clients ip address.
2) Edit the Folder.aspx file in the same folder and add this javascript code at the bottom of the file:
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js"></script>
<script type="text/javascript">
$(document.body).ready(function () {
try {
var sReportName = 'Name of report here'
$("a").each(function() {
if (this.innerText == sReportName || this.textContent == sReportName)
{
this.href = this.href.replace('Report.aspx','ReportEx.aspx');
}
});
}
catch (e) { }
});
</script>
Replace the 'Name of report here' with the name of your report. The report name is by default the filename of the report minus the extension. You can find and change this value in the report "Properties" tab > "General" sub tab > "Name" field.
3) Finally, create a new file in the same folder called ReportEx.aspx. This file is based off of and uses the same header as the Report.aspx file:
<%@ Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI" Assembly="ReportingServicesWebUserInterface" %>
<%@ Page language="c#" Codebehind="Report.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage" EnableEventValidation="false" %>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.min.js"></script>
<script type="text/javascript">
function getUrlVars()
{
var vars = [], hash;
var hashes = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
for(var i = 0; i < hashes.length; i++)
{
hash = hashes[i].split('=');
vars.push(hash[0]);
vars[hash[0]] = hash[1];
}
return vars;
}
$(document.body).ready(function () {
try {
var sItemPath = getUrlVars()["ItemPath"];
var sSelectedTab = getUrlVars()["SelectedTabId"];
if (sSelectedTab === undefined || sSelectedTab == 'ViewTab')
{
$("table.msrs-normal").each(function() {
if (this.rows.length == 2 && this.rows[0].cells.length == 1)
{
var sIPAddress = $.ajax({
type: "GET",
url: 'GetClientIPAddress.aspx',
async: false
}).responseText;
this.rows[1].cells[0].innerHTML = '<iframe style="width: 100%; height: 100%;" frameborder="0" src="http://servername/ReportServer/Pages/ReportViewer.aspx?'+sItemPath+'&rs:Command=Render&IPAddress='+sIPAddress+'"></iframe>';
}
});
}
}
catch (e) { }
});
</script>
Replace 'servername' with the ip address or computer name of the server running the report/sql server.
This "hack" works by effectively intercepting the anchor tag href link to your report and replacing it with an almost identical link that goes to ReportEx.aspx instead of Report.aspx. On the ReportEx page, an ajax javascript call is made to the GetClientIPAddress.aspx page which returns the client ip address. The report is effectively hidden or erased through javascript and is replaced with an iframe of the exact same report. The only difference is that the new report has the ip address set as a query string parameter which is passed directly into the report this time. This of course requires that you create a parameter in your report called 'IPAddress' so that it will properly be accepted once we have done all this work to get it there!
So there we have it. If anyone can think of a better way to do this, or even a way to simplify some of my steps, I would love to hear about it! This was done on SQL Server 2008, and I imagine that this solution could work on earlier and newer versions but possibly with slight alterations of the code. As roundabout as this solution is, I am providing it in the hope that someone else may benefit from my hours and hours spent trying to figure out A way, ANY way to do this!