Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
Yes. SQL Server can query an Oracle database through a linked server, usually with Oracle’s OraOLEDB.Oracle provider. Install the provider and Oracle Net components on the SQL Server host, create an explicit Oracle login mapping, test with OPENQUERY, and use linked servers only where their latency, security, and transaction limits fit the workload.
Linked servers are available in the SQL Server Database Engine and Azure SQL Managed Instance (with limitations), but not in Azure SQL Database. See Microsoft’s linked-server documentation.
What a linked server does
A linked server is a SQL Server object that stores an OLE DB provider, remote data-source information, and security mappings. SQL Server delegates remote work to the Oracle provider; it does not convert Oracle into a SQL Server database. Oracle SQL syntax, data types, optimizer behavior, and transaction rules still apply. Provider capabilities vary, so a configuration that can read a table may not support every update or procedure call.
Before you begin
- SQL Server Database Engine on Windows, or Azure SQL Managed Instance.
- Oracle Database service name or TNS alias, such as
ORCLorPRODDB. OraOLEDB.Oracleand compatible Oracle Client/Instant Client components installed on the SQL Server computer.- Network access from that computer to the Oracle listener.
- A dedicated Oracle account with only the required object privileges.
- SQL Server permission to create linked servers:
ALTER ANY LINKED SERVERorsetupadminfor T-SQL; the SSMS workflow generally requiresCONTROL SERVERorsysadmin.
Installing Oracle software only on the workstation running SSMS is insufficient. The SQL Server service must be able to load the provider, and its service account needs read and execute access to the provider directory and subdirectories. Oracle documents the usual connection form as Provider=OraOLEDB.Oracle;User ID=user;Password=pwd;Data Source=constr;; for a remote database, Data Source must resolve to the correct Oracle Net service name. See Oracle’s OraOLEDB documentation.
#1 Best Overall
Install and validate Oracle connectivity
- Install a supported Oracle client/provider on the SQL Server host, matching the SQL Server process architecture.
- In SSMS, check Server Objects > Providers for
OraOLEDB.Oracle. - From the SQL Server host, validate the TNS alias, listener route, and Oracle credentials using the Oracle client tools available in your environment.
- Confirm the SQL Server service account sees the same Oracle home,
TNS_ADMIN, andtnsnames.oraas your interactive test account.
Oracle’s linked-server example uses the provider’s Allow inprocess option for some configurations. Treat that as a targeted provider-loading compatibility setting, not a universal fix: enabling it loads the provider inside the SQL Server process and should be tested on a non-production instance first.
Configure the linked server in SSMS
Open Object Explorer > Server Objects > Linked Servers, right-click Linked Servers, and select New Linked Server. On General, select Other data source and use values similar to:
| Field | Example |
|---|---|
| Linked server | ORACLE_PROD |
| Provider | Oracle Provider for OLE DB / OraOLEDB.Oracle |
| Product name | Oracle |
| Data source | ORCL |
| Provider string | Usually blank unless your Oracle configuration requires one |
| Catalog | Optional; provider-dependent |
On Security, map only the SQL Server logins that need access to a dedicated Oracle account. Turn off impersonation for a named username/password mapping. Do not assume an administrator’s successful test proves that an application login or SQL Agent job will work.
On Server Options, leave Data Access enabled. Enable RPC Out only for remote procedure calls. Leave Collation Compatible false unless you can prove the claim. Enable transaction promotion only when distributed transactions are required and tested. Do not turn on every option as a troubleshooting shortcut. Microsoft’s SSMS procedure explains these fields.
Rank #2
Configure it with T-SQL
USE master;
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'ORACLE_PROD',
@srvproduct = N'Oracle',
@provider = N'OraOLEDB.Oracle',
@datasrc = N'ORCL';
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'ORACLE_PROD',
@useself = N'False',
@locallogin = N'ReportingLogin',
@rmtuser = N'ORACLE_REPORT',
@rmtpassword = N'<secret>';
GOUse @locallogin = NULL only when every local login should receive the mapping. SQL Server can create a broad default self-mapping; inspect and remove mappings that are not intended. Microsoft documents sp_addlinkedserver and its security behavior. Never place real passwords in source control, job text, or shared scripts.
Inspect or remove the object with:
SELECT name, product, provider, data_source, catalog,
is_remote_login_enabled, is_rpc_out_enabled
FROM sys.servers
WHERE name = N'ORACLE_PROD';
EXEC master.dbo.sp_dropserver
@server = N'ORACLE_PROD',
@droplogins = N'droplogins';Test the connection
EXEC master.dbo.sp_testlinkedserver
@servername = N'ORACLE_PROD';
SELECT *
FROM OPENQUERY(
ORACLE_PROD,
'SELECT SYSDATE AS current_time FROM dual'
);The second test is useful because SYSDATE and DUAL are Oracle SQL constructs. Oracle shows the same general OPENQUERY validation pattern.
Query Oracle tables
A four-part name is written as <linked_server>.<catalog>.<schema>.<object>, but Oracle providers expose catalog metadata differently. Try the form reported by your installation:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsSELECT TOP (100) *
FROM ORACLE_PROD..HR.EMPLOYEES;
-- Some providers expose a catalog/service name:
SELECT *
FROM ORACLE_PROD.ORCL.HR.EMPLOYEES;For Oracle-native SQL and controlled remote filtering, use OPENQUERY:
SELECT employee_id, last_name
FROM OPENQUERY(
ORACLE_PROD,
'SELECT employee_id, last_name
FROM hr.employees
WHERE department_id = 10'
);OPENQUERY makes the remote statement explicit and supports Oracle syntax, but it is not guaranteed to be faster. Measure Oracle and SQL Server plans, elapsed time, rows returned, and network traffic. Four-part-name joins can move large rowsets:
SELECT s.CustomerID, s.CustomerName, o.CREDIT_LIMIT
FROM dbo.Customers AS s
JOIN ORACLE_PROD..AR.CUSTOMERS AS o
ON o.CUSTOMER_NUMBER = s.CustomerID;For a large join, filter and project on Oracle first, select explicit columns, and avoid SELECT *.
Data types and writes
Heterogeneous metadata is a common source of surprises. Oracle NUMBER precision and scale, DATE time-of-day, timestamps with time zones, CLOB/BLOB, LONG, quoted identifiers, collation, and Oracle’s empty-string-as-NULL semantics do not map perfectly to SQL Server. Cast difficult columns in the Oracle query:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
SELECT *
FROM OPENQUERY(
ORACLE_PROD,
'SELECT CAST(order_id AS NUMBER(18,0)) AS order_id,
CAST(order_date AS TIMESTAMP) AS order_date,
CAST(status AS VARCHAR2(30)) AS status
FROM ar.orders'
);Remote INSERT, UPDATE, DELETE, and procedure calls depend on provider support, keys, views, triggers, privileges, and transaction behavior. Test each operation against representative objects; do not assume that because a table is readable it is writable.
Security and delegation
- Create a separate Oracle account for each workload and grant only required
SELECT, DML, orEXECUTEprivileges. - Restrict mappings to named SQL Server logins or groups.
- Protect credentials and use Oracle encryption features where supported.
- Restrict the SQL Server host’s network access to the Oracle listener and audit both systems.
- Escape or parameterize values carefully when constructing dynamic
OPENQUERYtext.
Windows pass-through authentication is not automatic. It can require Kerberos delegation, SPNs, and constrained-delegation configuration. An explicit Oracle credential is usually simpler to troubleshoot, subject to your organization’s identity and secret-management requirements.
Transactions
A normal read does not automatically make SQL Server and Oracle one atomic transaction. Cross-system transaction promotion can involve MS DTC, firewall rules, provider enlistment, Oracle configuration, and the linked-server promotion setting. Oracle documents a DistribTX provider attribute; Microsoft documents transaction-promotion options. Avoid distributed transactions for ordinary reporting. If atomic cross-database writes are essential, test commit, rollback, disconnect, and failure behavior with the exact product versions. An “unable to enlist in the transaction” error is a transaction-support problem, not an ordinary login failure.
Troubleshooting by symptom
Provider missing from SSMS
Install the provider on the SQL Server host, verify architecture and registration, confirm service-account directory permissions, restart SQL Server if installation occurred after startup, and recheck Server Objects > Providers.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →“Cannot initialize the data source object”
Check the provider name, Oracle home, 32/64-bit compatibility, TNS_ADMIN, tnsnames.ora, alias, listener reachability, credentials, service-account environment, and (only if appropriate) Allow inprocess.
Best Value
- Used Book in Good Condition
Alias works interactively but not from SQL Server
The interactive user and SQL Server service account may use different Oracle homes or configuration files. Check the SQL Server service identity, environment, file permissions, and duplicate Oracle installations.
Login or mapping failure
EXEC master.dbo.sp_helplinkedsrvlogin
@rmtsrvname = N'ORACLE_PROD';Verify the intended local mapping, that @useself is not unexpectedly in use, and that the Oracle account is unlocked, unexpired, and independently able to connect.
Four-part name fails but OPENQUERY works
Suspect catalog/schema metadata, quoted identifiers, unsupported types, or SQL Server’s distributed-query translation. Use explicit Oracle SQL and casts, or resolve the metadata issue before requiring four-part names.
Timeouts or poor performance
Reduce columns and rows at Oracle, check Oracle indexes and execution plans, measure network latency, and avoid large cross-server joins. Compare both query forms rather than assuming either is faster.
When a linked server is the wrong tool
Linked servers fit small or moderate, near-real-time reads and tightly controlled writes. They are a poor choice for large recurring extracts, complex transformations, checkpointed retries, lineage, independent availability, or systems where distributed transactions are central.
Quick Recap
- SSIS: scheduled extraction, transformation, and local staging.
- Azure Data Factory: managed pipelines, incremental loads, retries, and monitoring; see official pricing for region-specific costs.
- Oracle GoldenGate: low-latency change-data capture and replication, not ad hoc querying; see Oracle’s product page.
- Staged or materialized copies: predictable reporting performance at the cost of freshness and storage.
- Application integration: best when validation, business rules, retries, and API boundaries matter more than SQL convenience.
Production checklist
- Provider installed and visible on the SQL Server host.
- Oracle Net alias resolves under the SQL Server service account.
- Dedicated, least-privileged Oracle account created.
- Explicit local-login mapping reviewed.
sp_testlinkedserverand an OracleOPENQUERYtest succeed.- Required four-part names, types, NULL behavior, and writes tested.
- Oracle and SQL Server plans, row counts, latency, and network volume reviewed.
- Distributed-transaction requirement explicitly decided.
- Secrets, monitoring, change control, and rollback documented.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
