Package runs on dev, fails on server

  • Pete

    SSC Rookie

    Points: 47

    I'm kind of new at this, so please bear with me. If I haven't included enough information, I'll be happy to supply additional details.

    So, I have developed three packages, each of which connects to a different off-site SQL server and updates databases in our local server. All three validate and run in debug mode on my development machine with no errors. Two of the three also run fine on the server. But one of them fails validation with a series of error messages related to the remote server connection. (See attachment.)

    The local server is running SQL Server 2014, so I targeted that version with my packages, although I'm actually using 2017 as my development environment. I'm using the project deployment model and using EncryptSensitiveWithUserKey protection level. On the server, I'm deploying to an Integration Services Catalog. My understanding is in this environment my passwords will automatically be converted on the server. And, as I said, it is working for 2 of the 3 packages.

    Any assistance in troubleshooting this issue would be greatly appreciated.

    Attachments:
    You must be logged in to view attached files.
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21184

    I think the clue might be in "login timeout expired" - normally you get that when a firewall or DNS issue stops you from resolving the server name… not normally login credentials, which tend to bounce you out almost straight away

    if it's one of the 3 packages that is failing, is there something about the server that it is connecting to?

    I'd need more info to figure this out

    MVDBA

  • Pete

    SSC Rookie

    Points: 47

    Thanks for that helpful response, Mike. It makes sense that it seems like a DNS or firewall issue between the local SQL Server and the remote one. So I tested this theory by running a PowerShell script on the local SQL Server and was able to connect to the remote server and retrieve some data using the same credentials. So there appears to be no issue with the two servers communicating with each other.

    What other information could I provide that might be of help?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21184

    off the top of my head... are you using an ODBC DSN for connection (either a file or system DSN)? - maybe that's not configured correctly on live

    MVDBA

  • Pete

    SSC Rookie

    Points: 47

    No, I'm using OLE DB.

    Here is the connection string that doesn't work on the live server:

    Data Source={redacted};User ID={redacted};Initial Catalog=mu9165;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;Application Name=SSIS-Package1-{F2F7B6B9-9E6E-4EA5-9BD7-B3BE2C0E5C4B}{redacted};

    Here's one from a package that works (for a different remote server):

    Data Source={redacted};User ID={redacted};Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

    Do you see anything that looks amiss?

    • This reply was modified 2 weeks, 1 day ago by  Pete.
  • bmg002

    SSC-Insane

    Points: 22441

    Are you using a SQL Alias to connect?  If so, do you get the same error when you connect with the full name?  What about if you connect with the server name and port?

    As MVDBA (Mike Vessey) indicated, the login timeout expired is a good hint as to what is wrong.  I expect you are connecting over TCP/IP and not over Named Pipes, correct?  If this is right, then the "Named Pipes" mention in the error is also critical as it means it tried TCP/IP and didn't get the response it expected.

    My guess is that your firewall is blocking the connection to that other server on the specified port from SSIS.  OR that the remote SQL instance is not configured to allow connections from your SSIS server.

  • Pete

    SSC Rookie

    Points: 47

    Thank you for the suggestions. I am not using and SQL Alias; I'm using the server name (URI) and connecting over TCP/IP. The fact that I am able to connect successfully to the remote server via a Powershell script running on the SSIS server would seem to preclude a firewall issue, right?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21184

    if you can connect via powershell, but live cannot connect - 100% firewall, DNS or port issue

    MVDBA

  • Pete

    SSC Rookie

    Points: 47

    Mike, please help me understand why this is so, before I go to our security team. If I am able to connect from the same machine to the same machine using the same credentials via PS but not via SSIS, what firewall setting could possibly be causing this?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21184

    ok - this is only a guess

    Via powershell, you might be using a different protocol (and hence a different port number) - the username and password has nothing to do with this issue.

    I don't know your firewall, but inbound and outbound ports usually have restrictions on and some can be on port numbers or application names.

    this error message suggests you are using "named pipes" not TCP/IP - you need to open up the port for NP or ensure you are using TCP/IP

    MVDBA

  • Pete

    SSC Rookie

    Points: 47

    Thanks for that additional information. I will look into these possibilities and discuss with our security folks.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply