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]