Linked servers SQL to Oracle

Linked server configuration on SQL Server
We need go for server object + Linked server +New Linked server

Here we are configuring the linked server between SQL to Oracle. Based on requirement we need to choose the Provider -oracle provider for ole db.
If oracle provider for ole db provider not there in provider list we need to install on the server. post installation we need to reboot the server.

Product name : servername.***.***: Port no
Data source : //servername .####.org:portno/SID

We need to go for security chose below option if you have oracle user name & password .

Post linked server configuration we need to check the test connection

Granting access to user
USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO [login]

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

Advertisements

#sql-dba