Error creating AWS RDS Linked Server to Oracle

  • MadhuriDara

    SSC Rookie

    Points: 32

    Hi,

    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..?

     

     

     

     

     

     

  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeff Moden

    SSC Guru

    Points: 996688

    You're paying a lot of money for AWS RDS.  Unfortunately, it also means that you give up some things because AWS is managing the instance for you.  For example, if you were to shift a database on RDS to the BULK LOGGED recovery model to do index maintenance faster, etc, you'll find that RDS shifts you back to the FULL recovery model because AWS is responsible for your backups, not you.

    There are a lot of things you can't do on RDS simply because they are responsible for your data.

    With that, I suggest that you contact AWS with this problem... you ARE paying for some support, after all.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply