SQL Server 2005 Unable to Connect to Integration Service or Unable to Work with Maintenance Plans

Wednesday, 27 May 2009 23:27 by RanjanBanerji

This is a post so that I remember this problem and can fix it when it resurfaces 6 months later.  Though I really hope that in 6 months I will only be dealing with SQL Server 2008 and not 2005.  I recently setup two SQL Server 2005 environments and on one of them I just cannot connect to the Integration Service.  I also noticed that I was unable to create a backup maintenance plan.  A little research led to the following Microsoft Knowledge base article:

http://support.microsoft.com/kb/919224

 

In my case, I do not know if this is true for everyone, there were some discrepancies between how my machine was setup and the fix for the problem.

  • Microsoft suggests registering the 32 bit dts.dll using %windir%\syswow64\regsvr32 "%ProgramFiles(x86)%\Microsoft SQL Server\90\dts\binn\dts.dll".  The problem I found is that dts.dll on my server was not in %ProgramFiles(x86)%\Microsoft SQL Server but instead it was in %ProgramFiles%\Microsoft SQL Server(x86)\90\dts\binn
  • Registering the 32 bit dts.dll did not solve the problem.  I could connect to Integration Service but I was getting all sorts of strange errors.  So I went ahead and registered all the dts/binn DLLs.  Now everything works.

I do not know if by registering all the DLLs if I have created new problems.  But so far everything seems to be working fine.  If I do find problems I will update this post.

Categories:   SQL Server
Actions:   E-mail | Permalink | Comments (0) | Comment RSSRSS comment feed

SQL Reporting Service and SharePoint 2007 - For security reasons DTD is prohibited in this XML document

Friday, 22 May 2009 02:11 by RanjanBanerji

I had setup SQL reporting Service with SharePoint Integration and everything has been working fine.  Well, until a few days ago when I installed Project Server 2007.  I have three environments and the combination of Project Server 2007, SharePoint 2007, and SQL Reporting Service 2005 worked fine on two.  But on one of my development environments I started to get a strange error each time I would try to launch a report.  The error being:

“For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method."

After searching the web I found that the most common explanation for this error is when the server is running out of memory.  I didn’t quite think that was the problem in my case.  Why?  Well I had the same reports working in two other environments, plus I created a simple report that executed a simple query to get one row of data and that too triggered the error.  Plus the machine had plenty of memory.  What was irritating is that neither the SQL Reporting Server logs nor the SharePoint ULS logs and for that matter the Event Logs showed any error.

Based on prior SharePoint experience I figured that this error is the result of some other error that was not handled correctly and then some code tried to parse the exception thinking it was XML and blew up.  Anyway, after much head scratching and analysis of my servers I finally found the culprit.  I am not sure how this happened but in IIS the default web site that hosted the report server virtual directory the following two account groups no longer had any permissions:

 

SQLServer2005ReportingServicesWebServiceUser$COMPUTER_NAME$MSSQLSERVER

SQLServer2005ReportServerUser$COMPUTER_NAME$MSSQLSERVER

 

Once assigned read, write permissions on the default side (cascading) the error vanished and everything was back to normal.

Categories:   SQL Server | SharePoint
Actions:   E-mail | Permalink | Comments (0) | Comment RSSRSS comment feed