question

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 asked CharlieLor-4693 answered

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

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-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered

Okay, I found the answer. It's still pointing to the old server that's why it's giving me this error.
132461-ssis-db.gif



ssis-db.gif (28.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered

Hi @CharlieLor-4693,

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.