%3CLINGO-SUB%20id%3D%22lingo-sub-1946095%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23151%3A%20External%20Tables%20and%20Private%20Link%20connectivity%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1946095%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20got%20a%20new%20service%20request%20that%20our%20customer%20implemented%20a%20private%20link%20and%20they%20want%20to%20use%20External%20tables.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%2C%20when%20they%20enabled%20the%20option%20%22Deny%20public%20network%20access%22%20they%20are%20not%20able%20to%20connect%3A%26nbsp%3B%3CSTRONG%3EConnection%20was%20denied%20since%20Deny%20Public%20Network%20Access%20is%20set%20to%20Yes%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOur%20customer%20implemented%20the%20following%20steps%3A%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ECreate%20Master%20KEY%3C%2FLI%3E%0A%3CLI%3ECreate%20Database%20Scoped%20Credential%3C%2FLI%3E%0A%3CLI%3ECreate%20External%20Data%20Source%20with%20this%20parameters%20%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%26nbsp%3B%26nbsp%3B%20LOCATION%3D'servername.database.windows.net'%2C%3C%2FLI%3E%0A%3CLI%3E%26nbsp%3B%26nbsp%3B%20DATABASE_NAME%20%3D%20'databasename'%2C%3C%2FLI%3E%0A%3CLI%3E%26nbsp%3B%26nbsp%3B%20CREDENTIAL%20%3D%20AppCredential%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CUL%3E%0A%3CLI%3ECreate%20External%20database%20from%20the%20data%20source%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20we%20tried%20to%20perform%20a%20select%20from%20the%20first%20database%20they%20faced%20the%20following%20message%3A%22Error%20retrieving%20data%20from%20servername.database.windows.net%26nbsp%3B%20The%20underlying%20error%20message%20received%20was%3A%20'A%20network-related%20or%20instance-specific%20error%20occurred%20while%20establishing%20a%20connection%20to%20SQL%20Server.%20The%20server%20was%20not%20found%20or%20was%20not%20accessible.%20Verify%20that%20the%20instance%20name%20is%20correct%20and%20that%20SQL%20Server%20is%20configured%20to%20allow%20remote%20connections.%20(provider%3A%20Named%20Pipes%20Provider%2C%20error%3A%2040%20-%20Could%20not%20open%20a%20connection%20to%20SQL%20Server)%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20need%20to%20explain%20that%20the%20connection%20will%20be%20made%20using%20the%20external%20IP%20of%20the%20node%20that%20the%20server%20is%20running%20and%20not%20the%20private%20IP.%20For%20this%20reason%2C%20you%20need%20to%20use%20the%20external%20connection%20or%20use%20Managed%20Instance%20that%20is%20possible%20to%20connect%20among%20databases%20that%20are%20running%20in%20the%20same%20instance.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1946095%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20got%20a%20new%20service%20request%20that%20our%20customer%20implemented%20a%20private%20link%20and%20they%20want%20to%20use%20External%20tables.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%2C%20when%20they%20enabled%20the%20option%20%22%3CSPAN%3EDeny%20public%20network%20access%22%20they%20are%20not%20able%20to%20connect.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E

Today, we got a new service request that our customer implemented a private link and they want to use External tables. 

Unfortunately, when they enabled the option "Deny public network access" they are not able to connect: Connection was denied since Deny Public Network Access is set to Yes

 

Our customer implemented the following steps: 

  • Create Master KEY
  • Create Database Scoped Credential
  • Create External Data Source with this parameters :
    •    LOCATION='servername.database.windows.net',
    •    DATABASE_NAME = 'databasename',
    •    CREDENTIAL = AppCredential
  • Create External database from the data source

 

When we tried to perform a select from the first database they faced the following message:"Error retrieving data from servername.database.windows.net  The underlying error message received was: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)".

 

We need to explain that the connection will be made using the external IP of the node that the server is running and not the private IP. For this reason, you need to use the external connection or use Managed Instance that is possible to connect among databases that are running in the same instance. 

 

Enjoy!