1

We have a LIVE set of 3 SQL Server instances on one network and a DEV set of 3 SQL Server instances on another. Being on different networks; LIVE cannot talk to DEV however the instances can talk to each other on the same network.

In each set; we have a parent/child relationship between the instances. Ie.

    (DEV)                             (LIVE)
    Parent                            Parent
    /    \          <--X-->           /    \
Child1  Child2                     Child1  Child2

To talk between instances, we are using LINKEDSERVERS.

Both LIVE and DEV should be a mirror image of each other. All 3 instances in the set will have the same tables. Both child instances have the same procedures to send the table data up to its parent. The parent instance can send data down using its own procedures.

Parent Procedures Logic:

Loops through each child server and checks if the server is online - if it is then pull/push data - otherwise it will skip that server and go onto the next. Includes try-catches that show message when it fails.

We have SQL Jobs that runs on the parent server that calls the above parent procedures.

The problem is when one of the child instances goes down. The jobs fails on LIVE but doesn't fail on DEV. Even though the settings seem to be the same.

DEV job shows the message and succeeds - SQL Severity 0

LIVE job produces an error and fails - SQL Severity 16

DEV:

Executed as user: NT SERVICE\SQLSERVERAGENT. OLE DB provider "SQLNCLI11" for linked server "Child1" returned message "Login timeout expired". [SQLSTATE 01000] (Message 7412) OLE DB provider "SQLNCLI11" for linked server "Child1" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Message 7412) OLE DB provider "SQLNCLI11" for linked server "Child1" returned message "Login timeout expired". [SQLSTATE 01000] (Message 7412) OLE DB provider "SQLNCLI11" for linked server "Child1" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Message 7412). The step succeeded.

LIVE:

Executed as user: TI\admin. OLE DB provider "SQLNCLI11" for linked server "Child1" returned message "Login timeout expired". [SQLSTATE 01000] (Message 7412) OLE DB provider "SQLNCLI11" for linked server "Child1" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Message 7412) Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 42000] (Error 50000) OLE DB provider "SQLNCLI11" for linked server "Parent" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI11" for linked server "Parent" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412)

I did notice that the LIVE job error has mention of the parent server but DEV does not; even though the parent server is online and just the child is offline in both cases. Also, that LIVE is using a different login than DEV to execute the job.

What i've tried:

  • Verified LINKEDSERVER settings on both LIVE and DEV
  • Verified server settings match
  • Verified login has permissions for TI\admin
  • Verified query/remote timeouts set to 0 on both LIVE and DEV
  • Verified procedures are the exact same
  • Verified job settings are exactly the same   Again, everything works as expected when all instances are online.

To me this seems more like a settings issue than the procedure logic as it works fine on DEV but not LIVE.

Is there some kind of settings I am missing or something I can check?

1
  • Live procedure is executed as TI\admin, while Dev as NT SERVICE\SQLSERVERAGENT. The first one looks like AD service account, the second doesn't. To me this could be the reason you see different bahavior between the environments. Did you check from this angle?
    – rois
    Oct 14, 2023 at 0:40

1 Answer 1

0

looks like Named Pipes protocol is not enabled on the linked server.

Similarly Check for TCP/IP protocol also.

2
  • How do you check on linked server object?
    – Drees
    Oct 13, 2023 at 19:08
  • Settings are the same for the servers themselves
    – Drees
    Oct 13, 2023 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.