Azure external table using a datasource that is defined with a managed identity

Question

can an external table be create using a system managed identity in Azure SQL database. I need to select data from one database to insert into a table in another database but we are using the Azure active directory so no one is using the sql authentication to sign in.

Answer 1

Please refer to this documentation. The syntax below is possible using Azure Data Explorer cluster.

.create external table tableName (col_a: string, col_b: string)  
kind = storage   
dataformat = csv (  
'https://StorageAccountName.blob.core.windows.net/Container;managed_identity=802bada6-4d21-44b2-9d15-e66b29e4d63e'  
)  

Answer 2

create external table [opeir].[spriden]
( [SPRIDEN_PIDM] decimal NULL,
[SPRIDEN_ID] nvarchar NULL,
[SPRIDEN_LAST_NAME] nvarchar NULL,
[SPRIDEN_FIRST_NAME] nvarchar NULL,
[SPRIDEN_MI] nvarchar NULL,
[SPRIDEN_CHANGE_IND] nvarchar NULL,
[SPRIDEN_ENTITY_IND] nvarchar NULL,
[SPRIDEN_ACTIVITY_DATE] datetime2 NULL,
[SPRIDEN_USER] nvarchar NULL,
[SPRIDEN_ORIGIN] nvarchar NULL,
[SPRIDEN_SEARCH_LAST_NAME] nvarchar NULL,
[SPRIDEN_SEARCH_FIRST_NAME] nvarchar NULL,
[SPRIDEN_SEARCH_MI] nvarchar NULL,
[SPRIDEN_SOUNDEX_LAST_NAME] nvarchar NULL,
[SPRIDEN_SOUNDEX_FIRST_NAME] nvarchar NULL,
[SPRIDEN_NTYP_CODE] nvarchar NULL,
[SPRIDEN_CREATE_USER] nvarchar NULL,
[SPRIDEN_CREATE_DATE] datetime2 NULL,
[SPRIDEN_DATA_ORIGIN] nvarchar NULL,
[SPRIDEN_CREATE_FDMN_CODE] nvarchar NULL,
[SPRIDEN_SURNAME_PREFIX] nvarchar NULL,
[SPRIDEN_SURROGATE_ID] decimal NULL,
[SPRIDEN_VERSION] decimal NULL,
[SPRIDEN_USER_ID] nvarchar NULL,
[SPRIDEN_VPDI_CODE] nvarchar NULL
)
kind = storage
dataformat = csv (
'https://sqldb-edw-dev-usscent-001.database.windows.net/Container;managed_identity=###'
)

I get the error:
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near 'kind'.

Answer 3

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql

Only SQL Auth is supported for elastic queries