SQL Server Reporting Services(2008) reports and SharePoint integration

Issue:
The reports were deployed to the Document Library but when we tried to access them the following error kept popping up
• An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source ‘data_source1′. (rsErrorOpeningConnection) Get Online Help
Internal 500 error. Database access denied.

Fix:
After some intense research we discovered that the problem was not RS as such, but the IIS setup hosting it.
IIS was handling the app pool in an aggravating way.
The fix was to set its properties so that under Performance, the idle timeout was disabled. We could access the reports from http://MOSSServer/SharedDocument folder afterward.
However, we could not add the reports as web parts.
Research revealed that the web part “ReportViewer.dwp“ had to be manually added to the web part collection.
ReportViewer.dwp can be found in the following folder
“C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\FEATURES\ReportServer”
The steps involved in adding are as follows,
My Site -> Site Actions -> Site Settings -> Galleries -> Web Parts -> Upload and browse to the ReportViewer.dwp file and add it.
On refreshing we can see the added web part named, SQL Server Reporting Services Report Viewer.

Debugging SharePoint web part(SmartTemplate)

After running around in circles for a day, finally figured out how to enable debugging in a SharePoint SmartPart web part project.

When using SharePoint smart part template, the DeploymentTarget is set to “WebApplication” in manifest.xml by default.

• Modify WSPBuilder.exe.config file adding

  <add key=“DeploymentTarget“ value=“GAC“ />

  Setting the value as GAC (GlobalAssemblyCache) installs the assembly of the custom web part is in C:\WINDOWS\assembly folder automatically.

• Change the Project Properties: ..\Inetpub\wwwroot\wss\VirtualDirectories\YourSharePointVirtualDirectory\bin\

Changing the output path as mentioned above adds the .dll and .pdb files to the specified web application.

• Build the solution.
• Refresh the SharePoint site that has the SmartPart.
• Debug the solution and attach to process

• Select the w3wp.exe process.

• Determine if the selected process is correct by verifying the modules. CTRL + ALT + U will display all the modules. If your module is not listed try another w3wp.exe process.

By following the above steps, we can debug the solution. Once it is ready, change the output path to \bin\debug. Rebuild the solution again.

It is ready to be deployed!

Images in SharePoint site.

The images that we require to use in a sharepoint webpart need to be placed in the following directory for us to be able to view it.

http://{the sharepoint site}/_catalogs/masterpage

In the above URL click upload and upload the required image.

Embed .js and .css to a web part

You can embed any resource (.jpg, .js, .css, etc) into a web part and provide it to the client without having to write an httphandler.   Here’s what you do:

1. Add the .js file to the project and mark as an embedded resource (See Build Action on properties tab).

2. Go into the AssemblyInfo.cs and add an entry.  It will look like this:
[assembly: WebResource ("Namespace.File.js","text/javascript")]

Where Namespace is the namespace for your web part project and file.js is the name of your file.

3.  Inside OnPreRender, add the resource to the page.  This is done with the following code:

Page.ClientScript.RegisterClientScriptResource(GetType(), “Namespace.File.js”);

Get the Server name and SQL instance name in ToolPart (c#)

Requirement: ServerName and InstanceName to be displayed in a ToolPart

Data: The classes required to retrieve the DataSource(this contains the server name and instance name used in the connection string) using ASP.NET are,

SqlConnection, SqlCommand

Solution:

public string RetrieveDataSource

{

get

{

SqlConnection sqlConnection = new SqlConnection(this.CurrentConnectionString); SqlCommand sqlCmd = new SqlCommand(); sqlConnection.Open();

sqlCmd.Connection = sqlConnection; return “Server Name :” + sqlCmd.Connection.DataSource;

}

}

Conclusion: The above code returns the DataSource in the following format “MyServerName\SQLInstanceName”

Silverlight and SharePoint

In order to integrate Silverlight application with SharePoint we need to make a bunch of modifications to web.config file. Feeling pretty nostalgic about your first SilverLight “Hello World” custom web part? I did too.  Then came the integration of the application for the SharePoint world to see. After going through some unexpected and undesirable results, rookie after all, I finally got it to work. A custom Silverlight Hello World app displayed in a SharePoint page as a custom web part! How fun.

Modify the SharePoint web.config following the steps below to see the application.

Steps to enable Silverlight 2,3 on SharePoint

1)       Add the following before the closing <configSections> tag.

<sectionGroup name=”system.web.extensions” type=”System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″>

<sectionGroup name=”scripting” type=”System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″>

<section name=”scriptResourceHandler” type=”System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ requirePermission=”false” allowDefinition=”MachineToApplication” />

<sectionGroup name=”webServices” type=”System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″>

<section name=”jsonSerialization” type=”System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ requirePermission=”false” allowDefinition=”Everywhere” />

<section name=”profileService” type=”System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ requirePermission=”false” allowDefinition=”MachineToApplication” />

<section name=”authenticationService” type=”System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ requirePermission=”false” allowDefinition=”MachineToApplication” />

<section name=”roleService” type=”System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ requirePermission=”false” allowDefinition=”MachineToApplication” />

</sectionGroup>

</sectionGroup>

</sectionGroup>

2)      Add the following safe control entry to <SafeControls>

<SafeControl Assembly=”System.Web.Silverlight, Version=3.0.40723.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ Namespace=”System.Web.UI.SilverlightControls” TypeName=”*” Safe=”True” />

<SafeControl Assembly=”System.Web.Silverlight, Version=2.0.5.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ Namespace=”System.Web.UI.SilverlightControls” TypeName=”*” Safe=”True” />

3)      Add the following section immediately before the closing </httpHandlers> tag.

<add verb=”*” path=”*.asmx” validate=”false” type=”System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

<add verb=”*” path=”*_AppService.axd” validate=”false” type=”System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

<add verb=”GET,HEAD” path=”ScriptResource.axd” type=”System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ validate=”false” />

4)      Add the following section before the closing   </httpModules> section

<add name=”ScriptModule” type=”System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

5)      Add the following section to the <assemblies> section.

<add assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ />

<add assembly=”Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” />

<add assembly=”System.Web.Extensions.Design, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

<add assembly=”System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089″ />

<add assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

<add assembly=”System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089″ />

<add assembly=”System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089″ />

<add assembly=”System.Web.Silverlight, Version=2.0.5.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

6)      Add the following section to the <pages><controls> group.

<controls>

<add tagPrefix=”asp” namespace=”System.Web.UI” assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

<add tagPrefix=”asp” namespace=”System.Web.UI.WebControls” assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35″ />

</controls>

7)      Add the following to the <runtime> <assemblyBinding>group before any other <dependentAssembly> entries.

<dependentAssembly>

<assemblyIdentity name=”System.Web.Extensions” publicKeyToken=”31bf3856ad364e35″ />

<bindingRedirect oldVersion=”1.0.0.0-1.1.0.0″ newVersion=”3.5.0.0″ />

</dependentAssembly>

<dependentAssembly>

<assemblyIdentity name=”System.Web.Extensions.Design” publicKeyToken=”31bf3856ad364e35″ />

<bindingRedirect oldVersion=”1.0.0.0-1.1.0.0″ newVersion=”3.5.0.0″ />

</dependentAssembly>

In case you didn’t know the first step before greeting the world was to configure Silverlight MIME types in IIS.

Configure IIS for running Silverlight application:

1) Windows Server 2008 IIS 7.0

All MIME types needed to support Silverlight are implemented by default in Windows Server 2008 IIS 7.  Add mime types by running “IIS Manager”, clicking on “Mime Types”, then clicking “add” and adding the following mime types:

• .xap     application/x-silverlight-app
• .xaml    application/xaml+xml
• .xbap    application/x-ms-xbap

2) Windows Server 2003 IIS 6.0

1. Start -> Administrative Tools -> IIS Manager

2. Right click on the server name and select “Properties”.In the Properties Dialog,

• MIME Types ->New-> MIME Type
• Enter the below listed MIME types one at the time:

1)  .xap     application/x-silverlight-app

2)  .xaml    application/xaml+xml

3)  .xbap    application/x-ms-xbap

SQL Server 2005 Reporting Services Configuration Madness

Well, after almost exactly 6 hours, I’ve succeeded at installing SQL Server 2005 Reporting Services on a server with more than one website.

We’re running Reporting Services on separate web servers. So, after the install of reporting services, you run their little configuration tool. This of course, accomplishes very little . See, apparently Reporting Services wasn’t designed to work on a server running, *gasp*, more than one application.

If you have a decent IIS install, the default website isn’t there and thus requests to http://localhost/ aren’t gonna work. Reporting Services doesn’t take this into consideration, and happily tries to request http://localhost/ReportServer/ even after you’ve specified this in the config tool. If this is your issue, you’ll get a “HTTP Error 400: Bad Request“ when trying to access the ReportManager (/Reports/) website. You’ll need to edit the config files in Program Files\…..\ReportManager and ReportServer. rsreportserver.config needs to point to http://the.reporting.host.name/ReportServer in the UrlRoot element.

In RSWebApplication.config, ReportServerUrl will need to have the same value. The ReportServerVirtualDirectory element must be deleted. You will get a “The configuration file contains an element that is not valid. The ReportServerUrl element is not a configuration file element. “ message. This is because the config reading code apparently doesn’t fail gracefully. What it’s trying to say is “the ReportServerUrl and ReportServerVirtualDirectory elements are mutually exclusive”. I’m still unsure why there should be anything besides a URL… Around here, you might notice a bunch of DCOM errors in your Event Log (or before this point). To fix these, you’ll need to go into dcomcnfg and edit the COM security for My Computer. Give the account you’re using (like Network Service or “MyReportingServicesAccount“) permissions for local activation and local launch. You need to reboot for these changes to take effect (I think). But don’t reboot just yet… Finally, you end up with a 401 Unauthorized when accessing the Reports site. You might have also noticed you are also unable to authenticate when browsing the Reports or ReportServer sites from your the local server. Why? “Windows XP SP2 and Windows Server 2003 SP1 include a loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.” So I’m guessing NTLM susceptible to this type of attack, and Microsoft is saving us from it. Well, it also hoses us in this case because from what I can tell, ReportManager (the thing in the Reports vdir — why it wasn’t called ReportManager by default…) needs to connect to ReportServer. It sends a request, which is denied because of the loopback protection above. A quick registry edit fixes this: http://support.microsoft.com/default.aspx?scid=kb;en-us;896861 After that… you might have a working SQL Reporting Services 2005 install! (Next up: Getting it to work with SSL…) Really, apart from the horrible setup/configuration, it’s a very very fine product. I’m actually pretty impressed. The report I wanted to setup (and the subscription so it’s mailed out) only took about 10 minutes (first time I’ve ever used RS)! I’m just at a loss why Microsoft makes it so hard to setup. This configuration can’t be that unusual. And, even stranger, most (if not all) of this configuration issues could take care of these problems. In other words, their little configuration app should automatically fix this stuff (or at least give explicit instructions on how to do so). Or maybe I just didn’t RTFM that well… but this is a Microsoft product… you’re supposed to just shove the DVD in the drive and click next, right?

P.S., if you’re getting a “Object Reference not set to an Instance of an Object“ when you add a new subscription, ensuring everything else is 100% working should make it go away…

GACUtil.exe in Windows Server 2008

I was trying to deploy a custom assembly using Windows 2008 and SQL Server 2008.

I could run as administrator from the command prompt icon and move the .dll file to the assembly folder.

But, the assembly was not installed and could not be listed.

Drag and drop to the folder gave “Access Denied” error.

Tried to look for GACUtil from C:\WINDOWS\Microsoft.NET\Framework\version to install the .dll but the file was missing.

Apparently, the GACUtil.exe utility has now moved first and foremost and is not installed by default until you install the .NET and Windows 2008 SDK.

After that, you’ll be able to find the GACUtil.exe utility in the SDK directory and can call it as shown below:

C:\Program Files\Microsoft SDKs\Windows\v6.1\bin\gacutil.exe /i “dllfilename.dll”

Then, you’ll need to copy your DLL file into the C:\windows\Microsoft.NET\Framework\v2.0.50727 (or whatever .NET version you wish to be on) directory and the custom assembly is ready to be used.

The issue that prompted this learning was that the custom web part deployed could not be added to my SharePoint site and it was throwing the following error.

“Unable to add selected web part(s). A Web Part or Web Form Control on this Page cannot be displayed or imported. The type could not be found or it is not registered as safe.”

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’

If this error occurs when connecting a custom web part to the Sql database it usually means that the Anonymous Logon identity doesn’t have permissions to the specified database. To resolve this we can either change the identity to one that does, or give these account permissions; Or, use SQL Server authentication.

If Windows Authentication mode is selected during installation, the sa login is disabled. If you later change authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login, use the ALTER LOGIN statement.

ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = ‘<enterStrongPasswordHere>’ ;
GO

Now, the sa account can log in and access the databases in the custom web part.

Sharepoint Unexpected Error

Wow!!! Unexpected error…hmmm…now what could it be?

Well to find that out, make the customErrors mode Off and set the SafeMode CallStack to true below change to the web.config file

1.    <customErrors mode=”Off” />

2.    <SafeMode MaxControls=”200″ CallStack=”true” DirectFileDependencies=”10″ TotalFileDependencies=”50″ AllowPageLevelTrace=”false”>