ssisdb project deployment model

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

ssisdb project deployment model

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2013-05-26 : 04:44:03

I have an SSIS project solution which I have deployed from our development server to a SSISB Catalog on our dedicated SSIS test server. All the databases used by the packages are on our development server. I have created project parameters for connection strings to the databases. The connection strings use Windows integrated security.

On the SSIS server I have created an environment called TEST and have tried to execute one of the packages from SSMS on the SSIS server. However the package fails giving the following error

CreationSearch_FullLoad: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'.".

Anybody have an idea why this is happening. I am able to run the package with my credentials in BIDS.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-26 : 07:57:27

quote:
Originally posted by sauce1979

I have an SSIS project solution which I have deployed from our development server to a SSISB Catalog on our dedicated SSIS test server. All the databases used by the packages are on our development server. I have created project parameters for connection strings to the databases. The connection strings use Windows integrated security.

On the SSIS server I have created an environment called TEST and have tried to execute one of the packages from SSMS on the SSIS server. However the package fails giving the following error

CreationSearch_FullLoad: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'.".

Anybody have an idea why this is happening. I am able to run the package with my credentials in BIDS.

If you are launching SSMS on your local machine and then connecting to SSIS on the test server: Instead, log on to Windows on the TEST server and then try to run the package from there.

If that succeeds, that will tell us something - we can figure out how to fix it then.

If that also fails, open the package (in BIDs or just by double clicking the package) and look under the connection managers tab to see what it says for the connection managers. Are they all valid connections on the TEST server?

sauce1979
Starting Member

47 Posts

Posted - 2013-05-26 : 13:48:22

quote:
Originally posted by James K

quote:
Originally posted by sauce1979

I have an SSIS project solution which I have deployed from our development server to a SSISB Catalog on our dedicated SSIS test server. All the databases used by the packages are on our development server. I have created project parameters for connection strings to the databases. The connection strings use Windows integrated security.

On the SSIS server I have created an environment called TEST and have tried to execute one of the packages from SSMS on the SSIS server. However the package fails giving the following error

CreationSearch_FullLoad: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'.".

Anybody have an idea why this is happening. I am able to run the package with my credentials in BIDS.

If you are launching SSMS on your local machine and then connecting to SSIS on the test server: Instead, log on to Windows on the TEST server and then try to run the package from there.

If that succeeds, that will tell us something - we can figure out how to fix it then.

If that also fails, open the package (in BIDs or just by double clicking the package) and look under the connection managers tab to see what it says for the connection managers. Are they all valid connections on the TEST server?

I have run the package direct from SSMS on the test server and it ran successfully. My next question is what the best account to use to run the packages from the Test Server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 01:22:33

quote:
Originally posted by sauce1979

quote:
Originally posted by James K

quote:
Originally posted by sauce1979

I have an SSIS project solution which I have deployed from our development server to a SSISB Catalog on our dedicated SSIS test server. All the databases used by the packages are on our development server. I have created project parameters for connection strings to the databases. The connection strings use Windows integrated security.

On the SSIS server I have created an environment called TEST and have tried to execute one of the packages from SSMS on the SSIS server. However the package fails giving the following error

CreationSearch_FullLoad: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'.".

Anybody have an idea why this is happening. I am able to run the package with my credentials in BIDS.

If you are launching SSMS on your local machine and then connecting to SSIS on the test server: Instead, log on to Windows on the TEST server and then try to run the package from there.

If that succeeds, that will tell us something - we can figure out how to fix it then.

If that also fails, open the package (in BIDs or just by double clicking the package) and look under the connection managers tab to see what it says for the connection managers. Are they all valid connections on the TEST server?

I have run the package direct from SSMS on the test server and it ran successfully. My next question is what the best account to use to run the packages from the Test Server.

Best approach would be to create a proxy account for execution of packages. Give the account only access it requires for package execution as well as access to folders where it needs to fetch files, save logs etc. Then configure packages to use this account for execution in sql agent job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sauce1979
Starting Member

47 Posts

Posted - 2013-05-27 : 08:58:46

Thanks for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 13:51:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Steve Seeley
Starting Member

1 Post

Posted - 2013-11-11 : 14:45:41

But what if you did not want to run this as a job? I am seeing the same problem. I can run an individual package if I remote into the remote server but if I am connected via ssms on my local box I get the same "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'.

How do I change the user that the packages run under when run remotely via the catalog. I am connecting to the remote server with the same user.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-12 : 01:54:52

quote:
Originally posted by Steve Seeley

But what if you did not want to run this as a job? I am seeing the same problem. I can run an individual package if I remote into the remote server but if I am connected via ssms on my local box I get the same "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'.

How do I change the user that the packages run under when run remotely via the catalog. I am connecting to the remote server with the same user.

Thanks

Is the remote server under same domain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rrogersiii
Starting Member

1 Post

Posted - 2014-01-07 : 11:03:59

quote:
Originally posted by visakh16

quote:
Originally posted by Steve Seeley

But what if you did not want to run this as a job? I am seeing the same problem. I can run an individual package if I remote into the remote server but if I am connected via ssms on my local box I get the same "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'.

How do I change the user that the packages run under when run remotely via the catalog. I am connecting to the remote server with the same user.

Thanks

Is the remote server under same domain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh16 - I'm a new user, but I have the same problem. YES: all of our servers are on the same domain. I know how to configure a proxy for SQL Agent, but there are times when I need to kick a package off manually, or when packages under development need to execute a deployed SSISDB package. During these times, I'm logged in as myself on my workstation and the SSISDB is remote (I have proper access rights for all databases in question). But both of these scenarios run into the same "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." problem.

Do you know how to work around this?

Robert Rogers
Sr. BI Developer
Best Doctors, Inc.

ansonee
Starting Member

3 Posts

Posted - 2016-06-02 : 15:09:48

This is actually a known issue with SQL Server 2012.

Apparently the new deployment model doesn't support credential delegation, so you are forced to either have your test/dev/prod environments on the same box or run packages as sql jobs.

I think this renders this whole "new and improved" deployment model completely useless!!!!!

Why on God's green Earth would someone have all of their environments on one server?!?!?

When I deploy packages, I would just like to run them one at a time without having to create a SQL job!!!

A. M. Robinson

ansonee
Starting Member

3 Posts

Posted - 2016-06-02 : 15:11:06

https://waheedrous.wordpress.com/2013/12/23/ssis-2012-nt-authorityanonymous-logon/

A. M. Robinson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources