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

Thanks for all the great suggestions, Tom!  Number two seems the most
likely candidate as I can't change the account under which the SQL
Server Agent runs and I'd like to keep the text files on our AS/400 (I
will be moving the code over to Websphere at some time - just need to
find time to do so!!).

Thanks again!

Brian.

-----Original Message-----
From: qsrvbas@xxxxxxxxxxxx [mailto:qsrvbas@xxxxxxxxxxxx] 
Sent: Monday, January 09, 2006 7:02 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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