How to Enable Remote errors on SQL reporting services.

If you have been using SQL reporting services, you may have encountered few errors which just become difficult to resolve. In such cases, its advisable to enable the remote errors on your reporing services which helps you to understand the inner exceptions.

The remote errors can be enabled on the reporting services in two ways:

1. Using the SQL Server Management Studio

Start Management Studio and connect to a report server instance.
Right-click the report server node, and select Properties.
Click Advanced to open the properties page.
In EnableRemoteErrors, select True.
Click OK.

2. Using a script to enable remote errors:

a. Create a text file and copy the following script into the file.

Public Sub Main()
Dim P As New [Property]()
P.Name = "EnableRemoteErrors"
P.Value = True
Dim Properties(0) As [Property]
Properties(0) = P
Try
rs.SetSystemProperties(Properties)
Console.WriteLine("Remote errors enabled.")
Catch SE As SoapException
Console.WriteLine(SE.Detail.OuterXml)
End Try
End Sub

b. Save the file as .rss in your local folder.
c. Open a command prompt window
d. Either Navigate to the directory that contains the .rss file you just created or use the absolute file location on the command prompt. Type the following command line, replacing servername with the actual name of your server:

rs -i EnableRemoteErrors.rss -s http://servername/ReportServer

3. Enabling the remote errors by updating the database.

a. Update ReportServer database Configuration table for EnableRemoteErrors

Update the ConfigurationInfo table in the ReportServer database for configuration record named "EnableRemoteErrors". The default value for EnableRemoteErrors property in the ConfigurationInfo database table is "False". Update the value to true by running a simple update statement.

You may not be successful to see the changes made on the ConfigurationInfo table on the Reporting Services application, if the application is being used intensely.

If everything goes fine, you should see a message "remote error enabled."

Note: Do ensure that your server is accessible from the location / machine you run the script from. If you have direct access to the server, run the script on the server.

Enabling the remote errors should give you detailed information on all the issues you encounter on your reporting services.

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 u

This is one of the errors you will get if you have used Network Library with your connection string to indicate the connection uses TCP/IP and not named pipes.

SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Lately I was working on a new ASP.net application when I had to deploy the application on one machine and the database on the other. Other machine only had SQL Server 2000 deployed. Though I had been receiving the errors:

"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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) "

"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

After trying many posts, google searches I was still unable to resolve the error. Most importantly I was not sure why I would get an error for SQL 2005 when I was using only SQL Server 2000.

The second error is encountered when you put Network Library property with your connectionstring. Apparently adding the network library is used to indicate that your connection will use TCP/IP and not Named Pipes.

Anyway, I found couple of interesting solutions during my search. Though they have not helped me (I guess must be something wrong with my configuration), these might help you.

Solution 1:

Go to, Start >> Programs >> Microsoft SQL Server 2005 >> Configuration
Tools >> SQL Server 2005 Surface Area Configuration >> Surface Area
Configuration for Services and connections.

Within this check whether "Local and remote connections" is choosen.
If not choose it :)

- Open the "SQL Server Configuration Manager" (under Configuration Tools)
- Expand the "SQL Server 2005 Network Configuration"
- Select the "Protocols for "
- Set the Named Pipes To Enabled

SOLUTION 2:

1. Click Start, click Run, type cliconfg, and then click OK.
2. In the SQL Server Client Network Utility dialog box, click the Alias tab, and then click Add.
3. In the Add Network Library Configuration dialog box, under Network libraries, click TCP/IP.
4. In the Server alias box, type the IP address of the computer or the
name of the computer that is running SQL Server, and then click OK.

SOLUTIONS 3:

Enable the TCP/IP protocol using the Surface Area Configuration Utility
Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances
Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall.
Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports that starting and stopping the SQL Server and Browser software is not enough.