SSIS automation through command line
-
I'm not sure how to explain this. It has become a big headache.
We're trying to be able to use an SSIS package that allows parameters and can be executed through command line. But we've had some problems.
When deploying the project to the IS Catalogs and using a combination of parameters and environment, we're able to run it using "dtexec /ISSERVER ..." but it fails with the following error.
: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
Is there a way to correct this error? Is there a better way to run the packages knowing that 3 parameters will change on every execution and 9 will change only depending on the target?
This package creates connections dynamically to connect to different servers. Maybe that's a key.
We don't want to start creating SQL Accounts everywhere to allow for easier connections.
Any tips that you might have?
-
Luis, do you use config files for this package?
Is this package an older one that is just now being upgraded to SQL 2012 or is it a new SQL 2012 package?
How are you determining what the connections should be for these connection managers? I'm assuming you're using an OLEDB connection manager (yes/no/maybe?).
EDIT: To clarify my third question... is the package being run manually on the command line? Through a .bat file? Through a job? If the later two, how does the package know which parameters to use for the connection?
-
You need to review how you are calling the packages, and under what account it is running. If you are using Windows Authentication then whatever account is calling the package has to have access to the server/instance.
I would assume your connections are dynamic in the sense you parameterized the connection managers, but you are using Windows Authentication. So you would need to configure a Windows account to call the package and provide that account access to any database or resource the package is trying to use.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
Luis Cazares (5/16/2016)
I'm not sure how to explain this. It has become a big headache.We're trying to be able to use an SSIS package that allows parameters and can be executed through command line. But we've had some problems.
When deploying the project to the IS Catalogs and using a combination of parameters and environment, we're able to run it using "dtexec /ISSERVER ..." but it fails with the following error.
: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
Is there a way to correct this error? Is there a better way to run the packages knowing that 3 parameters will change on every execution and 9 will change only depending on the target?
This package creates connections dynamically to connect to different servers. Maybe that's a key.
We don't want to start creating SQL Accounts everywhere to allow for easier connections.
Any tips that you might have?
I think you need to separate the idea of 'connection to server' vs. 'context being used to run process'.
Kerberos or having the same local windows account on each server (not SQL accounts) might be the answer.
Here is a link[/url] that describes the basic issue the NT Authority\ Anonymous Logon error.
-
Shawn Melton (5/16/2016)
You need to review how you are calling the packages, and under what account it is running. If you are using Windows Authentication then whatever account is calling the package has to have access to the server/instance.I would assume your connections are dynamic in the sense you parameterized the connection managers, but you are using Windows Authentication. So you would need to configure a Windows account to call the package and provide that account access to any database or resource the package is trying to use.
They're all using Windows Authentication. The package works perfectly fine when run from SSDT. The problem is only when running from the IS Catalogs.
Right now, we're calling the package using accounts that have access to the necessary servers, but still fail. Running them from the agent as a job also works fine. Running them directly, seems to lose the credentials somewhere.
-
Luis Cazares (5/16/2016)
The problem is only when running from the IS Catalogs.I do not understand what you mean by this statement. Do you mean running via SSMS with a connection to integration services? Or running through a SQL Agent job using the IS step?
Or something else?
-
Brandie Tarvin (5/16/2016)
Luis Cazares (5/16/2016)
The problem is only when running from the IS Catalogs.I do not understand what you mean by this statement. Do you mean running via SSMS with a connection to integration services? Or running through a SQL Agent job using the IS step?
Or something else?
I mean running the package directly when it's deployed to the Integration Services Catalog. Either through the GUI or using DTEXEC.
Here's a mockup package to explain where it is.
Running it from an agent job apparently works (I didn't try it, but a teammate told me so).
-
Have you tried connecting in SSMS with an IS connection instead of the Database Engine connection and running it from there?
I've never actually run a package from the catalog, so I'm unsure how to advise at this point. If the package is an older package, you might have to recreate the connection managers. Or if you were loading the connection strings from a file (.bat or .dtsconfig) you'd need to update the client details there. I think it may be defaulting to an anonymous login because it can't find the config file, but that's guessing because I haven't seen you respond to my other questions.
-
Brandie Tarvin (5/16/2016)
Luis, do you use config files for this package?Is this package an older one that is just now being upgraded to SQL 2012 or is it a new SQL 2012 package?
How are you determining what the connections should be for these connection managers? I'm assuming you're using an OLEDB connection manager (yes/no/maybe?).
EDIT: To clarify my third question... is the package being run manually on the command line? Through a .bat file? Through a job? If the later two, how does the package know which parameters to use for the connection?
OMG, I didn't see this post earlier!
I'm not using config files, but I could try. It would be the first time I do it.
This is a package developed for 2012, and has been run manually for some time now.
The connections are made with OLEDB, getting the information from a query to retrieve the servers and dbs.
The package is working manually from SSDT, but we need to automate it using command line (direct input on cmd, a bat file or Powershell). Nothing is set on stone, we're just trying to find out the best way to implement this.
-
Luis Cazares (5/16/2016)
I'm not sure how to explain this. It has become a big headache.We're trying to be able to use an SSIS package that allows parameters and can be executed through command line. But we've had some problems.
When deploying the project to the IS Catalogs and using a combination of parameters and environment, we're able to run it using "dtexec /ISSERVER ..." but it fails with the following error.
: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
Is there a way to correct this error? Is there a better way to run the packages knowing that 3 parameters will change on every execution and 9 will change only depending on the target?
This package creates connections dynamically to connect to different servers. Maybe that's a key.
We don't want to start creating SQL Accounts everywhere to allow for easier connections.
Any tips that you might have?
This is definitely a Kerberos issue. In a later post you stated that the package(s) would run fine when run in an Agent job, but not if you execute it from the catalog. The difference is that running the package using an Agent job is using the SQL Server agent account to connect to the packages and it is a single-hop (SQL Server to Connection), when running from the package is using your account (your PC to SQL Server to Connection) causing a double-hop which requires kerberos. The 3 ways to fix this are:
1. Correctly setup your servers to allow Kerberos. This article[/url] is my go-to for Kerberos.
2. Use SQL Accounts for all your connections in SSIS, which not my preferred method either.
3. Run your packages from SQL Agent.
FYI - Greg has also basically supplied this answer.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hmmm... I consider the Kerberos "double hop problem" to be a security feature rather than a fault and have been asked to eliminate the "problem" many times and have simply refused.
Wouldn't it be easier to give whatever user you're trying to allow to run this from the command prompt a batch file that uses SQLCMD to run the job itself?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jack.
A big take away from Brian's article is the Domain Administrator involvement to run setspn.
Using SSAS, SharePoint, and IIS, across multiple servers, it is a very good process to know how to setup.
I also leveraged AD Groups for security, which was very helpful.
Some of the AD Groups we had them setup to mirror Roles and Departments.
So as people moved, this followed them.
Luis -
I hope this helps you. If nothing else, you will have a better understanding of why this is happening, and what your options are.
A bonus to us actually was working with the Domain Admin.
They liked us moving the bulk of our security out into AD, as it was more visible.
Since I had pretty intimate knowledge of how all this worked, they gained a lot of respect for us.
So if we had an issue, they were very likely to help troubleshoot with us.
We were right there with them, and just as likely to come up with a solution.
-
Jeff Moden (5/16/2016)
Hmmm... I consider the Kerberos "double hop problem" to be a security feature rather than a fault and have been asked to eliminate the "problem" many times and have simply refused.Wouldn't it be easier to give whatever user you're trying to allow to run this from the command prompt a batch file that uses SQLCMD to run the job itself?
The double hop problem is indeed a security feature.
Setting up Kerberos, at least from my standpoint, allows user context to be passed without saving run as batch files.
Especially if multiple users are involved.
-
Luis Cazares (5/16/2016)
OMG, I didn't see this post earlier!I'm not using config files, but I could try. It would be the first time I do it.
This is a package developed for 2012, and has been run manually for some time now.
The connections are made with OLEDB, getting the information from a query to retrieve the servers and dbs.
The package is working manually from SSDT, but we need to automate it using command line (direct input on cmd, a bat file or Powershell). Nothing is set on stone, we're just trying to find out the best way to implement this.
If you are deploying to the catalog you can't use config files, you would use environments.
So if I understand what you are saying, you have a connection to a "configuration database" that returns the connection information you want to use for your connections. Where does the database reside that holds this connection information?
Can you check which connection is getting the authentication error? You can see package errors using the SSIS Catalog reports built into SSMS (naviage to the package in the IS Catalog -> right-click on package -> Reports -> Standard Reports -> All Executions). Once in the report click on "All Messages" and look for errors.
Why does it need to be automated via the command line vs an agent job?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Hi Luis,
I concur with Jack Corbett's first response (you actually *can* use Config files in the Catalog, I just don't recommend it...). I've encountered this issue with a host of clients. There are workarounds, but I trust the advice of security professionals when they tell me the best solution is to configure Kerberos. Brian's article (http://www.sqlservercentral.com/articles/Security/65169/) and one by Adam Saxton (https://blogs.msdn.microsoft.com/psssql/2014/06/26/getting-cross-domain-kerberos-and-delegation-working-with-ssis-package/) are the articles I reference.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply