CODEWITHSUNDEEP
sql-serverlinked-server
Leo Demarce
Leo Demarce

Reputation: 43

Unable to link SQL Server Express to SQL Server

I have a SQL Server version 12.0 instance installed on a Windows Server 2012R2 (SERVER/SQL), and a SQL Server Express version 11.0 instance installed on a windows 10 workstation (WIN10/SQLEXPRESS). Both are on the same domain. I am logged in as the domain admin and have full rights on both SQL instances.

On my WIN10 machine I am able to create a linked server from (WIN10/SQLEXPRESS) to (SERVER/SQL).

On my SERVER when I attempt to create a linked server from (SERVER/SQL) to (WIN10/SQLEXPRESS) I am told to ensure that the instance will allow for remote connections. I have gone through about 40 hours of troubleshooting this, ensuring that all firewalls, ports, security and agents and browsers are open.

Is there any reason I cannot link a SQL Server Express to another SQL Server, but can the other way around?

I would like to be able to call stored procedures from my main SQL Server but am not able to.

Please note, the purpose for the SQLEXPRESS on the WIN10 machine is due to the vendor requirements for the software I am using. Otherwise I would have the instance on my SERVER.

Upvotes: 0

Views: 844

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

Almost certianly a network issue.

  1. Using SQL Server Configuration Manager configure SQLEXPRESS to enable TCP/IP and listen on a fixed port, preferably 1433 if no other instance is on the server. Restart the instance.

  2. Create a Windows Firewall rule enable inbound TCP/IP connections to the port.

  3. Test network connectivity to the target port from the other server with powershell, eg:

    PS C:\> test-netconnection WIN10 -Port 1433

  4. Test SQL connectivity with SQLCMD or SSMS on the server to the WIN10 box.

  5. Create the linked server.

Upvotes: 2

Related Questions