• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

SQL Server Linked Server in .NET

I have a linked server in SQL Server 2005 to a foxpro database. I have written a stored proc that combines SQL Server and my foxpro tables.

I am able to run the stored proc inside of SQL Server and inside of Visual Studio 2005.

When I try to browse the page on the web I receive the following error:

I have looked at all the security options that I can think of, does anyone have any idea as to why my ASPNET account can't browse the website?

Thanks
0
irmsystems
Asked:
irmsystems
  • 6
  • 5
1 Solution
 
irmsystemsAuthor Commented:
Forgot to put the error

Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "DCCTCNET"
0
 
vo1dCommented:
you have to register the vfpoledb.dll on the server side.
0
 
vo1dCommented:
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
irmsystemsAuthor Commented:
I'm using VFP 7.0

I have one linked server that works correctly with an ASP.NET application but can't get any other ones to work. I am not sure what the problem is. I've looked at security, both on the SQL Server end and in IIS. The ASPNET user has access to the folder structure where the VFP database is.

0
 
vo1dCommented:
what kind of filesystem do you use? ntfs? then check the filesystem rights if the user for your page has enough acces rights to the dbf tables.
0
 
vo1dCommented:
but what your error message says, it seems that you have not register your dll in your page folder correctly.
0
 
irmsystemsAuthor Commented:
I have registered the vfpoledb.dll I have checked the filesystem rights on the dbf tables and the ASPNET user has full access.
0
 
vo1dCommented:
did you also double check, under which account you connected to the sql server? does that account have acces rights to the table?
maybe it is possible, that you are connected via a guest account and that account has not enough rights.
0
 
irmsystemsAuthor Commented:
Ok I got this to work. I was able to run the Stored procedure in SQL Server and in the development environment of Visual Studio from the beginning. The problem was running the query from the web page when browsing.

I went into IIS and modified the directory security. I was using the ASPNET user account and unchecked the allow IIS to control the password option. I can now view the result set in a grid. I cannot however view the results in a Crystal Report while browing, I get the following error:

Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB" for linked server "DCCTCNET". SQL State: 42000 Native Error: Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: The OLE DB provider "VFPOLEDB" for linked server "DCCTCNET" reported an error. Access denied. SQL State: 42000 Native Error: Failed to open a rowset. Error in File C:\DOCUME~1\GUENTH~1\ASPNET\LOCALS~1\Temp\ContactListsReport {F1D8A743-FD38-4F94-9666-4C6BB00A3056}.rpt: Failed to open a rowset.

Any ideas as to why I couldn't run the same stored procedure that I do in my GridView in the Crystal Report?

Thanks
0
 
irmsystemsAuthor Commented:
I have it all working for now in the testing environment that I'm using.

I modified the SQL Server service in Services to logon using the Local System Account. I had it loggin in using my domain account.

I hope this can help others out, this was quite the pain.
0
 
vo1dCommented:
thats fine. yes its very hard if you try to connect different systems with different security approaches.
what you should do is that you create a special account which has only small rights to connect to the different systems.
0
 
GranModCommented:
PAQed with points refunded (500)

GranMod
Community Support Moderator
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now