calling a remote command from a sql stored procedures

[luckyboy]

Hi , i'm trying to run a remote command (or run a program) on an as400
machine (which is successfully added as a linked server) from within an
ms sql server2000 stored procedure..... I tried using rmtcmd with
xp_cmdshell like this

exec master..xp_cmdshell 'rmtcmd CLRPFM FILE(Z8IDFCPYC/TRIAL1)'

but i get the folloowing error:
"CWB4004 - Security error occurred for system "
which means as i guess that the username and password are not sent to
the remote system, although when i run the same command from the
command line it executes successfully wihtout promting for login(client
access is using a default user to connect) ,so what's the problem ? is
there another way to run a remote command from a stored procedure
?........

[Drew Dekreon]

Trying looking at wrkactjob and displaying the job. I believe the job is
running as a different profile - the userid/pw you supply authorizes
access, but the rmtcmd is actually running under one of the Qxx id's
(qdbsrvxr maybe?) which lacks authority for object changes like clrpfm.
A workaround would be to put the command in a CL programming that has
inherited authority to do clrpfm on that file. Call that program and
you're all set...
I'm sure someone else will offer a much better solution - I am not an
expert on mssql<>as400 topics.

Post by luckyboy
Hi , i'm trying to run a remote command (or run a program) on an as400
machine (which is successfully added as a linked server) from within an
ms sql server2000 stored procedure..... I tried using rmtcmd with
xp_cmdshell like this
exec master..xp_cmdshell 'rmtcmd CLRPFM FILE(Z8IDFCPYC/TRIAL1)'
"CWB4004 - Security error occurred for system "
which means as i guess that the username and password are not sent to
the remote system, although when i run the same command from the
command line it executes successfully wihtout promting for login(client
access is using a default user to connect) ,so what's the problem ? is
there another way to run a remote command from a stored procedure
?........

[Steve Richter]

Post by luckyboy
Hi , i'm trying to run a remote command (or run a program) on an as400
machine (which is successfully added as a linked server) from within an
ms sql server2000 stored procedure..... I tried using rmtcmd with
xp_cmdshell like this
exec master..xp_cmdshell 'rmtcmd CLRPFM FILE(Z8IDFCPYC/TRIAL1)'
"CWB4004 - Security error occurred for system "
which means as i guess that the username and password are not sent to
the remote system, although when i run the same command from the
command line it executes successfully wihtout promting for

login(client

Post by luckyboy
access is using a default user to connect) ,so what's the problem ? is
there another way to run a remote command from a stored procedure
?........

lucky,

Maybe you dont have an active client access session between the sql
server PC and the 400.

are you using client access? is the as400 release v5r2 or higher? if
so, you can use the iDb2Connection and iDb2Command classes to create a
stored procedure which calls a cl program on the as400

void linkCreateProcedure_Click(object sender, EventArgs e)
{
String cs = "Server=192.168.1.160:446; " +
"Database=S104VDNM; " +
"UID=QSECOFR; PWD=xxxxxx;";

DB2Connection conn = null ;
DB2Command cmd = null ;

// connect to the as400.
try
{
conn = new DB2Connection(cs);
conn.Open();

// drop the stored procedure
try
{
cmd = new DB2Command();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DROP PROCEDURE FAXMAIL.TEST35A" ;
cmd.ExecuteNonQuery();
}
catch (DB2Exception)
{
}

// create the procedure
cmd = new DB2Command();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "CREATE PROCEDURE FAXMAIL.TEST35A (" +
"IN TranCode CHAR(1), " +
"IN FULLNAME VARCHAR(80), " +
"OUT ADDRESS VARCHAR(256)) " +
"LANGUAGE RPGLE " +
"DETERMINISTIC " +
"NO SQL " +
"SPECIFIC TEST35A " +
"PARAMETER STYLE GENERAL " +
"EXTERNAL NAME 'FAXMAIL/TEST35A'";
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}

void linkRunProcedure_Click(object sender, EventArgs e)
{
String cs = "Server=192.168.1.160:446; " +
"Database=S104VDNM; " +
"UID=QSECOFR; PWD=xxxxxx;";
DB2Connection conn = null;
DB2Command cmd = null;
// connect to the as400.
try
{
conn = new DB2Connection(cs);
conn.Open();

// run the procedure
int Vlu1 = 25 ;
string tranCode = "a";
string fullName = "Scott Mitchell";
string address = null ;
DB2Parameter parm = null;
cmd = new DB2Command();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure ;
cmd.CommandText = "FAXMAIL.TEST35A" ;
cmd.Parameters.Add(new DB2Parameter("TRANCODE", tranCode ));
parm = new DB2Parameter("FULLNAME", DB2Type.VarChar, 80);
parm.Direction = ParameterDirection.Input;
parm.Value = fullName;
cmd.Parameters.Add(parm);
parm = new DB2Parameter("ADDRESS", DB2Type.VarChar, 256);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}

[luckyboy]

Steve,
i do have a client access session,i can run the same command from the
command prompt of the same sql server machine without being prompted
for a user name or password (client access uses a default user )..
yes it's iSeries V5.somehting , how can i run a remote command(program)
from within a stored procedure , i don't get u , do i have to create
the stored procedure programmatically?!

Post by Steve Richter
lucky,
Maybe you dont have an active client access session between the sql
server PC and the 400.
are you using client access? is the as400 release v5r2 or higher?

if

Post by Steve Richter
so, you can use the iDb2Connection and iDb2Command classes to create a
stored procedure which calls a cl program on the as400
void linkCreateProcedure_Click(object sender, EventArgs e)
{
String cs = "Server=192.168.1.160:446; " +
"Database=S104VDNM; " +
"UID=QSECOFR; PWD=xxxxxx;";
DB2Connection conn = null ;
DB2Command cmd = null ;
// connect to the as400.
try
{
conn = new DB2Connection(cs);
conn.Open();
// drop the stored procedure
try
{
cmd = new DB2Command();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "DROP PROCEDURE FAXMAIL.TEST35A" ;
cmd.ExecuteNonQuery();
}
catch (DB2Exception)
{
}
// create the procedure
cmd = new DB2Command();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "CREATE PROCEDURE FAXMAIL.TEST35A (" +
"IN TranCode CHAR(1), " +
"IN FULLNAME VARCHAR(80), " +
"OUT ADDRESS VARCHAR(256)) " +
"LANGUAGE RPGLE " +
"DETERMINISTIC " +
"NO SQL " +
"SPECIFIC TEST35A " +
"PARAMETER STYLE GENERAL " +
"EXTERNAL NAME 'FAXMAIL/TEST35A'";
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}
void linkRunProcedure_Click(object sender, EventArgs e)
{
String cs = "Server=192.168.1.160:446; " +
"Database=S104VDNM; " +
"UID=QSECOFR; PWD=xxxxxx;";
DB2Connection conn = null;
DB2Command cmd = null;
// connect to the as400.
try
{
conn = new DB2Connection(cs);
conn.Open();
// run the procedure
int Vlu1 = 25 ;
string tranCode = "a";
string fullName = "Scott Mitchell";
string address = null ;
DB2Parameter parm = null;
cmd = new DB2Command();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure ;
cmd.CommandText = "FAXMAIL.TEST35A" ;
cmd.Parameters.Add(new DB2Parameter("TRANCODE", tranCode ));
parm = new DB2Parameter("FULLNAME", DB2Type.VarChar, 80);
parm.Direction = ParameterDirection.Input;
parm.Value = fullName;
cmd.Parameters.Add(parm);
parm = new DB2Parameter("ADDRESS", DB2Type.VarChar, 256);
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}

[Steve Richter]

Post by luckyboy
Steve,
i do have a client access session,i can run the same command from the
command prompt of the same sql server machine without being prompted
for a user name or password (client access uses a default user )..
yes it's iSeries V5.somehting , how can i run a remote

command(program)

Post by luckyboy
from within a stored procedure , i don't get u , do i have to create
the stored procedure programmatically?!

you dont have to, just something else to try to troubleshoot the
problem.

As you work to get an answer the release level will be important.
There are 3 v5 release levels: v5r1, v5r2, v5r3. At v5r2 IBM release a
.net managed provider in client access that made it easier to access
the 400 from .NET.

To isolate if your security problem is on the SQL Server or once your
code starts to run on the 400, try to run the SNDMSG command instead of
CLRPFM.
SNDMSG MSG('hello') TOMSGQ( QSYSOPR )
If the message is sent you can use it to trace back to the as400 server
job that sent the message. ( cursor on the message, press F1=help )

There is only so much I know. What is the rmtcmd stored procedure you
are running? Is that supplied by IBM? client access?

-Steve

[luckyboy]

Post by Steve Richter

Post by Steve Richter
you dont have to, just something else to try to troubleshoot the

problem.
As you work to get an answer the release level will be important.
There are 3 v5 release levels: v5r1, v5r2, v5r3. At v5r2 IBM release a
.net managed provider in client access that made it easier to access
the 400 from .NET.
To isolate if your security problem is on the SQL Server or once your
code starts to run on the 400, try to run the SNDMSG command instead of
CLRPFM.
SNDMSG MSG('hello') TOMSGQ( QSYSOPR )
If the message is sent you can use it to trace back to the as400 server
job that sent the message. ( cursor on the message, press F1=help )
There is only so much I know. What is the rmtcmd stored procedure you
are running? Is that supplied by IBM? client access?
-Steve

Samething happens when sending a message.When i run the command :

rmtcmd "SNDMSG MSG('hello') TOMSGQ( QSYSOPR )"
it works fine , but when i run the same command from a sql store
procedure it gives me the same security error.. The stored procedure
contains nothing but the following :
exec master..xp_cmdshell 'rmtcmd "SNDMSG MSG(''hello'') TOMSGQ(
QSYSOPR )"'

rmtcmd is a tool provided by ibm client access to run remote commands
from the shell, it does work well from the shell but it doesn't work
using xp_cmdshell!! although xp_cmdshell should give the same results
,shouldn't it ?

[Steve Richter]

Post by luckyboy

Post by Steve Richter

Post by Steve Richter
you dont have to, just something else to try to troubleshoot the

problem.
As you work to get an answer the release level will be important.
There are 3 v5 release levels: v5r1, v5r2, v5r3. At v5r2 IBM

release

Post by luckyboy
a

Post by Steve Richter
.net managed provider in client access that made it easier to access
the 400 from .NET.
To isolate if your security problem is on the SQL Server or once your
code starts to run on the 400, try to run the SNDMSG command

instead

Post by luckyboy
of

Post by Steve Richter
CLRPFM.
SNDMSG MSG('hello') TOMSGQ( QSYSOPR )
If the message is sent you can use it to trace back to the as400

server

Post by Steve Richter
job that sent the message. ( cursor on the message, press F1=help )
There is only so much I know. What is the rmtcmd stored procedure

you

Post by Steve Richter
are running? Is that supplied by IBM? client access?
-Steve

rmtcmd "SNDMSG MSG('hello') TOMSGQ( QSYSOPR )"
it works fine , but when i run the same command from a sql store
procedure it gives me the same security error.. The stored procedure
exec master..xp_cmdshell 'rmtcmd "SNDMSG MSG(''hello'') TOMSGQ(
QSYSOPR )"'
rmtcmd is a tool provided by ibm client access to run remote commands
from the shell, it does work well from the shell but it doesn't work
using xp_cmdshell!! although xp_cmdshell should give the same results
,shouldn't it ?

I know about rmtcmd. it is the xp_cmdshell I had never heard of.

So you run "rmtcmt" from the cmd.exe shell and it works, but you run
"rmtcmd" from the xp_cmdshell stored procedure and it does not. Reading
about xp_cmdshell, it looks like it effectively runs cmd.exe so who
knows.

Maybe there is an authority issue with the user account that the sql
server runs as. Where that PC account is not authorized to the client
access directories or files. What if you temporarily elevate the SQL
Server user account to administrator?

I am pretty sure the CWB error message you are getting is from client
access. You could root around in the client access menus and run its
trace facilities. Trace in all the IBM connectivity products is very
good. Once you know how to use it it will give you more information
about the error.

Another option is to try the .NET code I posted earlier.

good luck,

-Steve