locked
Unable to set credentials for a linked service to an on premises SQL Server RRS feed

  • Question

  • I have installed the Data Management Gateway on a server also running a SQL Server test database that I want to access from Data Factory. Using that gateway in a new linked service to the local database, I am unable to set the credentials for the service. I tried that both through the JSON script editor for the linked service, and through the GUI for creating a new data store. In the latter case, I've used the click once application that pops up when setting credentials. That seems to work, but when click OK in the credentials blade, the settings blade shows 'Credentials not set'. 

    So far, I have figured out that the problem is in the particular machine running the gateway, as connecting to other databases using gateways on other machines does work. But that is not an option, as these gateways do not belong to the network I have to use. But I do not know how to debug this further. There are no messages in the event log on the machine. So, I'm a bit stuck here...

    Monday, November 30, 2015 9:49 AM

All replies

  • By further investigating this issue, I found out that the problem is closely related to the fact that I had to run IE on the development server to set the credentials as my workstation cannot access the development server as it is not in the domain. So I'm almost 100% sure that the issue has to do with the click once app (the Credentials Manager) that does not work correct on the server. 

    I found two workarounds for this issue: use PowerShell to get the required encrypted credential info and paste that in the JSON script for the linked service, or find a way to set the credentials from a workstation, not a server. I've tested both and they both work. 

    It still remains unclear to me what I would need to do to be able to set the credentials from the server... So, if someone knows the answer to that, please let me know!

    Monday, November 30, 2015 12:03 PM
  • Can you share the encrypted credential info in the JSON script for the linked services?

    I am unable to set credentials even from another workstation not where the gateway resides.

    Wednesday, March 16, 2016 6:47 PM
  • Can you share the encrypted credential info in the JSON script for the linked services?

    I am unable to set credentials even from another workstation not where the gateway resides.

    You may need to check whether the FQDN is valid and port 8050 is open on the gateway machine. The FQDN and port 8050 of gateway machine are used by the ClickOnce app for connecting.
    Monday, April 11, 2016 4:05 AM