AWS环境下SQL Server 2016标准版创建Oracle链接服务器求助

First, let’s cover the critical prerequisites that are often the missing piece for successful linked server setup:

• Install the 64-bit Oracle OLE DB Provider: The ORAOLEDB.ORACLE provider isn’t included with SQL Server by default. You’ll need to download and install the 64-bit version matching your Oracle database version on your AWS SQL Server instance.
• Validate Network Connectivity: Ensure your AWS SQL Server can reach the Oracle database over the network. This usually means opening port 1521 (Oracle’s default listener port) in security groups, firewalls, or network ACLs between the two servers.
• Check Service Permissions: The SQL Server service account needs read/execute access to the Oracle provider files installed on the server.

Once those are sorted, here’s your T-SQL properly formatted, with breakdowns of each parameter:

USE [master]
GO

-- Define the linked server connection
EXEC master.dbo.sp_addlinkedserver 
    @server = N'ServerName',          -- Your chosen name for the linked server in SQL Server
    @srvproduct=N'Oracle',           -- Target database product (Oracle)
    @provider=N'ORAOLEDB.ORACLE',    -- OLE DB provider for Oracle
    @datasrc=N'ServerName',          -- Oracle net service name (from tnsnames.ora) or direct string like '//OracleHost:1521/ServiceName'
    @provstr=N''                     -- Optional provider-specific config (can leave blank if using @datasrc)
GO

-- Set up login mapping for authentication
EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'ServerName',       -- Must match the linked server name above
    @useself=N'False',               -- Don't use the current SQL Server login for Oracle authentication
    @locallogin=NULL,                -- Apply this mapping to all local logins (specify a login here to restrict access)
    @rmtuser=N'UserName',            -- Valid Oracle username
    @rmtpassword='UserPassword'      -- Corresponding Oracle password
GO

Quick Tips: #

• Replace ServerName with your desired linked server name and the actual Oracle connection string/net service name. For example, if your Oracle DB is at oracle-prod.example.com:1521/ORCL, set @datasrc=N'//oracle-prod.example.com:1521/ORCL'.
• To restrict access to specific SQL Server logins, change @locallogin=NULL to the local login name (e.g., @locallogin=N'AppSQLUser').
• Test the linked server after setup with a simple query: SELECT * FROM [ServerName]..[OracleSchema].[OracleTableName]; (note the double dots between server name and schema/table).

If you hit errors like "provider not found" or connection timeouts, double-check the provider installation, network access, and Oracle credential validity.

内容的提问来源于stack exchange，提问作者lvaldez