azureexternal-tables

Azure External tables no longer working. after working


I created some Azure Extended tables that queried SqlServer. They seemed to be working fine. Then about 8 hours later stopped working giving me the error. "An error occurred while establishing connection to remote data source: Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired[Microsoft][ODBC Driver 17"

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password1!';

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'adminadmin', SECRET = 'password1!';

CREATE EXTERNAL DATA SOURCE [dbI]
WITH
( TYPE = RDBMS ,
LOCATION = 'sql-1' ,
DATABASE_NAME = 'dbI' ,
CREDENTIAL = SQLServerCredentials
) 

I can't get CONNECTION_OPTIONS to work in the create data source command either. CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',

I can connect to the external tables and script them out. The external tables are in the Tables/External Tables on Azure.

To be able to select top 1000 rows from external table


Solution

  • I created Master Key and Scoped credential with server credentials and created Data source using below code:

    CREATE EXTERNAL DATA SOURCE [dbs]
    WITH
    ( TYPE = RDBMS ,
      LOCATION = 'dbserevere' ,
      DATABASE_NAME = 'db' ,
      CREDENTIAL = SQLServerCredentials
    )
    

    Created External table with created data source:

    CREATE EXTERNAL TABLE dbstudents2(
      Id INT,
      Name VARCHAR(20)
    )
    WITH (
        DATA_SOURCE = dbs,
        SCHEMA_NAME = 'dbo',
        OBJECT_NAME = 'students'
    )
    

    Selected data using select * from dbstudents2 I got same error

    enter image description here

    The issue might be causing because of wrong location here I provided Azure sql server name. I created data source using code:

    CREATE EXTERNAL DATA SOURCE [db]
    WITH
    ( TYPE = RDBMS ,
      LOCATION = 'dbserevere.database.windows.net' ,
      DATABASE_NAME = 'db' ,
      CREDENTIAL = SQLServerCredentials1
    )
    

    enter image description here

    Created external table with above data source selected the data. I am able to retrieve the data:

    enter image description here