none
"Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'UserName'." RRS feed

  • Question

  • 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
    • Edited by Kenny_I Monday, June 6, 2011 1:44 PM
    Monday, June 6, 2011 11:21 AM

Answers

  • 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
    • Marked as answer by Kenny_I Wednesday, June 8, 2011 2:09 PM
    Tuesday, June 7, 2011 10:48 AM
    Moderator

All replies

  • 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
    Monday, June 6, 2011 11:29 AM
    Moderator
  • 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
    Monday, June 6, 2011 11:39 AM
  • 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!



    • Edited by Kenny_I Monday, June 6, 2011 1:43 PM
    Monday, June 6, 2011 11:46 AM
  • Do you run the .net application under same account who ssis package developed by it?
    http://www.rad.pasfu.com
    Monday, June 6, 2011 12:15 PM
    Moderator
  • 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
    • Edited by Kenny_I Monday, June 6, 2011 1:37 PM
    Monday, June 6, 2011 12:20 PM
  • 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

    Monday, June 6, 2011 12:47 PM
  • "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
    Tuesday, June 7, 2011 5:21 AM
    Moderator
  • 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
    Tuesday, June 7, 2011 6:08 AM
  • 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).

    Vivek


    Regards, Vivek
    Tuesday, June 7, 2011 7:31 AM
  • 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
    Tuesday, June 7, 2011 7:54 AM
  • 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
    Tuesday, June 7, 2011 8:02 AM
    Moderator
  • 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
    Tuesday, June 7, 2011 8:25 AM
  • 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
    Tuesday, June 7, 2011 8:27 AM
    Moderator
  • 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 Try


    Kenny_I
    Tuesday, June 7, 2011 9:51 AM
  • 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
    Tuesday, June 7, 2011 10:15 AM
    Moderator
  • 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
    • Marked as answer by Kenny_I Wednesday, June 8, 2011 2:09 PM
    Tuesday, June 7, 2011 10:48 AM
    Moderator
  • I got similar error - but running Integration Services under domain user account, resolved this issue.

    hope it helps,


    HydPhani

    Tuesday, February 21, 2012 5:20 AM
  • Hi Reza,

      I did the same what you have explained but i am getting issue of "login failed for user..."

     I execute the package from the windows service under local system for this service as well integration service is also under local system

    Please help.

       

    Saturday, December 22, 2012 6:32 AM