SQL Server Linked Servers

Master SQL Server Linked Server configuration for Nodinite distributed environments. This comprehensive guide covers linked server setup, RPC configuration, Always On Availability Groups (AOAG), failover clusters, security contexts, and troubleshooting common connectivity issues.

What you'll learn on this page:

✅ When linked servers are required for Nodinite deployments
✅ Configure linked servers for default instances, named instances, clusters, and AOAG
✅ Enable RPC and RPC Out for distributed transactions
✅ Set up security contexts for Windows authentication
✅ Troubleshoot common linked server connectivity and authentication issues

Overview

SQL Server Linked Servers enable Nodinite to query databases across multiple SQL Server instances. The Configuration Database uses linked servers to access Log Databases, BizTalk tracking databases, and other remote SQL resources. Even when databases reside on the same SQL Server instance, linked servers must be configured for Nodinite to function correctly.

Which Nodinite Services Use Linked Servers?

Only specific Nodinite services access databases through linked servers:

• ✅ Logging Service — Fetches log data from Log Databases and BizTalk tracking databases
• ✅ Monitoring Service — Retrieves monitoring data from Log Databases
• ✅ Log API — Accesses log data through linked servers
• ✅ Web API — Queries databases through linked servers

Note

Service Account Implications: The service accounts running these four services must have appropriate SQL Server permissions on all linked instances. See Step 3: Set Up Security and User Rights below for detailed requirements.

What is a Linked Server?

A linked server is a SQL Server object that allows you to execute queries against remote SQL Server instances (or other OLE DB data sources) as if they were local. Nodinite relies on linked servers to maintain a consistent architecture regardless of database distribution.

Architecture Overview:

Nodinite Configuration Database connects to Log Databases and BizTalk databases via Linked Servers

When are Linked Servers required?

• ✅ Always — Even when Configuration Database and Log Databases are on the same SQL Server instance
• ✅ Configuration Database accessing Log Databases on remote SQL Server instances
• ✅ Configuration Database accessing BizTalk databases (BizTalkMGMTDb, BizTalkDTADb) for logging agents
• ✅ Configuration Database accessing Dynamics 365 databases for monitoring
• ✅ Any scenario where Nodinite Core Services query databases across SQL instances

When are Linked Servers NOT required?

• ❌ Never — Linked servers are always required for Nodinite to function, regardless of database distribution

Important

Linked Servers are always required: Even when all databases are on the same SQL Server instance, Nodinite requires linked server objects to maintain a consistent data access architecture. This design ensures Nodinite can seamlessly scale from single-instance to distributed environments without code changes.

Configuration Requirements

Core Linked Server Settings

All linked server configurations for Nodinite must have:

1. Server name — Exact name of the SQL Server instance (see Naming Conventions below)
2. Security context — "Be made using the login's current security context" (passthrough authentication)
3. RPC enabled — rpc = true
4. RPC Out enabled — rpc out = true

Important

RPC and RPC Out are critical: These settings enable Nodinite to execute remote stored procedures across linked servers. Without them, distributed transactions and Core Services operations will fail. See About Linked Server RPC and RPC OUT option for details.

Security Context: Passthrough Authentication

Nodinite uses passthrough authentication where the Logging Service, Monitoring Service, and other Core Services maintain the identity of their service account when traversing linked servers.

Configuration: "Be made using the login's current security context"

Benefits:

• ✅ Consistent security model across all linked servers
• ✅ Windows Integrated Security with Kerberos authentication
• ✅ No need to store SQL credentials in linked server definitions
• ✅ Centralized service account management

Requirements:

• ✅ Service accounts must have SQL Server login rights on all linked instances
• ✅ Trusted for delegation configured for multi-hop authentication
• ✅ Service Principal Names (SPN) registered for Kerberos
• ✅ Firewall ports open (see SQL Server Firewall Configuration)

Note

SQL Server mixed mode alternative: Using SQL Server in mixed mode allows you to impersonate a Windows account with a SQL account, bypassing Kerberos-related problems. We urge you to only use this solution as a temporary workaround until you have resolved your Kerberos-related issues. Windows authentication with Kerberos is the recommended production configuration.

Creating Linked Servers

SQL Script Template

Use the following template to create a linked server (replace %SERVER\INSTANCE% with your actual server name):

-- Add the linked server
EXEC sp_addlinkedserver @server = '%SERVER\INSTANCE%';

-- Enable RPC
EXEC sp_serveroption '%SERVER\INSTANCE%', 'rpc', true;

-- Enable RPC Out
EXEC sp_serveroption '%SERVER\INSTANCE%', 'rpc out', true;

Examples

Default instance (localhost):

EXEC sp_addlinkedserver @server = 'LOCALHOST';
EXEC sp_serveroption 'LOCALHOST', 'rpc', true;
EXEC sp_serveroption 'LOCALHOST', 'rpc out', true;

Named instance (localhost):

EXEC sp_addlinkedserver @server = 'LOCALHOST\BIZTALK';
EXEC sp_serveroption 'LOCALHOST\BIZTALK', 'rpc', true;
EXEC sp_serveroption 'LOCALHOST\BIZTALK', 'rpc out', true;

Remote server (default instance):

EXEC sp_addlinkedserver @server = 'SQL-PROD-01';
EXEC sp_serveroption 'SQL-PROD-01', 'rpc', true;
EXEC sp_serveroption 'SQL-PROD-01', 'rpc out', true;

Remote server (named instance):

EXEC sp_addlinkedserver @server = 'SQL-PROD-01\BIZTALK';
EXEC sp_serveroption 'SQL-PROD-01\BIZTALK', 'rpc', true;
EXEC sp_serveroption 'SQL-PROD-01\BIZTALK', 'rpc out', true;

Tip

Verify linked server creation: After creating a linked server in SQL Server Management Studio (SSMS), you should be able to expand the linked server node and see the list of databases available on the remote instance.

Naming Conventions

The linked server name must match the SQL Server instance name format. Use the following conventions based on your environment:

Default Instance

Named Instance

Important

Use listener names for AOAG: Always On Availability Groups require using the listener name, not individual node names, to ensure seamless failover.

Always On Availability Groups (AOAG)

If your SQL Server environment is configured with Always On Availability Groups, additional configuration is required for linked servers.

Listener Names

Always use the AOAG listener name when creating linked servers for availability group databases. This ensures connections automatically fail over to the active replica without manual intervention.

Example:

-- Correct: Use listener name
EXEC sp_addlinkedserver @server = 'SQL-AOAG-Listener';

-- Incorrect: Don't use individual node names
-- EXEC sp_addlinkedserver @server = 'SQL-Node-01';

SQL Accounts Are Not Replicated

Warning

SQL logins are not replicated between AOAG nodes. You must manually create SQL Server login accounts on each node in the availability group. Windows authentication with domain accounts is strongly recommended to avoid this issue.

SPN Configuration for AOAG

Proper Service Principal Names (SPN) registration is critical for Always On Availability Groups. You must register SPNs for:

• ✅ AOAG listener name (with and without port)
• ✅ Each physical node name (with instance port)
• ✅ Each instance name on each node

Tip

AOAG SPN Guide: See the Service Principal Names (SPN) page for AOAG-specific SPN requirements including listener names, node names, and port configurations. The guide includes complete registration examples for two-node and multi-node AOAG clusters.

DNS Suffix Considerations

Important

If you are using a DNS suffix in your environment (e.g., server.contoso.com), you may need to create duplicate linked servers with both short names and fully qualified domain names (FQDNs):

-- Short name
EXEC sp_addlinkedserver @server = 'SQL-AOAG-Listener';

-- FQDN with DNS suffix
EXEC sp_addlinkedserver @server = 'SQL-AOAG-Listener.contoso.com';

Failover Clusters

If your SQL Server environment is configured with failover clustering, you need to review the following additional requirements:

Cluster Name vs. Node Names

Use the cluster virtual network name when creating linked servers, not individual node names. This ensures connections work regardless of which node currently owns the SQL Server resource.

Example:

-- Correct: Use cluster name
EXEC sp_addlinkedserver @server = 'SQL-Cluster';

-- Incorrect: Don't use individual node names
-- EXEC sp_addlinkedserver @server = 'SQL-Node-01';

MSDTC Configuration

Failover clusters require special Microsoft Distributed Transaction Coordinator (MSDTC) configuration to support distributed transactions across cluster nodes.

Important

You must configure MSDTC as a clustered resource. See the MSDTC guide for detailed cluster configuration steps.

Linked Servers on Each Node

Important

If you have remote databases (BizTalk BizTalkDTADb, BizTalk BizTalkMGMTDb, and Nodinite Log Databases), you will need to add a linked server to every remote SQL instance on each node of the failover cluster.

Required Linked Servers for Nodinite

Before installing Nodinite, a SQL DBA must create the following linked servers on the Configuration Database SQL instance:

Core Services (Always Required)

1. Configuration Database → Local Log Databases — Even when on same instance
2. Configuration Database → Remote Log Databases — If Log Databases on different SQL instance(s)

BizTalk Logging Agents

If using Nodinite to log BizTalk Server events:

3. Configuration Database → BizTalk Management Database (BizTalkMGMTDb)
4. Configuration Database → BizTalk Tracking Database (BizTalkDTADb)

Note

If BizTalkMGMTDb and BizTalkDTADb are on different SQL Server instances, create separate linked servers for each.

Dynamics 365 Monitoring

If using Nodinite to monitor Dynamics 365:

5. Configuration Database → Dynamics 365 Database(s)

Custom Integrations

If using Nodinite to monitor custom databases:

6. Configuration Database → Custom Application Databases (as needed)

Troubleshooting

Common Issues

Testing Linked Server Connectivity

Test a linked server query:

-- Test basic connectivity
SELECT @@SERVERNAME AS LinkedServerName;

-- Test through linked server (replace LINKEDSERVERNAME)
SELECT * FROM [LINKEDSERVERNAME].[master].[sys].[databases];

-- Test RPC is enabled
EXEC sp_helpserver;

Expected output for sp_helpserver:

The rpc and rpc out columns should show true for the linked server.

Validation Checklist

After creating linked servers, verify:

• ✅ Linked server appears in SSMS under Server Objects → Linked Servers
• ✅ Can expand linked server to see remote database list
• ✅ sp_helpserver shows rpc and rpc out = true
• ✅ Test query returns results without errors
• ✅ Service account has login rights on remote SQL instance
• ✅ Trusted for delegation configured (for distributed environments)
• ✅ SPNs registered (for distributed environments)
• ✅ Firewall ports open between SQL instances

Data Flow Architecture

Understanding how Nodinite uses linked servers helps troubleshoot connectivity issues:

Configuration Database accessing Log Databases via Linked Servers

Nodinite Core Services accessing Configuration Database, which uses Linked Servers to query Log Databases

Related Guides

Prerequisites and Configuration:

• Configuration Database Prerequisites
• Log Database Prerequisites
• SQL Server Connection Strings
• SQL Server Firewall Configuration

Security and Authentication:

• Service Principal Names (SPN) — Kerberos authentication for linked servers
• Trusted for delegation — Multi-hop authentication configuration
• MSDTC — Distributed transaction coordinator

Advanced Scenarios:

• Always On Availability Groups — AOAG-specific configuration
• Linked Server RPC and RPC OUT option — Deep dive into RPC settings

Next Steps

After configuring linked servers:

1. ✅ Verify SPNs are registered for all SQL instances
2. ✅ Configure Trusted for delegation for service accounts
3. ✅ Test linked server connectivity with sample queries
4. ✅ Proceed with Nodinite installation