Synapse linked service creation does not work for some linked service types

[nbarichev]

Describe the bug

Using az synapse linked-service create I'm not able to create Synapse linked service of type AzureSqlDW. Though the json code of linked service is the same as the one I see in Synapse Web UI for already existing linked service created via click-ops.

Command used

az synapse linked-service create --subscription xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx --workspace-name "SYNAPSE_NAME" --name "test" --file "@ls_definition.json" --debug

ls_definition.json

{
	"name": "Test",
	"properties": {
		"annotations": [],
		"type": "AzureSqlDW",
		"typeProperties": {
			"server": "test.sql.azuresynapse.net",
			"database": "test",
			"encrypt": "mandatory",
			"trustServerCertificate": false,
			"authenticationType": "SystemAssignedManagedIdentity"
		},
		"connectVia": {
			"referenceName": "ManagedNetworkIR",
			"type": "IntegrationRuntimeReference"
		}
	}
}

Related command

az synapse linked-service create --subscription xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx --workspace-name WORKSPACE_NAME --name LINKED_SERVICE_NAME --file "@file"

Errors

{
  "error": {
    "code": "BadRequest",
    "message": "Invalid payload {\r\n  \"Id\": \"/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/resourceGroups/RESOURCE_GROUP/providers/Microsoft.DataFactory/factories/SYNAPSE_NAME/linkedservices/test\",\r\n  \"Name\": \"test\",\r\n  \"Properties\": {\r\n    \"type\": \"AzureSqlDW\",\r\n    \"connectVia\": {\r\n      \"type\": \"IntegrationRuntimeReference\",\r\n      \"referenceName\": \"ManagedNetworkIR\"\r\n    },\r\n    \"annotations\": []\r\n  }\r\n} and error is Invalid linked service payload, the 'typeProperties' nested in payload is null.."
  },
  "etag": null,
  "id": null,
  "name": null,
  "properties": null,
  "status": "Failed",
  "type": null
}

Issue script & Debug output

cli.knack.cli: Command arguments: ['synapse', 'linked-service', 'create', '--subscription', 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx', '--workspace-name', 'WORKSPACE_NAME', '--name', 'test', '--file', '@FILE', '--debug']
cli.knack.cli: __init__ debug log:
Enable color in terminal.
cli.knack.cli: Event: Cli.PreExecute []
cli.knack.cli: Event: CommandParser.OnGlobalArgumentsCreate [<function CLILogging.on_global_arguments at 0x10107b600>, <function OutputProducer.on_global_arguments at 0x1013d4ae0>, <function CLIQuery.on_global_arguments at 0x1013f65c0>]
cli.azure.cli.core.util: attempting to read file SynapseDedicatedSQLPool.json as utf-8-sig
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableCreate []
cli.azure.cli.core: Command index version or cloud profile is invalid or doesn't match the current command.
cli.azure.cli.core: Command index has been invalidated.
cli.azure.cli.core: No module found from index for '['synapse', 'linked-service', 'create', '--subscription', 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx', '--workspace-name', 'WORKSPACE_NAME', '--name', 'test', '--file', '{\n\t"name": "test",\n\t"properties": {\n\t\t"annotations": [],\n\t\t"type": "AzureSqlDW",\n\t\t"typeProperties": {\n\t\t\t"server": "SERVER",\n\t\t\t"database": "DATABASE",\n\t\t\t"encrypt": "mandatory",\n\t\t\t"trustServerCertificate": false,\n\t\t\t"authenticationType": "SystemAssignedManagedIdentity"\n\t\t},\n\t\t"connectVia": {\n\t\t\t"referenceName": "ManagedNetworkIR",\n\t\t\t"type": "IntegrationRuntimeReference"\n\t\t}\n\t}\n}', '--debug']'
cli.azure.cli.core: Loading all modules and extensions
cli.azure.cli.core: Discovered command modules: ['acr', 'acs', 'advisor', 'ams', 'apim', 'appconfig', 'appservice', 'aro', 'backup', 'batch', 'batchai', 'billing', 'botservice', 'cdn', 'cloud', 'cognitiveservices', 'compute_recommender', 'config', 'configure', 'consumption', 'container', 'containerapp', 'cosmosdb', 'databoxedge', 'dla', 'dls', 'dms', 'eventgrid', 'eventhubs', 'extension', 'feedback', 'find', 'hdinsight', 'identity', 'interactive', 'iot', 'keyvault', 'kusto', 'lab', 'managedservices', 'maps', 'marketplaceordering', 'monitor', 'mysql', 'netappfiles', 'network', 'policyinsights', 'privatedns', 'profile', 'rdbms', 'redis', 'relay', 'resource', 'role', 'search', 'security', 'servicebus', 'serviceconnector', 'servicefabric', 'signalr', 'sql', 'sqlvm', 'storage', 'synapse', 'util', 'vm']
cli.azure.cli.core: Loading command modules:
cli.azure.cli.core: Name                  Load Time    Groups  Commands
cli.azure.cli.core: acr                       0.103        36       149
cli.azure.cli.core: acs                       0.035        14        76
cli.azure.cli.core: advisor                   0.002         3         6
cli.azure.cli.core: ams                       0.005        22       100
cli.azure.cli.core: apim                      0.007        14        69
cli.azure.cli.core: appconfig                 0.005         9        47
cli.azure.cli.core: appservice                0.136        79       270
cli.azure.cli.core: aro                       0.039         1        10
cli.azure.cli.core: backup                    0.006        16        60
cli.azure.cli.core: batch                     0.082        34       102
cli.azure.cli.core: batchai                   0.007        10        30
cli.azure.cli.core: billing                   0.022        20        53
cli.azure.cli.core: botservice                0.004        12        42
cli.azure.cli.core: cdn                       0.314         8        50
cli.azure.cli.core: cloud                     0.003         1         7
cli.azure.cli.core: cognitiveservices         0.003        10        33
cli.azure.cli.core: compute_recommender       0.005         1         1
cli.azure.cli.core: config                    0.002         2         7
cli.azure.cli.core: configure                 0.001         2         5
cli.azure.cli.core: consumption               0.036         8         9
cli.azure.cli.core: container                 0.043         1        11
cli.azure.cli.core: containerapp              0.168        37       123
cli.azure.cli.core: cosmosdb                  0.018        58       199
cli.azure.cli.core: databoxedge               0.010         5        28
cli.azure.cli.core: dla                       0.008        23        62
cli.azure.cli.core: dls                       0.004         7        41
cli.azure.cli.core: dms                       0.003         3        22
cli.azure.cli.core: eventgrid                 0.006        25        96
cli.azure.cli.core: eventhubs                 0.027        13        19
cli.azure.cli.core: extension                 0.002         1         7
cli.azure.cli.core: feedback                  0.001         1         2
cli.azure.cli.core: find                      0.002         1         1
cli.azure.cli.core: hdinsight                 0.014         9        42
cli.azure.cli.core: identity                  0.002         2        11
cli.azure.cli.core: interactive               0.001         1         1
cli.azure.cli.core: iot                       0.894        19        82
cli.azure.cli.core: keyvault                  0.015        20       113
cli.azure.cli.core: kusto                     0.003         3        14
cli.azure.cli.core: lab                       0.007        11        34
cli.azure.cli.core: managedservices           0.002         3         8
cli.azure.cli.core: maps                      0.002         5        13
cli.azure.cli.core: marketplaceordering       0.004         1         2
cli.azure.cli.core: monitor                   2.369        17        61
cli.azure.cli.core: mysql                     0.291        16        56
cli.azure.cli.core: netappfiles               0.115         8        17
cli.azure.cli.core: network                   0.633       103       338
cli.azure.cli.core: policyinsights            0.027         9        17
cli.azure.cli.core: privatedns                0.041        14        60
cli.azure.cli.core: profile                   0.003         2         8
cli.azure.cli.core: rdbms                     0.085        49       202
cli.azure.cli.core: redis                     0.004         7        38
cli.azure.cli.core: relay                     0.048         7         8
cli.azure.cli.core: resource                  0.018        51       231
cli.azure.cli.core: role                      0.003        17        61
cli.azure.cli.core: search                    0.016         7        19
cli.azure.cli.core: security                  0.017        48        98
cli.azure.cli.core: servicebus                0.021        12        14
cli.azure.cli.core: serviceconnector          0.049        20       309
cli.azure.cli.core: servicefabric             0.061        27        80
cli.azure.cli.core: signalr                   0.005         9        34
cli.azure.cli.core: sql                       0.023        56       215
cli.azure.cli.core: sqlvm                     0.106         4        20
cli.azure.cli.core: storage                   0.050        59       273
cli.azure.cli.core: synapse                   0.014        54       246
cli.azure.cli.core: util                      0.002         3         7
cli.azure.cli.core: vm                        0.173        58       233
cli.azure.cli.core: Total (66)                6.227      1208      4702
cli.azure.cli.core: Loading extensions:
cli.azure.cli.core: Name                  Load Time    Groups  Commands  Directory
cli.azure.cli.core: account                   0.003         4        13  /Users/user/.azure/cliextensions/account
cli.azure.cli.core: automation                0.009         9        34  /Users/user/.azure/cliextensions/automation
cli.azure.cli.core: containerapp              0.011        47       139  /Users/user/.azure/cliextensions/containerapp
cli.azure.cli.core: Total (3)                 0.022        60       186  
cli.azure.cli.core: Loaded 1241 groups, 4854 commands.
cli.azure.cli.core: Updated command index in 0.002 seconds.
cli.knack.cli: Event: CommandInvoker.OnPreCommandTableTruncate [<function AzCliLogging.init_command_file_logging at 0x102652480>]
cli.azure.cli.core.azlogging: metadata file logging enabled - writing logs to '/Users/user/.azure/commands/2024-09-11.17-17-58.synapse_linked-service_create.78820.log'.
az_command_data_logger: command args: synapse linked-service create --subscription {} --workspace-name {} --name {} --file {} --debug
cli.knack.cli: Event: CommandInvoker.OnPreArgumentLoad [<function register_global_subscription_argument.<locals>.add_subscription_parameter at 0x1029d27a0>]
cli.knack.cli: Event: CommandInvoker.OnPostArgumentLoad []
cli.knack.cli: Event: CommandInvoker.OnPostCommandTableCreate [<function register_ids_argument.<locals>.add_ids_arguments at 0x1029d2840>, <function register_cache_arguments.<locals>.add_cache_arguments at 0x1029d2980>, <function register_upcoming_breaking_change_info.<locals>.update_breaking_change_info at 0x1029d2a20>]
cli.knack.cli: Event: CommandInvoker.OnCommandTableLoaded []
cli.knack.cli: Event: CommandInvoker.OnPreParseArgs [<function _documentdb_deprecate at 0x11c55b380>]
cli.knack.cli: Event: CommandInvoker.OnPostParseArgs [<function OutputProducer.handle_output_argument at 0x1013d4b80>, <function CLIQuery.handle_query_parameter at 0x1013f6660>, <function register_ids_argument.<locals>.parse_ids_arguments at 0x1029d28e0>]
cli.azure.cli.core.auth.persistence: build_persistence: location='/Users/user/.azure/msal_token_cache.json', encrypt=False
cli.azure.cli.core.auth.binary_cache: load: /Users/user/.azure/msal_http_cache.bin
urllib3.util.retry: Converted retries value: 1 -> Retry(total=1, connect=None, read=None, redirect=None, status=None)
msal.authority: Initializing with Entra authority: https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx
msal.authority: openid_config("https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/v2.0/.well-known/openid-configuration") = {'token_endpoint': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/oauth2/v2.0/token', 'token_endpoint_auth_methods_supported': ['client_secret_post', 'private_key_jwt', 'client_secret_basic'], 'jwks_uri': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/discovery/v2.0/keys', 'response_modes_supported': ['query', 'fragment', 'form_post'], 'subject_types_supported': ['pairwise'], 'id_token_signing_alg_values_supported': ['RS256'], 'response_types_supported': ['code', 'id_token', 'code id_token', 'id_token token'], 'scopes_supported': ['openid', 'profile', 'email', 'offline_access'], 'issuer': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/v2.0', 'request_uri_parameter_supported': False, 'userinfo_endpoint': 'https://graph.microsoft.com/oidc/userinfo', 'authorization_endpoint': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/oauth2/v2.0/authorize', 'device_authorization_endpoint': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/oauth2/v2.0/devicecode', 'http_logout_supported': True, 'frontchannel_logout_supported': True, 'end_session_endpoint': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/oauth2/v2.0/logout', 'claims_supported': ['sub', 'iss', 'cloud_instance_name', 'cloud_instance_host_name', 'cloud_graph_host_name', 'msgraph_host', 'aud', 'exp', 'iat', 'auth_time', 'acr', 'nonce', 'preferred_username', 'name', 'tid', 'ver', 'at_hash', 'c_hash', 'email'], 'kerberos_endpoint': 'https://login.microsoftonline.com/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/kerberos', 'tenant_region_scope': 'NA', 'cloud_instance_name': 'microsoftonline.com', 'cloud_graph_host_name': 'graph.windows.net', 'msgraph_host': 'graph.microsoft.com', 'rbac_url': 'https://pas.windows.net'}
msal.application: Broker enabled? None
cli.azure.cli.core.auth.credential_adaptor: CredentialAdaptor.get_token: scopes=('https://dev.azuresynapse.net/.default',), kwargs={}
cli.azure.cli.core.auth.msal_authentication: UserCredential.get_token: scopes=('https://dev.azuresynapse.net/.default',), claims=None, kwargs={}
msal.application: Cache hit an AT
msal.telemetry: Generate or reuse correlation_id: 8648785f-3ea6-48d9-96cc-0948637b00cf
urllib3.connectionpool: Starting new HTTPS connection (1): SYNAPSE_NAME.dev.azuresynapse.net:443
urllib3.connectionpool: https://SYNAPSE_NAME.dev.azuresynapse.net:443 "PUT /linkedservices/test?api-version=2020-12-01 HTTP/1.1" 202 420
urllib3.connectionpool: https://SYNAPSE_NAME.dev.azuresynapse.net:443 "GET /operationResults/55497dfc-111c-4d69-90e9-bfafc355473e?api-version=2020-12-01 HTTP/1.1" 202 23
urllib3.connectionpool: https://SYNAPSE_NAME.dev.azuresynapse.net:443 "GET /operationResults/55497dfc-111c-4d69-90e9-bfafc355473e?api-version=2020-12-01 HTTP/1.1" 202 23
urllib3.connectionpool: https://SYNAPSE_NAME.dev.azuresynapse.net:443 "GET /operationResults/55497dfc-111c-4d69-90e9-bfafc355473e?api-version=2020-12-01 HTTP/1.1" 202 23
urllib3.connectionpool: https://SYNAPSE_NAME.dev.azuresynapse.net:443 "GET /operationResults/55497dfc-111c-4d69-90e9-bfafc355473e?api-version=2020-12-01 HTTP/1.1" 200 606
cli.knack.cli: Event: CommandInvoker.OnTransformResult [<function _resource_group_transform at 0x1026a8400>, <function _x509_from_base64_to_hex_transform at 0x1026a84a0>]
cli.knack.cli: Event: CommandInvoker.OnFilterResult []
{
  "error": {
    "code": "BadRequest",
    "message": "Invalid payload {\r\n  \"Id\": \"/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/resourceGroups/RESOURCE_GROUP_NAME/providers/Microsoft.DataFactory/factories/SYNAPSE_NAME/linkedservices/test\",\r\n  \"Name\": \"test\",\r\n  \"Properties\": {\r\n    \"type\": \"AzureSqlDW\",\r\n    \"connectVia\": {\r\n      \"type\": \"IntegrationRuntimeReference\",\r\n      \"referenceName\": \"ManagedNetworkIR\"\r\n    },\r\n    \"annotations\": []\r\n  }\r\n} and error is Invalid linked service payload, the 'typeProperties' nested in payload is null.."
  },
  "etag": null,
  "id": null,
  "name": null,
  "properties": null,
  "status": "Failed",
  "type": null
}
cli.knack.cli: Event: Cli.SuccessfulExecute []
cli.knack.cli: Event: Cli.PostExecute [<function AzCliLogging.deinit_cmd_metadata_logging at 0x102652700>]
az_command_data_logger: exit code: 0
cli.__main__: Command ran in 39.393 seconds (init: 0.560, invoke: 38.833)
telemetry.main: Begin splitting cli events and extra events, total events: 1
telemetry.client: Accumulated 0 events. Flush the clients.
telemetry.main: Finish splitting cli events and extra events, cli events: 1
telemetry.save: Save telemetry record of length 3826 in cache file under /Users/user/.azure/telemetry/20240911171831433
telemetry.main: Begin creating telemetry upload process.
telemetry.process: Creating upload process: "/opt/homebrew/Cellar/azure-cli/2.64.0/libexec/bin/python /opt/homebrew/Cellar/azure-cli/2.64.0/libexec/lib/python3.11/site-packages/azure/cli/telemetry/__init__.py /Users/user/.azure /Users/user/.azure/telemetry/20240911171831433"
telemetry.process: Return from creating process 78858
telemetry.main: Finish creating telemetry upload process.

Expected behavior

Synapse linked service created using json file provided

Environment Summary

azure-cli 2.64.0

core 2.64.0
telemetry 1.1.0

Extensions:
account 0.2.5
automation 1.0.0b1
containerapp 0.3.53

Dependencies:
msal 1.30.0
azure-mgmt-resource 23.1.1

Python (Darwin) 3.11.10 (main, Sep 7 2024, 01:03:31) [Clang 15.0.0 (clang-1500.3.9.4)]

Legal docs and information: aka.ms/AzureCliLegal
Your CLI is up-to-date.

Additional context

From the debug output I see one strange thing to me – providers/Microsoft.DataFactory/factories/

Invalid payload {\r\n  \"Id\": \"/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx/resourceGroups/RESOURCE_GROUP/providers/Microsoft.DataFactory/factories/SYNAPSE_WORKSPACE/linkedservices/test

Also the same issue I face for linked service of type AzureSqlMI

[yonzhan]

Thank you for opening this issue, we will look into it.

[github-actions]

Here are some similar issues that might help you. Please check if they can solve your problem.

• Cannot create a linked-service of type AzureDatabricksDeltaLake #17498

[rams2011]

Faced similar issue with AzureSQLMI.
It works if you choose legacy connection string.

    "name": "AzureSQLServerMI",
    "type": "Microsoft.Synapse/workspaces/linkedservices",
    "properties": {
        "type": "AzureSqlMI",
        "connectVia": {
            "referenceName": "AutoResolveIntegrationRuntime",
            "type": "IntegrationRuntimeReference"
        },
        "annotations": [],
        "typeProperties": {
            "connectionString": "Integrated Security=False;Encrypt=True;Connection Timeout=30;Data Source=<ServerName>;Initial Catalog=<DBName>",
            "credential": {
                "type": "CredentialReference",
                "referenceName": "MI"
            }
        }
    }
}```