Cannot connect to on-premise SQL Server

bk 466 Reputation points
2021-11-17T17:25:20.827+00:00

Hi All,
When testing connection to the data source (on-premise SQL Server) from pipeline I am getting the below error message
Cannot connect to SQL Database: 'SERVER', Database: '', User: 'sqlabc'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), SqlErrorNumber=-1,Class=20,State=0, Activity ID: d5fbc2c0-1da2-4b33-8348-924f74237bbe.
I checked the login on the on-premise server and the user 'sqlabc' has the right permissions. I can connect to this SQL Server using SSMS. Please advice.
Thanks

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,227 questions
{count} votes

2 answers

Sort by: Most helpful
  1. PRADEEPCHEEKATLA 90,541 Reputation points
    2021-11-18T05:21:21.507+00:00

    Hello @bk ,

    Thanks for the question and using MS Q&A platform.

    As per the error message and the SQL error number, below could be the possible root case and resolution for the issue:

    1. Make sure your server name is correct, e.g., no typo on the name.
    2. Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \ to . If you are not sure about your application, please try both ServerInstance and Server\Instance in your connection string ]
    3. Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
    4. Make sure SQL Browser service is running on the server.
    5. If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

    For more details, refer to SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified and nice video on SQL Network Interfaces error 26 Error locating server/instance specified.

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

  2. HimanshuSinha-msft 19,476 Reputation points Microsoft Employee
    2021-11-18T19:37:56.927+00:00

    Hello @bk ,
    Nice to see you on the forum after a while :)
    Just to add to what @PRADEEPCHEEKATLA said ,a fter reading message it looks to me more of connectiivty issue and the ADF is not able to connect to the onpremise DB .

    I think the integration runtime is not tagged with this ADF and if its is , may be its not running , can you please check that .

    Please do let me know how it goes .
    Thanks
    Himanshu

    -------------------------------------------------------------------------------------------------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.