[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Sql Reporting Services accessing VFP data on another server

I was running the 180 day trial version of SQL Server 2005 Reporting Services on a server that also housed a Visual FoxPro table.  The server is running Windows Server 2003.

We liked the product enough that we purchased a new server just to house the SQL Server 2005 and SRS.

When I moved all of the reports to the new server I am having trouble with all the reports that want to access FoxPro tables on another server.  All the SQL Server based reports work fine.  

The error I am receiving is:
"Query Execution failed for data set 'Departments'. (rsErrorExecutingCommand) Cannot open file \\IT-App\AppTrack$\Data\Department.dbf"

I am using the VFP OLE-DB Provider and the report works perfectly in the test environment and on the old test server.  However, running the SRS on one maching accessing a VFP data source using OLE-DB on another maching is not working.

Any ideas or suggestions would be very appreciated.

Thanks,
Jeremy

0
jscrime
Asked:
jscrime
  • 4
  • 3
1 Solution
 
kblack05Commented:
It looks like the error you are seeing is generated by a share, not the dbase engine. Perhaps the
\\IT-App\AppTrack$\Data\
share lacks permissions for you to connect to it?
0
 
jscrimeAuthor Commented:
I have added my network id with full permissions on the share.  I then told the report to prompt for credentials before running the report.  I used the same credentials in the prompt as were added to the share.

Same result, error opening file.  As soon as I copy the folder to the same server SRS is running everything works fine.  What good is an enterprise reporting solution if all of my Fox stuff has to reside on the reporting server.

Thanks for the feedback, do you have any other suggestions!!!!
0
 
kblack05Commented:
Can you connect to the share manually from the reporting system?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jscrimeAuthor Commented:
yes, I can connect to the share from the reporting server.  Also the report runs fine developing from my local machine and previewing on the new server.

Thanks,
Jeremy
0
 
kblack05Commented:
What error message is reported in the log file for the SQL server?
0
 
jscrimeAuthor Commented:
Here is a copy of the log just after running the report with an error:
The only area where log items were recorded for this time slot were under Management\Sql Server Logs\Windows NT

06/01/2006 07:58:40,Security,Success Audit,Special privileges assigned to new logon:<nl/><nl/>      User Name:      <nl/><nl/>      Domain:            <nl/><nl/>      Logon ID:            (0x0<c/>0x7F88C7)<nl/><nl/>      Privileges:      SeSecurityPrivilege<nl/>                  SeBackupPrivilege<nl/>                  SeRestorePrivilege<nl/>                  SeTakeOwnershipPrivilege<nl/>                  SeDebugPrivilege<nl/>                  SeSystemEnvironmentPrivilege<nl/>                  SeLoadDriverPrivilege<nl/>                  SeImpersonatePrivilege,Logon/Logoff,576,RPT-SERVER\sql,RPT-SERVER

06/01/2006 07:58:40,Security,Success Audit,Successful Logon:<nl/><nl/>      User Name:      sql<nl/><nl/>      Domain:            RPT-SERVER<nl/><nl/>      Logon ID:            (0x0<c/>0x7F88C7)<nl/><nl/>      Logon Type:      2<nl/><nl/>      Logon Process:      Advapi  <nl/><nl/>      Authentication Package:      Negotiate<nl/><nl/>      Workstation Name:      RPT-SERVER<nl/><nl/>      Logon GUID:      -<nl/><nl/>      Caller User Name:      NETWORK SERVICE<nl/><nl/>      Caller Domain:      NT AUTHORITY<nl/><nl/>      Caller Logon ID:      (0x0<c/>0x3E4)<nl/><nl/>      Caller Process ID: 2936<nl/><nl/>      Transited Services: -<nl/><nl/>      Source Network Address:      -<nl/><nl/>      Source Port:      -,Logon/Logoff,528,RPT-SERVER\sql,RPT-SERVER

06/01/2006 07:58:40,Security,Success Audit,Logon attempt using explicit credentials:<nl/><nl/>Logged on user:<nl/><nl/>      User Name:      NETWORK SERVICE<nl/><nl/>      Domain:            NT AUTHORITY<nl/><nl/>      Logon ID:            (0x0<c/>0x3E4)<nl/><nl/>      Logon GUID:      -<nl/><nl/>User whose credentials were used:<nl/><nl/>      Target User Name:      sql<nl/><nl/>      Target Domain:      RPT-SERVER<nl/><nl/>      Target Logon GUID: -<nl/><nl/><nl/>Target Server Name:      localhost<nl/><nl/>Target Server Info:      localhost<nl/><nl/>Caller Process ID:      2936<nl/><nl/>Source Network Address:      -<nl/><nl/>Source Port:      -,Logon/Logoff,552,NT AUTHORITY\NETWORK SERVICE,RPT-SERVER

06/01/2006 07:58:40,Security,Success Audit,Logon attempt by:      MICROSOFT_AUTHENTICATION_PACKAGE_V1_0<nl/><nl/>Logon account:      sql<nl/><nl/>Source Workstation:      RPT-SERVER<nl/><nl/>Error Code:      0x0,Account Logon,680,RPT-SERVER\sql,RPT-SERVER

06/01/2006 07:58:36,Security,Success Audit,User Logoff:<nl/><nl/>      User Name:      sql<nl/><nl/>      Domain:            RPT-SERVER<nl/><nl/>      Logon ID:            (0x0<c/>0x7E1A6C)<nl/><nl/>      Logon Type:      2,Logon/Logoff,538,RPT-SERVER\sql,RPT-SERVER

06/01/2006 07:58:36,Security,Success Audit,User Logoff:<nl/><nl/>      User Name:      sql<nl/><nl/>      Domain:            RPT-SERVER<nl/><nl/>      Logon ID:            (0x0<c/>0x7DDFC2)<nl/><nl/>      Logon Type:      2,Logon/Logoff,538,RPT-SERVER\sql,RPT-SERVER
0
 
jscrimeAuthor Commented:
I figured this one out.  It turns out that when using SRS on a VFP data source with VFPOleDB.DLL, the key security setting resides in the SRS Configuration Manager under the Execution Account.

This account must be set to an account with enough permissions on the server where the data resides.  Also, the data source must be set so that you check the credentials are not required.  Checking Windows Authentication will not work.

Thanks for your assistance.

Jeremy
0
 
DarthModCommented:
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now