0

I deployed my report to the report server. This report has some DB connection which uses Windows Authentication.

When I run the report from the same server works fine, but when I try this from a remote sever I errors saying EXECUTE permission denied on the stored procedure.

The EXECUTE permission was denied on the object 'sp_xxx', database 'DB', schema 'dbo'.

I did further analysis and found that when the report try to connect to the DB is uses NT AUTHORITY\ANONYMOUS LOGON login.

And the report server is running under NT Authority\NetworkServices.

p.s: Also the remote server and the report server is in the same domain.

What am I doing wrong which is not passing my Windows authentication?

Please help...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TTK
  • 33
  • 6
  • All reports do this or just this one? If all, it sounds like the report server is not receving your Windows credentials, it's sees you as "anonymous".You could try the IE settings in security, add the SSRS report server to the local intranet zone? – Jerry Ritcey Mar 26 '14 at 02:43

2 Answers2

0

If you have access to the server you should define a ReportUser user that has access to ssrs and mssql since you are using windows auth. When you deploy, the dataset credentials should be set to the same user above. This would be akin to going into ssrs and setting the configuration of the dataset of the report to a known user account with sufficient privileges to run the report and query the database.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
0

I'll try to walk you through, I hope I haven't forgot something

Can you connect to your server remotely using management studio and your admin credentials? (Check TCP/IP is enabled under SQL Server configuration manager)

Have you assigned enough permissions to the security role? have you assigned the user to the role?(check this out - Grant execute permission for a user on all stored procedures in database?)

Can you successfully connect with your data source? (Check username and password are working)

Community
  • 1
  • 1
UV.
  • 492
  • 6
  • 9
  • Thanks UV. But I am using windows Authentication and I can connect to the DB also I have enough permissions on the SQL DB. My concern is why my domain credentials are changing to NT AUTHORITY\ANONYMOUS LOGON when connecting to the DB. – TTK Mar 26 '14 at 22:00
  • Do you have both machines in the same domain? What version of SQL and active directory are you using? – UV. Mar 26 '14 at 22:14
  • Yes I have both Machines on the same domain. SQL Server R2 and Windwos Server 2008. – TTK Mar 26 '14 at 22:25
  • Are you using IE? have you checked "Enable Integrated Windows Authentication"?, it's under the **Internet options** > **advanced** tab. Also, make sure you are logging on to the domain and not to the local machine. – UV. Mar 26 '14 at 22:50
  • Thanks UV. I have "Enable Integrated Windows Authentication" enabled and I am logging into the Domain. – TTK Mar 26 '14 at 23:21
  • Please create a new report, add a text box with **=User!UserID**. does it show your original credentials from web access? – UV. Mar 27 '14 at 01:09
  • I created a textbook with =User!UserID and it shows my credentials in web (ie: I deployed to the report server and ran the report from a remote server) – TTK Mar 27 '14 at 03:03
  • open your data source, connection string: -> Edit... make sure it's marked as "Use Windows Authentication". under the project properties, make sure "Overwrite Data Source" = TRUE, otherwise the data source can be using faulty configuration and the update will not pass – UV. Mar 30 '14 at 23:16