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