RE: RE: Moving a text file to a physical file

midrange-l-request@xxxxxxxxxxxx wrote:

>   8. RE: Moving a text file to a physical file (Brian Piotrowski)
>
>Moving the file through the MSSQL server is a piece of cake.  The big
>problem I am having is trying to get the AS/400 to allow the SQL server
>to access the text files on the IFS.
>
>I haven't found an easy way to allow the DTS package on the SQL server
>access the AS/400 file share.  Because you cannot simply access the
>shared drive on the AS/400 (due to enhanced security), I can't have the
>program run and grab the file.  It always fails with an "access denied"
>error.
>
>So the only other way I can think I can get around this is to transform
>the data on the AS/400 and then import it through an ODBC connection on
>the SQL Server.

Brian:

Okay, so the problem becomes one of permissions/authorities which is more like 
what I expected.

Three general possibilities come to mind:

1. Make sure that the user that runs the process on the SQL Server system 
matches the logon request to your IFS.

2. Create a new share that only shares the file(s) you want to transfer and 
open this to a [guest] profile through NetServer.

3. Have the AS/400 send the file to a /QNTC share on the SQL Server system.

For (1.), the process that attempts to access the file needs to be able to 
logon to the server. This can be done by creating a user on both the SQL Server 
system and your AS/400; the user and passwords would match and the process 
would need to be run as that user on the PC.

Or perhaps a connection could be established using an iSeries Access function 
so that a matching user/password isn't needed. I think the simplest way to do 
that is through the CWBLOGON command:

To signon to a server:

  CWBLOGON  system   /u  userid   /p  password

To clear a specific userid:

  CWBLOGON  system   /u  userid   /c

The process on your SQL Server might do that before attempting to copy from 
your IFS. That can prepare for a connection that doesn't need matching 
user/password.

For (2.), you can go into iSeries Navigator and drill down to NetServer 
properties. Under the [Advanced] tab, you can specify a "guest" profile. You 
create this user profile on your AS/400 with no authority to do anything 
(except _possibly_ very specific things you might choose for your purposes). 
Then, when a user attempts a connection and no matching profile exists, this 
becomes the profile that's used for the connection. Your SQL Server process 
might be running under Administrator or some other user that makes no sense to 
your AS/400; the "guest" profile could help perhaps.

For (3.), well, it's pretty much the reverse of the problems you're currently 
having. You'll still need a logon from your AS/400 onto your SQL Server system 
or onto some other PC server that can be accessed by SQL Server. You might find 
that it's easier to create matching profiles by bringing an intermediate PC 
server into the mix.

I'm not certain any of these general directions will help, but one might get 
you going.

Tom Liotta