SSRS 2008 Common Install Problems

1. When attempting to connect to report server or report manager you get 404 errors or page could not be displayed.

Resolution: change the default TCP port in web service URL and report manager URL (advanced) to any port other than port 80.
http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/75910bcb-a879-4eb7-9757-acc45201fa3a

2. When attempting to connect to report server or report manager: "the report server has encountered a configuration error. (rsServerConfigurationError)"

Resolution: Configure the database in reporting services configuration manager.

Crystal Report Viewer - Load Report Failed

Here is what I did to fix the "Load Report Failed" problem:


1. Add the Crystal Report to your project using Solution Explorer.




2. Right Click the Crystal Report in Solution Explorer and click properties. Set the Build action to None and delete everything from the Custom Tool field.







3. Configure the report source of your report viewer and Wala! Select your report from the drop down.

Can't get report viewer to work with IIS 7.0?

The web.config must be migrated to work with IIS 7. Use this command:

%systemroot%\system32\inetsrv\APPCMD.EXE migrate config "Default Web Site/my web site"

http://social.msdn.microsoft.com/Forums/en-US/vsreportcontrols/thread/6cf9cff6-fb6b-44b2-81db-67d54a94dfcf/

http://www.west-wind.com/WebLog/posts/168221.aspx

http://blogs.msdn.com/webtopics/archive/2009/02/10/report-viewer-toolbar-does-not-render-properly-on-iis-7-0.aspx

MVP Award Program Blog Goes Live!

Check out the MVP Award Program Blog to find out more about the program and the contributions of MVPs to technical communities across the globe.

Here is the link:

http://blogs.msdn.com/mvpawardprogram/default.aspx

Need SQL Info?

Check out this site:

http://msdn.microsoft.com/en-us/sqlserver/bb671054.aspx

Microsoft .NET Framework Error -- But I already installed the .NET Framework!!!!

If you have already installed the Microsoft .NET Framework (2.0, 3.5, whatever) on a client machine and try to run a program written using the framework that causes this type of error message:

Then do this:

First on the client's PC remove (through add/remove programs) ALL installations of the .NET Framework (1.1, 2.0, 3.0, 3.5, etc.) - remove them all. Reboot the machine after uninstalling all of the existing .NET versions.
Next, install .NET 3.5 SP1 from this site: http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=en
Once it has finished installing, reboot the machine.

For some reason when you install the .NET Framework, it doesn't REALLY install and just sits in Add/Remove Programs to taunt you into thinking it is installed.

SQL 2008 Deploying Report Project - Login Prompt?

I used the project wizard to convert my SQL 2005 project to SQL 2008. When I went to deploy the project, there was a reporting service login box. No matter what credentials I entered they were all rejected.

Try this if you're seeing the same issue:

I originally had http:///reportserver_SQL2008. This was giving the login prompt and I couldn't deploy. So I changed the report manager virtual directory in reporting services configuration to something else. Then I changed it back - recreated the virtual directory and it worked.

It seems as though when SSRS 2008 installed, it did not create the report manager virtual directory properly. I also had changed the service account from a domain account to Network Service. This may have had something to with why the virtual directory was not created properly during the original install.

I suppose this is similar to applying default settings to the virtual directory in SQL 2005.

Do you copy a lot of stored procedures or user-defined functions?

Don't do that anymore.

Use Management Studio to generate a script of all stored procedures/user-defined functions/tables:

-Right click your database
-Go to Tasks -> Generate Scripts
-Click Next, Next and chose Stored Procedures, user defined functions or tables.

Suppress Blank Space from Matrices if no Data

Do you have blank lines between your vertically aligned matrices?

The best way I have found to resolve this is to "almost" place the matrices on top of one another.
Make all of your matrices one-one thousandth of an inch lower on the report than the previous matrix.

Printing from the ActiveX control in report viewer will no longer be an option, but you can always export to Excel and print from there.

Windows XP Search -- It won't find what I search for!

This has nothing to do with SSRS, but has cured one of the headaches I've had over the past few years.

Have you ever used the Windows search and noticed that it never finds the files you're looking for? Even if you create a file with an extension of your choice and some content of your choice and search for that content, it won't find the file!

Here is the KB article:

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

This method fixed all of my failed searching headaches:

Method 2

To configure Windows XP to search all files no matter what the file type, obtain the latest service pack for Windows XP and then turn on the Index file types with unknown extensions option. If you use this method, Windows XP searches all file types for the text that you specify. This can affect the performance of the search functionality. To do this:
1.
Click Start, and then click Search (or point to Search, and then click For Files or Folders).
2.
Click Change preferences, and then click With Indexing Service (for faster local searches).
3.
Click Change Indexing Service Settings (Advanced). Note that you do not have to turn on the Index service.
4.
On the toolbar, click Show/Hide Console Tree.
5.
In the left pane, right-click Indexing Service on Local Machine, and then click Properties.
6.
On the Generation tab, click to select the Index files with unknown extensions check box, and then click OK.
7.
Close the Indexing Service console.

Importing SQL2K5 Servers into 2K8

If you try to import SQL2K5 servers into 2K8, you'll get an error.

M$ is working on a KB, but until then here is the answer:

http://blogs.msdn.com/buckwoody/archive/2008/08/18/importing-sql-server-2005-registered-servers-to-sql-server-2008.aspx

SSRS 2005 / Visual Studio 2005 Web Application Installation Troubleshooting

The following list of symptoms and resolutions was compounded by myself over the past few years during the setup of SSRS 2005 and a VS 2005 web application.

Help yourself if you see a similar problem to your own.

Symptom:

When attempting to browse to a report using IE the following error message is displayed:

“The user ‘MachineName\ASPNET’ does not have sufficient access to perform the requested operation”

Resolution:

Open Internet Information Services from Administrative Tools in Control Panel. Right Click Report Server and select Properties.

Under Anonymous access and authentication control, click the Edit… button.


Check the Anonymous access checkbox. Next, click Browse….


In the “Enter the object name” textbox, enter “Machinename\ASPNET”. Click OK Click OK once again on the window just above. Try to access the report once again.

-------------------------------------------------------

Symptom:

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.20'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)


Resolution:

“This is usually an issue where RS is configured to the wrong DB. You should use the config tool to configure RS to a ReportServer DB.”

“In the ReportServer Configuration tool, does the Database tab have a green check? If not you need to go to that tab and choose the Report Server Database that you want to use.”

-------------------------------------------------------

Symptom:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

Resolution:
“1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:Changes to Connection Settings will not take effect until you restart the Database Engine service.

4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.”

-------------------------------------------------------

Symptom:
“Login failed for user ‘NT Authority\Network Service’”

Resolution:
1. In SQL Server Management Studio go to Security. Expand Logins.
2. Right click NT Authority\Network Service.
3. Change the default database to the database that you are trying to access.

4. In the left pane, click server roles. Check the sys admin server role.
5. Click OK to save the changes.

-------------------------------------------------------

Symptom:
"The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request."

Resolution:
It is possible that the webserver may have more than one IP address.

“I was reading through the IIS logs and app logs and discovered that the web server has two IP addresses. Each IP is statically assigned to other websites hosted by this server and are both using port 80. When you type in [hostname] it is defaulting to the other website and causing the error. I changed the port to 81 on the site for the geneva software and that worked locally and remotely”
For example: http://greg:81/WebApplication/Default.aspx
Refer to the following KB article if this does not solve the issue:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315158

-------------------------------------------------------

Symptom:
"Cannot Retrieve Application. Authentication Error” when trying to access report manager on a remote machine.

Resolution:
Using IIS, grant anonymous access to the /Reportserver/ReportBuilder directory.

Symptom:
All sorts of strange behavior surrounding timeout issues, i.e. “Execution ‘’ could not be completed”.

Resolution:
http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
Create a file called sessionTimeout.rss and paste the following code in it:

Public Sub Main()

Dim props() as [Property] props = new [Property] () { new [Property](), new [Property]() }

props(0).Name = "SessionTimeout"

props(0).Value = timeout

props(1).Name = "SessionAccessTimeout"

props(1).Value = timeout

rs.SetSystemProperties(props)
End Sub

Run the following command from the directory in which the sessionTimeout.rss file is stored:rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout="6000"
This example sets the SessionTimeout and SessionAccessTimeouts to about an hour and a half.

-------------------------------------------------------

Symptom:
"Page cannot be found" when browsing aspx pages in Windows Server 2003 with IIS 6.0

Resolution:
1. From your Run command, type inetmgr and press enter.

2. Expand the appropriate nodes in the IIS to locate the "Webservice Extensions" Node

3. Click on the same.

4. You will find a list of "prohibited" extensions in the right.

5. Click on ASP.NET and "allow" it

-------------------------------------------------------

Symptom:
The permissions granted to user 'NT AUTHORITY\NETWORK SERVICE' are insufficient for performing this operation. (rsAccessDenied)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=262746&SiteID=1

Resolution:
Add NT AUTHORITY\NETWORK SERVICE role assignment to security in report manager.

-------------------------------------------------------

Symptom:

The remote server returned an error: (403) Forbidden.

Resolution:

Apply default settings to the report server virtual directory in reporting services configuration manager

Add full access for the Network Service account to 'C:\WINDOWS\Microsoft.NET\Framework\V2.0.050727\Temporary ASP.NET Files'

Go to: 'C:\WINDOWS\Microsoft.NET\Framework\V2.0.050727\Temporary ASP.NET Files'. (or where ever your .NET framework is installed on your machine)Right click on the "Temporary ASP.NET Files" and select properties->secutity.See if the "NETWORK SERVICE" is already part of the "User/Group" section.

If not add it by typing "Network Service".

If it's already there then grant the write permissions.

Assign access to everything under the sun in SQL and in IIS for Reports and ReportServer.

-------------------------------------------------------

Symptom:

The SQL Server 2005 installer says the tools are installed but they in fact are not. When I run SqlRun_Tools.msi and select 'Managment Tools' and/or 'Business Intelligense Development Studio' it gives an message "A component that you have specified in the ADD_LOCAL property is already installed. To upgrade the existing component, refer to the template.ini and set the UPGRADE property to the name of the component. (OK)". Press OK and the setup exits.


Resolution:

Try this command from the setup folder of CD2:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Tools90

msiexec runs for a while in the background, no errors displayed. Still no management tools...

Rebooted and then the install worked.

SQL Injection Prevention

Check out these resources from Buck Woody (SQL Server Program Manager):

http://blogs.msdn.com/buckwoody/archive/2008/06/25/stopping-sql-injection-in-it-s-tracks.aspx

Here's another good resource I came across:

http://msdn.microsoft.com/en-us/magazine/cc163917.aspx

My Report Manager Home page is "almost" blank!!!

All I see is:

SQL Server Reporting Services
Home
Home My Subscriptions Help
Anyone any ideas on how I get the rest of the page back?


1. Goto IIS Manager (inetmgr) then uncheck the 'Allow annonymous access' in the Directory security of my Report virtual directory.2. Open IE and load up the Report webpage, it should be okay by now3. Goto Properties tab of the Report webpage, give access permission to NT AUTHORITY\IUSR and re-enable anonymous access from inetmgr

Post Summit Analysis

Well the '08 Summit turned out to be a blast. Just remember, what happens in Seattle, stays in Seattle. (For the most part)

Here are the transcripts of the keynotes from

Steve Ballmer, Chief Executive Officer:
http://www.microsoft.com/Presspass/exec/steve/2008/04-17MVP.mspx

And

Ray Ozzie, Chief Software Architect:
http://www.microsoft.com/presspass/exec/ozzie/04-17MVP.mspx

Can't make an ODBC connection to SQL? Error code 53?

Consider adding ALL access rights to the SQL login in SQL Server Management Studio (not just sysadmin).

Fit All of Report Data on One Page

I'm not sure how this eluded me for so long, but here is the solution.

In solution explorer, select the dropdown containing all of the items on the report. Select "Report". Then set the interactive height property to zero.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3092542&SiteID=1

SSRS Overwrite Datasources - Avoid the Headache of Reports Not Updating

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2908860&SiteID=1
In order to avoid complete headache, consider these settings.

Right click your project name in solution explorer of BIS.

Set overwrite datasources to true. Click configuration manager. Check the checkboxes for build and deploy on both debug configuration and production configuration.
Retype the target server url after clicking ok to be http://localhost/reportserver.
Make sure the deploy folder is the correct folder (it may reset this). Rebuild and deploy the report.

As a side-note, SSRS 2005 caches reports. It will keep a .rdl.data cache file around which caches the dataset instead of always reexecuting dataset queries.
Try closing VS, deleting the .rdl.data file, and reopening again?

SSRS Pooled Connections Max

After adding 100+ SQL queries to a report, you may encounter the following error:

A connection cannot be made to the database. Timeout Expired. The timeout
period elapsed prior to obtaining a connection from the pool. This may have
occurred because all pooled connections were in use and max pool size was
reached.

This can be fixed by changing the Pooled value of the dataset from true to false.

As long as the performance of the report is not adversly affected and only a few users will be running the report, then this should not be a problem.

Welcome To My Blog

I will be attending the Microsoft MVP Summit in Seattle, Washington. Hope to see you there!

https://www.mvpsummit2008.com/public/home.aspx