November 21, 2019 at 9:43 pm
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.
November 22, 2019 at 10:34 am
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
November 22, 2019 at 1:23 pm
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?
November 22, 2019 at 1:35 pm
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
November 22, 2019 at 1:55 pm
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?
November 22, 2019 at 9:18 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 25, 2019 at 1:27 pm
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?
November 25, 2019 at 2:07 pm
if you can connect via powershell, but live cannot connect - 100% firewall, DNS or port issue
MVDBA
November 25, 2019 at 2:13 pm
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?
November 25, 2019 at 4:46 pm
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
November 25, 2019 at 4:55 pm
Thanks for that additional information. I will look into these possibilities and discuss with our security folks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply