2022 azure vm cant run same ssis pkg we run on prem
-
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.
-
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.
-
the $ at the end of the username makes me think that is a local machine account. I'd recommend checking if the SQL services (SSIS, SQL Server, and SQL Agent... forget the ACTUAL names or if there are more, but those 3 should be starting points) are running as a local account (such as LOCAL SYSTEM, which I think is what yours are likely running as based on the login you gave) or a domain account. If it is local, it likely won't have access to remote systems (like the ERP system).
I'd be willing to bet that the account ending in $ is the same one that one (or more) of those services are running as.
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. - stan wrote:
thx emperor100, i ran this and believe it eliminates protocols as the problem? Is that correct?
Yes, that's correct. All the protocols are enabled on this instance.
TCP/IP is important here for enabling other clients to connect to this instance.
=======================================================================
-
check this link for granting permission's
=======================================================================
-
thx all, i've never seen a post go to 2 pages so i was posting updates to the service acct suspicion in a prior reply where i wrote "stay tuned". i thought my 2 top replies on this page disappeared into thin air. i posted the exact duplicate thinking the 1st didnt take.
anyway, yes, we always use a specific acct as the agent service acct so it can log into our erps for extracts. it is added to all erps as a login i believe actually as a member of an AD group. i believe it is used as the default login acct in the absence of any overrides on the connection configs themselves.
our dba was asked to check it way at the beginning of this adventure but in hindsight it appears he checked if it was a user on the erp's not the acct sql agent was using as a service acct.
after he reset it on the vm suddenly we could run the sql agent job's 1st step and we saw data loading into our dw. For whatever reason we could only run the job using ssms when rdp'd into the vm. i think he thinks he broke something else when setting the service acct so we cant actually connect from our local instance of ssms at this time. the error we get says "the target principal name is incorrect" but i believe he is close to fixing that too.
he did fix it. i'm not going to ask him how it broke changing the service acct (i know he needed network's help because he didnt have sufficient permissions to fix it) but i'm moving on to running a second new erp step, then sharing comparison performance stats with our azure vm team and then installing at least 3 connectors (oracle, a one off oracle one of our teammates is using for ssrs and a netsuite connector that isnt from netsuite). And then building linked servers over those to further exercise this new machine. And start throwing some serious volume at this vm. and because its developer edition and even though we are talking about a serverless ssas box right in azure, perhaps show leadership what incremental processing can do for us now that we have even more time zones to deal with. thx.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply