4

We have Report server services in SharePoint Integrated mode and it was working up until last week but now when ever we are trying to open a report or data source we are getting the below error

Report Server could not access this web application's content database. The application pool account may not have permission. Navigate to the properties page of this SQL Server Reporting Services service application and ensure it has appropriate permissions. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException:

When i went through the event viewer i am seeing the below

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

But the SharePoint functionality is working fine only thing which is not working is the report server .I checked the permissions of the report service application account on the content DB and its fine

Can you please suggest what else i need to do

Update 1 : I deleted the service application and recreated again but its still happening again , i am not sure where i am going wrong

Update 2 : I restarted all the SharePoint servers and also checked whether the service application account can logon to SQL and the issue is still there

Update 3 : I have even now uninstalled and re created reporting services from Scratch and the issue is still there

Thanks

6
  • Did you check if your datasources are approved and a report major version is published? Also you seem to be using named pipe connection to SQL, try changing it to TCP using sql client tools
    – reva
    Aug 5, 2014 at 20:20
  • Hi Reva ,yes the data sources and reports have major versions that are published and We are using Always on in SQL so we use the listeners to connect to SQL and i checked the listeners too and everything is fine except the reporting services...Also even the reporting service application comes up fine in the central admin
    – Anil
    Aug 5, 2014 at 20:52
  • Anil, Did you check if the Application Pool assigned to the web application has the required permissions to the SQL Db which is used in the reports?
    – reva
    Aug 5, 2014 at 22:25
  • I would check firewalls. Can one server ping the other, and communicate over the designated ports? Aug 6, 2014 at 1:18
  • Have you checked that the account in the secure store service that you are using to pass credentials is correct? Aug 6, 2014 at 8:28

5 Answers 5

1
+50

The problem is occuring because the account in use for the service application has to make multiple hops on the network to authenticate with SQL Server for authentication. If the SQL instance is using windows authentication for the service account it will never authenticate via the service application because it has to make multiple hops before authenticating with the database. The reason why you can verify the account access is because you are making one hop. Follow the below article to register the service account as Service Principle Name account.

http://msdn.microsoft.com/en-us/library/cc281382.aspx

I had the same issue before.

0

Try giving access through powershell commands:

$w = Get-SPWebApplication -Identity http://xyz
$w.GrantAccessToProcessIdentity("myDomain\srsAccount")

Follow this article for creating reporting service account: http://support.microsoft.com/kb/2723587?wa=wsignin1.0

Hope it helps!! :)

3
  • Note: srsAccount is account of the app pool running the reporting services Aug 8, 2014 at 10:30
  • I tried that option before but its still not working :(
    – Anil
    Aug 8, 2014 at 18:07
  • Try connecting sql server from your SP Server through SQL Management Studio with SRS Account(With run as option) and see if it can access the database. Only if you can instll Management studio on the SharePoint Server for testing ;). Another thing, In your "Update3", you've recreated the service app, so now try following the article and verify. Aug 11, 2014 at 12:17
0

I had similar issues when trying to configure PowerPivot Server on a SharePoint 2010 On Premise. I finally got it working by giving database access through PowerShell commands. It appears that accessing database with R/W access, via Management Studio, with the dedicated service account, was not relevant. PowerShell did the trick.

1/ Get GUID from all farm's databases

Get-SPDatabase | Where-Object {$_.WebApplication -like "SPAdministrationWebApplication"}

2/ Set databases admin rights to the Report Server Account

Add-SPShellAdmin -UserName DOMAIN\RSACCOUNT -database 418658e3-167c-48ef-ac01-2fbc8f0ba1ad

Hope that still remains useful on 2013.

0

Does your Reporting Services Account have the Log On As A Service permission? I had a similar problem with Integration services and solved it by adding that permission through the MMC console then reapplying the logon settings of the service account in the Services console and restarting the service.

0

Did you start Reporting Services in SharePoint?

Run the following commands in powershell to make sure the service is running

Install-SPRSService

Install-SPRSServiceProxy

Get-SPServiceInstance -all | where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

Launch Central Admin and make sure the reporting services are started. If not, start them manually.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.