Local External Data Source into Local DB Environment-CodePudding

I have been trying to create an External Data Source locally in SSMS v18.11.1 using SQL Server 2019.

I have PolyBase enabled the master key and credential have also been created.

Azure code (works):

CREATE EXTERNAL DATA SOURCE [MasterDataSource]
WITH (
      TYPE = RDBMS,
      LOCATION = N'{location}.database.windows.net',
      DATABASE_NAME = N'{DatabaseName}',
      CREDENTIAL = [MasterDbCred]
     );

Local (not working):

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
      LOCATION = 'sqlserver://{HostName}:1433' ,
      CREDENTIAL = MasterDbCred
     )

I am using SELECT HOST_NAME() to get the hostname.

When running the local script above no matter what LOCATION used, I get:

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Login timeout expired".

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "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. For more information see SQL Server Books Online.".

Which would suggest it's not connecting to local.

Other external data sources work, for example, I have a BLOB_STORAGE working locally, but this is to an external location.

CREATE EXTERNAL DATA SOURCE [external_data]
WITH (
      TYPE = BLOB_STORAGE,
      LOCATION = N'https://storage.blob.core.windows.net/datacontainer'
     );

Any help would be appreciated, thanks.

CodePudding user response：

Looks like normal connectivity troubleshooting.

Verify using SQL Server Configuration Manager that the target instance has TCP/IP enabled, and you can connect to its listening port with test-netconnection.

Remember SQL Server Developer and Express Editions have TCP/IP disabled by default.