2022 azure vm cant run same ssis pkg we run on prem
-
Hi, we set up an environment on our new azure 2022 vm with a cloned "prod target warehouse", ssis project clone from prod, cloned sql agent etc etc. amongst other things the ssis points at a new erp's sales data we are testing with in just one agent step/ssis project execution for that 2019 std erp that "sits" across the pond. i have no problem using ssms (19) locally to read from that erp with my admin acct. but i do get an error using ssms (20) on the vm ssms instance trying to connect to it with my admin acct creds. see part of the error in figure 1. i did set encryption to optional and trust the certificate on ssms 20.
when i run the the ssis project on the vm configured just for the 1 erp source i get connection errors as shown below both via sql agent (presumably service acct of the vm) and vs2022 (my admin acct on an instance of vs2022 installed on vm). i even set retain connection to true as suggested at https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/sql-server-faces-connectivity-issue-ssispack-fail.
when i run vs2022 locally with the same configs under my admin, the data from the erp loads nicely into the vm's warehouse.
when i run a bogus pkg under vs2022 on the vm pointing at a different server as the source, no issues.
what are we missing?
Figure 1
===================================
Cannot connect to xxx.
===================================
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Framework Microsoft SqlClient Data Provider)
------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-error
from agent
from vs 2022 (copied from more than 1 pkg's progress tab)
Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "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.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".
Error: Error 0xC0012050 while executing package from project reference package "yyy.dtsx". Package failed validation from the ExecutePackage task. The package cannot run.
[Get data from source [17]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ERP" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
- This topic was modified 1 month, 1 week ago by stan. Reason: clarity
-
When I connect to 2022 databases on an Azure VM, I use the MSOLEDB provider, because I had trouble connecting with Native Client. Maybe you could try that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Another thing it could be is firewalls.
If I am reading things right - pulling data from ERP using SSMS (19) locally works fine, but SSMS (20) from the Azure VM gives you errors and SSIS from the Azure VM gives errors.
If I am reading things right, the Azure VM cannot talk to the ERP database at all. To me, that sounds like a firewall issue. If you have telnet installed on your local machine, try telnetting to the cloud VM on the port that hosts your SQL instance. If you get a big black screen, telnet could connect so you can rule out firewall or anything blocking the connection. You won't get past the big black screen without knowing the handshake information required for the SQL instance, but this test takes everything out of the equation while testing - no tool bugs (SSMS 19 vs SSMS 20), no driver issues (MSOLEDB vs Native client), it is just opening a network connection between your machine and the server. If connection is successful, the server will wait for an appropriate command to come in.
But to me, that sounds like a firewall issue.
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. -
-- Firewall
Also check which protocols are enabled on the SQL Instance.
=======================================================================
-
you can check protocol conectivity
-
thx phil, its tempting but then i have to wonder why the bogus pkg has no problems connecting to a different server from the vm using native.
- stan wrote:
thx phil, its tempting but then i have to wonder why the bogus pkg has no problems connecting to a different server from the vm using native.
True, I think Brian's post is worth digging in to.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - stan wrote:
thx phil, its tempting but then i have to wonder why the bogus pkg has no problems connecting to a different server from the vm using native.
True, I think Brian's post is worth digging in to.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thx emperor100, i ran this and believe it eliminates protocols as the problem? Is that correct?
-
this likely a DNS issue - within the server where SSIS is running open a command line (RDP) or a xp_cmdshell, or even a custom SSIS package just for the purpose of executing a "ping servername" where servername here is EXACTLY what the failing SSIS Package is trying to use - and to be sure sure I would change the SSIS package with the issue and log the server name onto its logging (with a c# script fireinformation block if needed be and if it does not show on the all executions catalog report)
then check that the IP being returned from the ping is the correct one.
-
also, i ran this query on both our dw and vm. i get 5 rows on our dw for registry keys ...\tcp\ip 1 thru ip 4 and ip all.
i get only one on the vm for ...\tcp\ip all.
all results show 1433 as the port.
SELECT TOP 10 *
FROM sys.dm_server_registry
WHERE registry_key like '%SuperSocketNetLib%'
AND registry_key not like '%AdminConnection%'
AND value_name in ('TcpDynamicPorts','TcpPort')
AND value_data IS NOT NULL
AND value_data != ''
AND value_data != '0'
-
looks like you are correct frederico. and i did ping the same server from my local and got a reply with no loss. in both efforts , our domain seemed to be tacked on to the end of the command and those "names" matched.
Obviously our vm knows how to find our prod dw server (remember the bogus pkg) so "DNS" is correct there? So i'm deducing that DNS is (can be) associated with particular servers and in this case one entry is missing in our vm's DNS list? Namely the name of our source erp? or do firewalls automatically eliminate any DNS not allowed in communication with the initiating server?
-
if the IP returned in both local PC and problem SSIS server are the same then the server is able to get the DNS entry for the target - on this case then it becomes a firewall or route issue (so all to do with network team). Note the PING can return timeout even if server and DNS is correctly configured - firewall can block ping for example.
a possible issue (DNS) is if reverse DNS entries are not configured for that server - but this most time would case authentication issues, not connection issues.
if IP is the same, and if remote server is a windows/*nix server you may try to RDP to it (from the SSIS Server) to see if you get connectivity - but again firewall can block these.
-
yes, they are the same. stay tuned.
i'm not sure whats going on in this thread but this is the 3rd time i'm adding this info...
our network team added the vm to an erp group thru a network product i've heard of before that from what i understand makes all non cloud erp's accessible to our vm. so now i can use the vm's ssms instance to query the erp's data . And i can use the vm's vs2022 instance to import/debug the ssis project as my admin and the chain of pkg calls runs to completion loading my warehouse with that erp's facts and dimensions. the chain starts with a pkg called root which "schedules" the dim and fact pkgs to run automatically and in the right order based on metadata contained in a rules table.
when i run the the "chain" from sqlagent (uses our service acct), not so good. i have no proof that the config user and pswd overrides for each connection would actually change the connection credentials but i tried these variations on the erp's connection config ... domain\myadmin, myadmin, domain/myadmin each with what i hope was the right pswd to my admin acct. i also just tried "retain connection" with no credentials. i'll probably post the remainder of this issue to a new post since you all have gotten me pretty far. i dont have any proof that our service acct can log into that erp either. No matter what i tried, the same 3 or so errors generate for each pkg as shown below. i'll probably repost this image in the new thread. one interesting thing that i redacted is that the user name for which the login supposedly failed is domain\ourvmname$. i dont know of a user with that name anywhere in this app.
I just noticed that the agent acct on the vm isnt the very specialized one we use everywhere else but rather one of those generic NT ones that default at installation time. ive always believed that we use this one special acct that can login from hops between servers because the target(erp here) has a login for that acct or perhaps a login for a group that acct is in. i contacted our dba to discuss.
-
Just wanted to add to Frederico_fonseca's reply about pings - pings are not a reliable way to see if a system is up. MOST security experts recommend having ICMP ECHO turned off/blocked and this is required for a ping. The reason you'd want to block it is if someone gets into your system, they can easily run a script to ping IP ranges to try to find machines that are on in an attempt to compromise them. A script like that is trivial to write. Writing a script to target them by machine name is a lot more work to do. Now that being said, if someone is in your network, you have no idea what they are going to try to compromise and they can do other checks like doing a port probe on the IP's instead of a ping, but that is a slower process and a more complex script. Still easy enough to do mind you.
But, like I said earlier, this sounds like firewall. Using telnet you can check if a port is waiting for a connection even if you don't know the protocol and syntax to send to the server, telnet is a helpful tool to check if a server is listening on a port.
Using the advice from frederico_fonseca is not going to tell you if the SQL SERVER port is open to you. You can RDP into a machine and still have the SQL SERVER ports blocked. You can ping a machine and still have ports blocked.
Now another thought, if the ERP database is on-premise and the SQL instance is in the cloud, you may need to set up a proxy to allow the connection or you may need to expose the ERP database server to the DMZ so it can be accessed from the internet. To me it sounds like inbound connections from Azure to ERP are what is being blocked, and that will be firewall, but the solution to it may be beyond the role of a DBA and fall on the networking team. And even if it is strictly firewall (not a DMZ issue), you could still have a hardware firewall that you have no access to that is blocking things. Best practice is that you want to limit access from external sources (such as Azure) to internal corporate resources. Or you may need to set up a site to site VPN between Azure and your on premise stuff.
Also, I wouldn't fully rule out DNS as DNS is sever specific. The Azure VM and your local machine MAY have different DNS servers they are relying on to map machine name to IP. The Azure one MAY be using a public DNS server while your on prem stuff is using a private DNS server.
Long story short - this is almost certainly a networking issue. But narrowing down the exact point is a challenge. My opinion it is likely firewall, but not sure if it is firewall on the Azure VM, firewall on the ERP server, hardware firewall, DMZ settings, or something else. I'd bring your networking team into the equation and they can use their tools to try to find the problem as they can probably see exactly where the connection is being blocked and why.
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply