Interserver and openrowset

Question

Interserver and openrowset

sradoff

SSC Rookie

Points: 41
More actions
June 7, 2011 at 2:19 pm

#99394

I have two Windows 2003 servers connected by a local network. One server has SQL Server 2000. The other is a Web server. The Web server has functionality that stores an Excel workbook in a local directory. The database server has a sproc that reads the Excel workbook with OpenRowSet. The sproc uses a dynamic query to build the address portion of openrowset. The Web server is addressed with its IP using UNC. All this works perfectly when I execute the query using Query Analyzer from the SQL Server server using the IP address of the Web server. However in production I implement the sproc using a VB6 COM+ class via a command object. If I use the IP address of the SQL Server server, all works fine. If I use the IP of the WEB server, I receive the failure: OLE/DB provider returned message: Failure creating file. The source file security is set to Everyone with full control. Sharing permissions is set to Everyone with full control. Any thoughts would be mostly appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Is the VB6 COM+ installed as a COM+ application on the SQL server or is it just a COM dll that is called by something else?

Basically it sounds like the process that is using the VB6 class is running under an account that does not have network access permissions - not file permissions or share permissions, but permission to access the network as a whole, one such user being Local System....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sradoff SSC Rookie Points: 41 More actions · Answer 2

The VB6 COM+ is installed as a COM+ application on the Web server. Both the Web and SQL Server O/S run in the same workgroup. There is a group on each server that has an account with the same credentials. This account has a Local Policy, User Rights Assignment, Log on as service. I may need some guidance to establish the account access the the network such as Local System.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

sradoff (6/7/2011)
The VB6 COM+ is installed as a COM+ application on the Web server. Both the Web and SQL Server O/S run in the same workgroup. There is a group on each server that has an account with the same credentials. This account has a Local Policy, User Rights Assignment, Log on as service. I may need some guidance to establish the account access the the network such as Local System.

Sorry, I just re-read your first post and you said it works fine from QA but not from your code...so my last post was meaningless!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Interserver and openrowset

Cookies on SQLServerCentral