When you're using EC2, don't think of it as "a special version of SQL Server" but rather "the same SQL Server I'm used to, running in a special data center." There are no feature restrictions in SQL Server--you're limited only by the infrastructure that you build in the AWS cloud/data center.
You can absolutely create linked servers from on-prem to EC2, and vice-versa. You just have the same challenges as any time you create linked servers across data centers.
You mention that AWS & on-Prem are in different domains. This will require that you either have a trust between the domains, or that you use SQL Server authentication.
You've already said you're solving this with SQL Auth, and the configuration of the linked server & auth is a whole separate question, so I'll not dive into those details here.
Ultimately, your servers need to be able to talk to each other. It sounds like that isn't happening. You mentioned that the error you are seeing is "Login timeout expired." This is a somewhat generic error, but I'm guessing the more verbose version of it is this:
Login timeout expired.
A network-related or instance-specific error
has occurred while establishing a connection to SQL Server. Server is
not found or not accessible. Check if instance name is correct and if
SQL Server is configured to allow remote connections.
This full error indicates that your client (in this case, your client is your on-prem instance that is establishing the connection to the AWS instance) cannot reach the server. Presumably, both SQL Instances are online, healthy, and accepting connections. The issue could be a problem resolving the server name, or it could be that a firewall is blocking the communication, or some other network issue.
You will need to establish a (secure!!!) network route between your on-prem server and your EC2 instance.
From your on-prem server, open a PowerShell prompt, and use the
Test-NetConnection cmdlet to confirm if your server can communicate with the EC2 instance on the SQL Server port.
In this example, I'm testing a connection to the machine
blep.am2.co which is running a SQL Server instance running on the default port 1433:
PS C:\> Test-NetConnection blep.am2.co -port 1433
WARNING: TCP connect to (192.168.86.100 : 1433) failed
ComputerName : blep.am2.co
RemoteAddress : 192.168.86.100
RemotePort : 1433
InterfaceAlias : Wi-Fi
SourceAddress : 192.168.86.226
PingSucceeded : True
PingReplyDetails (RTT) : 20 ms
TcpTestSucceeded : False
The test shows that the name was correctly resolved to an IP, that a ping test was successful (
PingSucceeded: True), but that it was unable to connect on the specified TCP port (
A successful ping indicates that there is a successful route to the server (that's good!); a failed ping could indicate that the server is not accessible at all (it is also possible to disable ping responses, so a failed ping is valuable data, but depending on your network/security configuration may not be conclusive).
The TCP Test failure indicates that the specific connection on port 1433 is failing. The combination of a successful ping, and failure on port 1433 indicates that the most likely issue is a firewall blocking the connection. You would need to check all firewalls along the network path between servers to identify which one is blocking the traffic. At minimum, you would likely need to check:
- Outgoing connections on the Windows firewall for your on-prem server
- Outgoing connections on the firewall for your on-prem data center
- Incoming connections on your AWS network
- Incoming connections on the Windows firewall for your EC2 server
Once you resolve the networking connectivity, and the
Test-NetConnection is successful on your SQL Server port, you will be able to successfully create your linked server.
If you aren't familiar with networking & network security, I would suggest proceeding carefully when making any configuration changes to allow your on-prem & AWS servers to communicate. Misconfiguring the connections could create a vulnerability where either you allow network connections from unauthorized sources, or that you transmit data unencrypted across the internet, where it could be intercepted.