LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon