I have VS2005 based .Net application that calls and execute SQL Server 2008 SSIS package and results to error. It works perfectly when I execute package by VS2005 or by selecting package file. Error occurs only with .net application. The SQL account UserName has db_owner rights to the database. The username and password have been defined in web.config Why this error occurs? This is error: -1071636471 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 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'.". -1073573396 Failed to acquire connection "ServerName.DatabaseName.UserName". Connection may not be configured correctly or you may not have the right permissions on this connection. This is the code: Imports System.Data.SqlClient Imports Microsoft.SqlServer.Dts.Runtime Dim pkgLocation As String Dim pkg As New Package Dim app As New Application Dim pkgResults As DTSExecResult pkgLocation = Package Try If Storage = "Server" Then If Trusted_Connection Then pkg = app.LoadFromSqlServer(pkgLocation, DTS_Server, Nothing, Nothing, Nothing) Else pkg = app.LoadFromSqlServer(pkgLocation, DTS_Server, DTS_UserId, DTS_Password, Nothing) End If Else pkg = app.LoadPackage(pkgLocation, Nothing) End If Catch ex As Exception lblMsg.Text = "<b>Package load did not succeed, error:</b> " & ex.Message pkg = Nothing app = Nothing Exit Sub End Try Try pkgResults = pkg.Execute() Catch ex As Exception lblMsg.Text = "<b>Package execution did not succeed, error:</b> " & ex.Message End Try Kenny_I

how did you deployed package? did you consider proper PROTECTION LEVEL during deployment? if this doesn't make sense to you, tell me what is protection level of package?http://www.rad.pasfu.com

Username may be a db_owner for the database but that doesn't mean they've got login rights to SQL Server. Give the account login permissions to SQL Server. It works from VS2005 as according to your code, it's connection via Trusted_Connection i.e. Windows Authentication If <strong>Trusted_Connection </strong>Then <strong>pkg = app.LoadFromSqlServer(pkgLocation, DTS_Server, Nothing, Nothing, Nothing) </strong> Else pkg = app.LoadFromSqlServer(pkgLocation, DTS_Server, DTS_UserId, DTS_Password, Nothing) End If Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

VS2005 project says that Protection level is EncryptSensitiveWithUserKey. It seems to be default. The .net application seems to running on Network service. I have login to windows server as AD account UserName2. Obviously I succesfully execute package file with this AD account when I directly execute without .Net application. I have no access to DB with AD account UserName2. I would always SQL user account UserName to access database. Now I realize that original login error post concerned SQL account, which I have defined in web.config. What should I do to make it working? Thanks for your help!

Do you run the .net application under same account who ssis package developed by it?http://www.rad.pasfu.com

I'm able to login to database with SQL Server 2008 Studio with SQL account 'UserName'. I have no access to Database by windows authentication. Kenny_I

Windows Server -Login to server with AD user AD_UserName2 .Net Application -Windows authentication for web interface with acccount AD_UserName2 -Application pool is runned with account Network Service -DB config in WEB.CONFIG (Integrated Security=False;User=SQL_UserName;Password=pwd123"/>) SSIS Package project -Developer opened VS2005 with integrated authentication account AD_UserName2. -ProtectionLevel = ExcryptSensitiveWithUserKey -PackagePassword = nothing (Tested with pwd 123, but no success) SQL Server 2008 Management Studio -SQL_UserName has db_owner rights and I can edit database -AD_UserName2 has no rights Kenny_I

"Application pool is runned with account Network Service" This should be problem, because you set EncryptSensitiveWithUserKey the SSIS needs to be opened with the account AD_UserName2 only! if you do it with another account, your sql authentication password will be removed and this will cause that error you get. You should deploy package with EncryptSensitiveWithPassword, then click on the ellipsis in front of PackagePassword, and type a password twice, then you need to set this password where you run the package from your .net code. http://www.rad.pasfu.com

Is this what is happening? SSIS Error Code DTS_E_OLEDBERROR occurs, because Application pool identity (Network Service) and User(AD_UserName2) who opens SSIS with VS2005 are different? What is the solution? I don't want to run service with AD user of specific individual. Should I create new service account for this? I have development environment, where I develop SSIS 2005 in XP and SQL Server 2005 is in Window Server 2003. There I have AD_UserA, which run application and AD_userB, which edit SSIS. Scenario works fine there. Kenny_I

Hi Kenny Change the ProtectionLevel to "DontSaveSensitive" and deploy the package. That should fix the issue. When you have the protection level to "EncryptSensitiveWithUserKey"; during the execution SSIS service with run the package under that login (i.e. your windows login). VivekRegards, Vivek

Unfortunately I still get same error. ->Open VS2008 ->Set Protection level to DontSaveSensitive ->Build ->Excecute package via application -1071636471 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 10.0" Hresult: 0x80040E4D Description: "Login failed for user SQL_UserName.". -1073573396 Failed to acquire connection "ServerName.DatabaseName.SQL_UserName". Connection may not be configured correctly or you may not have the right permissions on this connection.Kenny_I

Why did you set it to DontSaveSensitive?! Did you ever read my previous post?! Set it with EncryptSensitiveWithPassword, read previous post carefully. this is obvious that Don'tSaveSensitive will remove all sensitive data ( for example connection string password ) ! you just use DontSaveSensitive when connection to data source is windows authentication, this is not suitable for your case.http://www.rad.pasfu.com

Thanks everybody for helping me. I have tried with "EncryptSensitiveWithPassword" and then set twice password of SQL_username in first try. It get same login error. Then tried with password of AD_Username, which is current windows user. Result was same in both test. Should this password be one for user, which runs application pool of IIS (IIS_UserName) Perhaps I don't understant the real meaning of error. Is this Package file that return error to the web application? In this stage there has been no attempt to perform ETL routine to database? Only login that ever can execute package is the windows login? Or is it possible that SQL login defined in web.config could execute? Does application pool identity have anything to do with my problems? -1071636471 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 10.0" Hresult: 0x80040E4D Description: "Login failed for user SQL_UserName.". Kenny_I

did you provide the package password in .net code where you run the package correctly? could you paste the .net code part which run the package here ?http://www.rad.pasfu.com

This is the code: Imports System.Data.SqlClient Imports Microsoft.SqlServer.Dts.Runtime Dim pkgLocation As String Dim pkg As New Package Dim app As New Application Dim pkgResults As DTSExecResult pkgLocation = Package Try If Storage = "Server" Then If Trusted_Connection Then pkg = app.LoadFromSqlServer(pkgLocation, DTS_Server, Nothing, Nothing, Nothing) Else pkg = app.LoadFromSqlServer(pkgLocation, DTS_Server, DTS_UserId, DTS_Password, Nothing) End If Else pkg = app.LoadPackage(pkgLocation, Nothing) ' THIS IS EXECUTED. Package location is pointing to dtsx file. End If Catch ex As Exception lblMsg.Text = "<b>Package load did not succeed, error:</b> " & ex.Message pkg = Nothing app = Nothing Exit Sub End Try Try pkgResults = pkg.Execute() Catch ex As Exception lblMsg.Text = "<b>Package execution did not succeed, error:</b> " & ex.Message End TryKenny_I

where did you set PackagePassword?! you should set PackagePassword for the app object with the exact same password you set at package protection Level.http://www.rad.pasfu.com

I set PackagePassword in SSIS project of Visual Studio. This solution did not work.Kenny_I

I set PackagePassword in SSIS project of Visual Studio. This solution did not work. Kenny_I I don't meant in the SSIS Proejct, I meant in your .net code, add this line: app.PackagePassword="yourpassword" right before the LoadPackage.http://www.rad.pasfu.com

I tried this in application code and and got same errors. app.PackagePassword=Packard_Password (this password would be exacly same as password defined in SSIS project protection level) pkg = app.LoadPackage(pkgLocation, Nothing) ' THIS IS EXECUTED. Package location is pointing to dtsx file. I'm very grateful for all the help and I feel that I have learn very much in this process, but still looking for the solution. I really wonder why this occurs now. Package password should be optional? It should work if it is left empty? I have been developing with VS2005 and SQL Server is 2008. Could there be compatibility issue?

I have set protection level as EncryptSensitiveWithPassword and entered valid password. When made test and I clicked the package file and execute it asks password. I entered password and it resulted to success. This the code now: app.PackagePassword=Packard_Password (this password would be exacly same as password defined in SSIS project protection level) pkg = app.LoadPackage(pkgLocation, Nothing) ' THIS IS EXECUTED. Package location is pointing to dtsx file. Error: Package load did not succeed, error: Failed to remove package protection with error 0xC0014037 "The package is encrypted with a password. The password was not specified, or is not correct.". This occurs in the CPackage::LoadFromXML method.