Oracle Database Discussions

Hi,

I am on Oracle Cloud Infrastructure. I have an autonomous database in a private subnet.

I want to create a Database link to a SQL server that is connected to my on premise network. I was able to connect to SQL server from Oracle Data integration service in the cloud. So the host info, user/password information is correct, and firewall/ACL rules are in place to connect.

I tried the following:

BEGIN 
    DBMS_CLOUD.CREATE_CREDENTIAL( 
        credential_name => 'SQL_SERVER_CRED', 
        username => 'etl_user_01', 
        password => '*****' 
    ); 
    DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK( 
        db_link_name => 'SOURCE_SQLSERVER_DBLINK_01', 
        hostname => '10.10.10.10', 
        port => '1433', 
        service_name => 'DB_NAME', 
        credential_name => 'SQL_SERVER_CRED', 
        gateway_link => TRUE, 
        directory_name => NULL, 
        private_target => TRUE, 
        gateway_params => JSON_OBJECT('db_type' value 'azure')); 
END; 

But when I query the linked server like the following:

SELECT * FROM TABLE_NAME@SOURCE_SQLSERVER_DBLINK_01;

I am getting below error.

ORA-28500: connection from ORACLE to a non-Oracle system returned this message: 
[DataDirect][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host Name and Port Number. {08001}

My question is:

• Should I enter the database name in the service_name parameter? Is that right? If not where should I specify SQL database name?
• If I specify the database name somewhere else what should go in service_name? It looks like a required parameter.
• Is it ok to have gateway_link and private_target to true? I remember reading somewhere when private_target is true I should provide a hostname and not an IP address so is it ok to provide IP address? I don't have any host name for the server.
• Anything else I should do to fix the connection?

Thanks,
Nachi