SQL Agent login to UNC folders?

[jjob]

Hi,

My job is to get a DTS package on my SQL Server machine to load a text file from a folder on another machine. I have no control over either machine, both are hosted by an ISP, one is a SQL Server machine holding 2 SQL 2000 databases of mine, as well as other users databases. The second machine is a web server which holds my web application that uses one of the 2 databases (it could use either, one is a test database)

The plan is to FTP a file to a folder in the actual web site folder - and this I can do.

Then to schedule the DTS on the SQL server machine to read this file.

At present, the FTP folder is not shared, so I have no UNC path that I can give to SQL server. I would like it shared so I can have that path.

My ISP tech support seem concerned that what I want to do is dangerous (the share would be open to the world) and is not feasible, as the two machines are not in the same domain. I could be confused here, as these seem contradictory claims, if it was open to the world that is bad, and so I can understand them not allowing it, but if they are then saying that is because the machines are not o the same domain and they need to be for security restrictions to work I am really puzzled.

It strikes me that if the IIS can talk to the SQL server, I can't see why, once configured, the SQL Agent of the server can't talk to the folder I want on the web server, without compromising security.

I think that the folder needs to be shared, that this share needs permissions so that my SQL Agent login can be used to grant access to the folder. i.e the login and password that SQL Agent uses on the Database server need to be set up as a user/group with permission to access the new share on the web server, of the folder containing my uploaded file.

Are my assumptions above correct, or is my ISP's tech support correct and I just don't understand the security system of Windows sufficiently?

I assumed that SQL Agent, when it tries to access a UNC resource, is asked to provide a login and password - am I right, and does it matter where or what domain the machine at then end of the UNC path is?

TIA

John

 

[rickholc]

Most ISPs will not enable file and print sharing on machines that are not completely blocked from outside access. Both machines would need to be behind the same firewall with the ports used for file and print sharing blocked from outside access by that firewall. In this case they must not be able to do that because the machines are in two different locations. It may be possible to establish a VPN connection between the two machines but only if these two machines were dedicated for your use only.
I have done something similar in the past but put the work into the webserver. It opened the file that was ftp'd to it and then used insert statements to insert the records into the SQL tables. This means the file never has to be read directly by the SQL server. The web server does all the work. To go any further you now need to know ASP and how to open and close local data files. DTS would not be involved.

 

[mrdenny]

Your severs are the ISPs servers, and they probally won't do much to get this done for you (read this as anything).

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jjob]

The ASp route is the one we've gone, which is a pity, as I've spent a lot of time moving as much SQL and recordset processing as possible out of the scripts into the database as stored procedures

The real frustration is seeing how SQl server and it's tools are being hamstrung. But who knows, one day we may get our own server and sort it out from there - although I am intrigued as to why the two cannot verify each other for this type of work, when they obviously do so for the web site processing of tables etc, or why we can't have an ftp folder on the database server, then the security would be between the database and its own server OS.