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.

Analysis Services(2005) Memory Usage Issue and Fix

What?

SQL Server 2005’s Analysis Services uses a process titled Msmdsrv.exe
The memory used by this process does not decrease as expected and results in error on deployment. The error usually is
“Server: The operation has been cancelled”

Why?

Memory utilization by Analysis Services increases when performing process operation, typically to the type of the object being processed.
a)    Dimension:
a.     Memory increase is approximately the size of the dimension plus Read-ahead and the process buffer settings for AS.
b.    Returns to the pre-processing level, for pre-processed dims. For the first time processing, memory usage increases by size of dim that is loaded in the server memory space.
b)     Cube:
a.    Memory increases by the size of the read-ahead and the process buffers.
b.    Memory usage returns to pre-processing levels after processing completes.

This problem occurs when the memory usage of the Analysis Services Server (Msmdsrv.exe) does not return back to the pre-processing levels.

How?

This issue can be resolved by deleting the unused databases and cubes that are saved. Also what helps is choosing  the processing option as “Full” instead of Default. Default processing is usually incremental update and when the file store of the attributes reach 4GB, error message are issued while processing cubes/dimensions in AS.

Possible HotFix :
http://support.microsoft.com/kb/912016/

So?

It is safer to retain just the databases and cubes that are currently being used and avoid unnecessary data dumps. Also, though incremental processing is fast, Full processing done saves memory usage

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”>

SharePoint Solutions File(.wsp)

SharePoint Solutions File(.wsp)

Introduction:

A SharePoint solution file is a collection of feataures, webparts and files with the extension .wsp
The .wsp file is a .cab file which is a windows archive format.
The general content of the .cab file are as follows,
•    Manifest.xml
•    CustomFeatureCreated\feature.xml
•    CustomFeatureCreated\webpartsmanifest.xml
•    CustomFeatureCreated\webpartcreated.webpart
•    CustomFeatureCreated.dll
•    Images(if they are used in the solution)

Deployment:

Once the .wsp file is move to the below mentioned directory C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Bin
Executing the commands below adds and deploys the solution to the SharePoint sites.

•    stsadm -o addsolution -filename SolutionName.wsp
•    stsadm -o deploysolution -name SolutionName.wsp -immediate -allContentUrls -allowGacDeployment
•    stsadm -o execadmsvcjobs

Activate the feature in SharePoint Site > Site Settings  > Site Features
Add the new webpart  from SharePoint Site  >  Site Settings  >  Web Parts  >  New

Debugging the solution in Visual Studio:

In order to debug the solution in visual studio ensure that
Project Properties > Build > Output > Output Path > is set to c: \Inetpub\wwwroot\wss\VirtualDirectories\Sharepoint MySite\bin\
Redploy the solution following the steps mentioned in Deployment section.
Now, we can modify the .cs files and can debug and see the changes in the webpart without having to deploy the solution every time.