Solved

Getting network error using utl tcp package

Posted on 2016-09-29
7
157 Views
Last Modified: 2016-10-02
Oracle 11g
Linux red hat

New to FTP. Trying out UTL TCP program used in Tim Hall's FTP package. Created an FTP site and I am able to access. The plsql program gives the following error

DECLARE
  l_conn  UTL_TCP.connection;
BEGIN
  l_conn := ftp.login('ftp.ftpsite.com', '21', 'uname', 'pwd');
  ftp.ascii(p_conn => l_conn);
  ftp.get(p_conn      => l_conn,
          p_from_file => '/ftpfolder/filename.csv',
          p_to_dir    => 'mydir',
          p_to_file   => 'xyz.csv');
  ftp.logout(l_conn);
END;
/

ORA-29260: network error: not connected
ORA-06512: at "SYS.UTL_TCP", line 235
ORA-06512: at "SYS.UTL_TCP", line 473
ORA-06512: at "HR.FTP", line 267
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "HR.FTP", line 479
ORA-06512: at line 6
29260. 00000 -  "network error: %s"
*Cause:    A network error occurred.
*Action:   Fix the network error and retry the operation.


I have added the ftp site to acl list. Is there anything I should look into..

Thanks,
0
Comment
Question by:gs79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 

Author Comment

by:gs79
ID: 41822815
I installed sdstuber's ftp program and still i get the same error. It must be in the way i created ftp site but not sure. Let me know if you have come across this issue

Thanks
0
 
LVL 37

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 50 total points
ID: 41823000
you need to grant the user the network priviliges to that host
that's what this line means > ORA-24247: network access denied by access control list (ACL)

you'll need to create an acl and assign it to your ftp network
extensive guide is here:
https://oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
ID: 41823316
Please post the ACL grants you ran.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:gs79
ID: 41823559
I ran this as dba on my sandbox database

BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl (
acl => 'ftp_permissions.xml');
COMMIT;
END;


begin
dbms_network_acl_admin.create_acl (
acl => 'ftp_permissions.xml', -- or any other name
description => 'ftp Access',
principal => 'HR', -- the user name trying to access the network resource
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/

commit;



 begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ftp_permissions.xml',
principal => 'HR',
is_grant => true,
privilege => 'connect');
end;
/

commit;


 begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'ftp_permissions.xml',
principal => 'HR',
is_grant => true,
privilege => 'resolve');
end;
/

commit;

BEGIN
dbms_network_acl_admin.assign_acl (
acl => 'ftp_permissions.xml',
host => 'ftp.ftpsite.com', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/
lower_port => 21,
upper_port => 21
);
END;
/

I see the acl connection I created using below query


 SELECT *
FROM dba_network_acls;


ftp.ftpsite.com      21      21

Thanks
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 400 total points
ID: 41823676
my ftp package (and I believe Tim Hall's as well) uses passive ftp connections.

This means there is a secondary communication port.  That port is not something you specify, it is determined by the server.  So, for your ACL, try using NULL (meaning all ports) instead of 21.  That way you'll be able to communicate on the passive port.

or, if you want to be more precise,  the passive port range should be  1024 - 65535  so you should be able to use 21-65535 for the lower-upper range to include the connection/control port as well as the passive port
0
 

Author Comment

by:gs79
ID: 41823902
Bingo, worked!

@sdstuber What do you recommend as best practice. To send a file to third party offsite, do you recommend generating file and use shell program to send the file or use oracle plsql? I think there is lot of flexibility in leveraging utl_tcp/http, will there be security implications. Reading more about active and passive ftp connection

Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 41824071
If your data is in the database I'd lean toward pl/sql.  Using my package you don't even need to generate a file (although you can if you want/need to.)  You can send a clob or blob directly from the db to the remote server as a file without needing to create a physical file locally.

Security is important, but if you control your ACL grants (the principal assignments) and limit access to the package it shouldn't be a problem keeping the sending secure.
As for the acl ports, they are no more or less secure than what you need to do it for a client.  Think of it the acl like an extra firewall layer.  If your external firewall doesn't allow communication across ports 1024-65535 then your external client won't be able to talk to the remote server either.

If your data is not inside the database then I'd probably use something external to the db.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question