A network-related or instance-specific error occurred while establishing a connection to SQL Server.

CharlieLor 551 Reputation points
2021-09-14T19:16:43.547+00:00

I have been through this tips page however I'm still getting this error. I got this error in two ways. First, when my agent job runs and it errored out. The agent job uses DTSX package. The DTSX package builds fine, no error. The same DTSX package works fine in another DB SQL Server. The second way I see this error is when I open up this agent job up, click on Steps and then at the bottom I clicked on the "Edit" button. It stalls for a while then this error window pops up.
132020-screen-shot-2021-09-14-at-21324-pm.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,697 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,736 Reputation points
    2021-09-15T02:55:18.99+00:00

    Hi @CharlieLor ,

    Are you using a proxy account to run the job?

    You may refer to the MS document Create a SQL Server Agent Proxy and the blog How to: Configure an SSIS Package to Execute as a Proxy Account to get more detail information about proxy accounts.

    In addition, the error message is general, you may follow below steps to do troubleshooting.

    1. Make sure SQL Server Service is running
    2. If a named instance, make sure SQL Server browser service is running. Make sure the instance name is spelled correct and there is actually such an instance on your target machine.
    3. Make sure SQL Server is configured to allow remote connections
    4. Examine the SQL Server error log for messages confirming that SQL is listening on the expected network interfaces and ports
    5. Test server connectivity with PING from the client machine
    6. Test port connectivity using TELNET to the server and port (from step 4) from the client machine. For example
      TELNET <server-name> 1433
    7. Check firewall settings if step 5 or 6 connectivity test fails

    If you have some confuse about the steps, please refer Troubleshoot connecting to the SQL Server Database Engine to get more information.

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments
  2. CharlieLor 551 Reputation points
    2021-09-15T13:11:38.057+00:00

    Thanks for the response but I've checked all the 1-7 steps. I have other backup jobs and they didn't fail. What could have caused this error simply by clicking on the "Edit" button of the first step 1? What actually happens when going into Job Step Properties?

    0 comments
  3. CharlieLor 551 Reputation points
    2021-09-15T13:32:27.84+00:00

    Okay, once I clicked the OK button on that popup error and eventually get to the Job Step Properties, I clicked on the "Configuration" tab right next to the "Package" tab, I got the same error popup again. I'm not sure what exactly it's checking or accessing that's causing this error.

    0 comments