locked
Connection failed [{"code":9056,"message":"Cannot connect to SQL Server: 'DESKTOP-R022H7L\\MSSQLSERVER1', Database: 'AdventureWorks2014', User: 'pc-admin'."}] RRS feed

  • Question

  • Hi,

    I am beginner  in ADF so just i am exploring the Azure data factory for pull the data from local computer sql server database and pushing to the azure blob using Azure data factory. but i am facing the issues with connect with local sql server.

    what i have done to establish the connection between linked service and local computer sql server db-

    step 1- connection

    step-2 create a linked service. and selected sql server then filled with some details

    step 3- created integration runtime- create new integration with self hosted and install the exc file where local sql server machine 

    step3- then filled with server host :DESKTOP-R022H7L\MSSQLSERVER1

    step4-  database name: AdventureWorks2014

    step5 - Authentication type : SQL Authentication as well i tried with windows auth.

    step 6 - user : local system user name like- pc-admin, password- password of those system.

    So please guide me how to complete this task. thanks

     

    Thursday, November 8, 2018 7:40 AM

All replies

  • Hi,

    Are you able to connect to this SQL Server via SQL Server Management Studio on the machine with self-hosted integration runtime installed?

    • Proposed as answer by kr sanjeev Friday, November 9, 2018 7:20 AM
    Thursday, November 8, 2018 8:25 AM
  • yes. I think firewall is not allowing to connect that sql port
    • Edited by kr sanjeev Friday, November 9, 2018 6:56 AM
    Friday, November 9, 2018 6:55 AM
  • Hi,

    If you configured to use self-hosted integration runtime to run Copy Activity, actually the .exe will connect to SQL Server on the local machine, so there should be no firewall issue. Could you share with me your activityRunId for further troubleshooting?

    Thanks,

    Joyce

    Monday, November 12, 2018 9:54 AM
  • Hello, I'm having the same problem, ADF V2 is not letting me connect to my local SQL Server. Any more info on this issue?

    Also, I usually connect via windows authentication in SSMS (which requires no password) however when I select this option in ADF (when creating the linked service) it's asking me to put a password, what should I put there, my Azure account pass?

    Thanks. 

    Wednesday, February 27, 2019 4:07 PM
  • Hi

    I just tried connecting using windows authentication and succeeded. You should be using the username and password that you use to unlock your computer. I have done the same and it worked without any issues.. Hope this helps.

    Thanks - Pavan

    Thursday, August 8, 2019 6:15 PM
  • Hi there,

    As Pavan mentioned you could try using windows authentication (username: domainname\username, password: Specify a password for the user account you specified for the user name - which is nothing but your windows login username and password). Please refer to below doc, which has clear instructions with examples about different authentication types supported for SQL Server linked service in ADF.

    It is recommended as a best practice to always test a linked service connection using Test Connection button while creating a new linked service (before using it in a pipeline). 



    Thanks Pavan (P Marella) for sharing your experience and resolution steps.



    Thank you

    [ If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster. ]



    Friday, August 9, 2019 10:50 PM