I am trying to create a linked server in AWS RDS to an Oracle Database. I created using the t-sql scripts as I could not find the list of Providers under the Linked Server in SSMS. And when I try to access it, I get EXECUTE permission was denied on the object sys.xp_prop_oledb_provider error.
I read that I need to grant access to the login. So i tried "execute on xp_prop_oledb_provider to login". I got following error indicating that the database user "login" does not exist in master database.
When I tried to add the "login" to Master database with "Public" role, it doesn't let me. It says I do not have the Privileges to do it.
I am logged in as the Master user in AWS RDS "Admin" to do these steps.
Is the Master Database not accessible in AWS RDS? If so, how can I get access to the system procedures in Master database.
Please advise and let me know your experience working with this.
If Linked Server to Oracle is not available in AWS RDS 2017, what other options are available to be able to query Oracle database from SQL Server 2017..?