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
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
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
)
Created external table with above data source selected the data. I am able to retrieve the data: