How to pass Azure Data Factory Linked Service connection string as plain text?

Question

Using Azure Data Factory, I am trying to create a linked service, connecting to a local sql server through a self-hosted Integration Runtime. I am using the Python Azure SDK.

Creation of the service succeeds, but I am not able to get the connection string right in my script. It's supposed to be the server name and database name in plain text, however, the sdk only accepts the variable as a SecureString.

Here is some sample code:

from azure.mgmt.datafactory.models import (SecureString, SqlServerLinkedService)
from pprint import pprint
from azure_setup_automation.utils import print_item, pdir
from utils.config import get_config_sql, get_config_azure
from azure_setup_automation.utils import (get_azure_credentials)
from azure.mgmt.datafactory import DataFactoryManagementClient

conf = get_config_azure()
local_conf = get_config_sql()
credentials = get_azure_credentials(conf)
adf_client = DataFactoryManagementClient(credentials, conf.subscription_id)

connection_string_source = (
    f'Data Source={local_conf.server};'
    f'Initial Catalogue={local_conf.database};'
    'Integrated Security=False;'
)

ls_name_local_sql = '02_test_linked_service_local_sql'

ls_local_storage = SqlServerLinkedService(
    connection_string=SecureString(connection_string_source),
    connect_via={"referenceName": "IRTest1", "type": "IntegrationRuntimeReference"},
    user_name=local_conf.user_id,
    password=SecureString(local_conf.password)
)

ls_obj_local_sql = adf_client.linked_services.create_or_update(
    conf.resource_group_name,
    conf.azure_data_factory_name,
    ls_name_local_sql,
    properties = ls_local_storage
)

#print_item(ls_obj_local_sql)
#pdir(ls_obj_local_sql)
#print('-------------------------')
#print (ls_obj_local_sql.type)
#print (ls_obj_local_sql.serialize())

pprint (ls_obj_local_sql.as_dict())

This prints the following.

{'etag': 'XXXX-0000-0000-0000-5ba0fcb10000',
 'id': '/subscriptions/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXX/resourceGroups/XXX/providers/Microsoft.DataFactory/factories/XXXX/linkedservices/02_test_linked_service_local_sql',
 'name': '02_test_linked_service_local_sql',
 'properties': {'connect_via': {'reference_name': 'IRTest1',
                                'type': 'IntegrationRuntimeReference'},
                'connection_string': {'type': 'SecureString',
                                      'value': '**********'},
                'encrypted_credential': 'XXXXXXXXXXXXXXXXXXXXXXX',
                'type': 'SqlServer',
                'user_name': 'XXXXX\\XXXXX'},
 'type': 'Microsoft.DataFactory/factories/linkedservices'}

Here is a picture off of the ADF portal. Illustrating how the parameters show up asterisks, and how the connection fails: Link to image

Is there any way to pass these parameters in plain text without changing them manually?

Casper Lehmann Casper Lehmann · Accepted Answer · 2018-09-19T13:06:50

It was all thanks to a typo. Apparently the Americans have a different way of spelling for Catalogue:

f'Initial Catalog={local_conf.database};'

In the end, there is no need for the connection string to be in plain text, even if that's the way the portal interface presents it. I misunderstood what was going on, thinking that the error was about the sdk not being able to parse the SecureString.

How to pass Azure Data Factory Linked Service connection string as plain text?

1 Answers