Linked server from SQL Server to Oracle with MSDAORA
Create a SQL Server linked server to an Oracle database with the MSDAORA OLE DB provider and map a local login to an Oracle user.
SQL Server can query an Oracle database through a linked server that uses Microsoft's MSDAORA OLE DB provider. The Oracle client components must be installed on the SQL Server host before the linked server is created.
Create the linked server
sp_addlinkedserver defines the linked server. The last argument is the Oracle data source (the TNS alias or the net service name).
EXEC sp_addlinkedserver
'OracleLink', 'OracleLink',
'MSDAORA', 'OracleDb'
Map logins
sp_addlinkedsrvlogin maps a local login to a remote Oracle user. With @useself = N'False', the local login is not used for the remote connection and the given remote user and password are used instead.
EXEC sp_addlinkedsrvlogin
'OracleLink', N'False',
'<domain>\<windows_login>', '<oracle_user>',
'<oracle_password>'
EXEC sp_addlinkedsrvlogin
'OracleLink', N'False',
'<domain>\<other_windows_login>', '<oracle_user>',
'<oracle_password>'
Create one mapping per local login that must query Oracle.
Query the linked server
Use the four-part name [linked server]..[schema].[table]:
SELECT *
FROM [OracleLink]..<schema>.CHAINED_ROWS
GO
Notes
- The remote password is stored in the SQL Server linked-server definition. Use a dedicated Oracle account with the rights it needs, and do not write the password in shared scripts.
- The placeholders in angle brackets must be replaced with the real values in a secure way, not stored in the repository.