SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 ssisdb project deployment model
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sauce1979
Starting Member

46 Posts

Posted - 05/26/2013 :  04:44:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 05/26/2013 :  07:57:27  Show Profile  Reply with Quote
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?
Go to Top of Page

sauce1979
Starting Member

46 Posts

Posted - 05/26/2013 :  13:48:22  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/27/2013 :  01:22:33  Show Profile  Reply with Quote
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
Go to Top of Page

sauce1979
Starting Member

46 Posts

Posted - 05/27/2013 :  08:58:46  Show Profile  Reply with Quote
Thanks for your help
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/27/2013 :  13:51:08  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Steve Seeley
Starting Member

USA
1 Posts

Posted - 11/11/2013 :  14:45:41  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/12/2013 :  01:54:52  Show Profile  Reply with Quote
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
Go to Top of Page

rrogersiii
Starting Member

USA
1 Posts

Posted - 01/07/2014 :  11:03:59  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000