none
[Execute SQL Task] Error: Failed to acquire connection "(local).DatabaseName". Connection may not be configured correctly or you may not have the right permissions on this connection.

    Question

  • Hi,

    I am trying to set up dynamic connections with variables. I have created a package with an Execute SQL task to Truncate the table in which date will be migrated. The Execute SQL task is connected to  a sequence container with two data flow tasks.

    I have an OLE DB connection set up in the connection manager and the same database is used in the package Data Source. The package with these settings executes without any problems.

    To make it dynamic I made 3 variables:

      1.containing the Server name

      2.containing the Database name 

      3. containing the Connection String .

    Then I enabled package configuration and created an xml configuration type with the first two variables added to the property attributes.

    For the third variable, in properties I set Evaluate Expression = true and added to the expression the following:

    "Data Source=" +@[User::Variable1]+"; Provider=SQLNCLI10.1;Initial Catalog="+@[User::Variable2]

    Then I updated the connection manager by adding to the expression a connection string property. The connection string property is the third variable containing the variables with Server name and Database name.The connection is Windows Authentication.

    The package not executing and the error message in the log is :

    [Execute SQL Task] Error: Failed to acquire connection "(local).Database". Connection may not be configured correctly or you may not have the right permissions on this connection.

    Task Execute SQL Task failed

    [Connection manager "(local).Database"] 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 10.0"  Hresult: 0x80040E4D  Description: "Invalid authorization specification".

    Can someone advise what can be the cause of this?

    Thanks

    Suhi



    • Edited by Suhi100 Sunday, December 08, 2013 3:46 PM
    Sunday, December 08, 2013 3:39 PM

Answers

  • Hi Suhi,

    The issue occurs because you didn’t specify the authentication mode in the connection string of the Connection Manager. To address the issue, we need to add “Integrated Security=SSPI” to the connection string to make sure the data source uses Windows integrated security. So, the expression for the ConnectionString variable is like below:

    "Data Source=" + @[User::ServerName] +";Initial Catalog=" + @[User::DBName] + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Suhi100 Friday, December 13, 2013 3:47 PM
    Wednesday, December 11, 2013 4:07 AM
    Moderator

All replies

  • is the instance your default instance on the machine?

    First check if connectionstring variable is getting the expected value. 

    Also check the edition you're trying to connect.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, December 08, 2013 6:40 PM
  • yes the instance is the default instance on the machine.

    Looking at the Value property of the connection string variable it is:

    Data Source=local; Provider=SQLNCLI10.1;Initial Catalog=database

    It only contains Data Source and Initial Catalog based on the variables and it looks fine. Should there be more deatils added to the connectionstring variable?

    Sunday, December 08, 2013 6:47 PM
  • There is one more thing: If I do not update the connection manager by adding the variable to the property expression editor containing the connection string and then I click on Connection String Poperty I can set the connection to be Windows Authentication

    If I, however add the variable to the property expression editor that holds this value:

    Data Source=(local); Provider=SQLNCLI10.1;Initial Catalog=AdventureWorks2008

    and then I click on the Connection String Property it is SQL server authentication selected by default and if I change it to windows authentication close it and reopen  it it is still on Sql authentication...

    As long as it is on Windows Authentication it works,if it is on Sql authentication I get error messages but it does not let me use Windows authentication.

    Is there a way to amend the connection string so that it sets up the connection windows authentication by default?

    Thanks!

    Monday, December 09, 2013 8:22 PM
  • Hi Suhi,

    The issue occurs because you didn’t specify the authentication mode in the connection string of the Connection Manager. To address the issue, we need to add “Integrated Security=SSPI” to the connection string to make sure the data source uses Windows integrated security. So, the expression for the ConnectionString variable is like below:

    "Data Source=" + @[User::ServerName] +";Initial Catalog=" + @[User::DBName] + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;"

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Suhi100 Friday, December 13, 2013 3:47 PM
    Wednesday, December 11, 2013 4:07 AM
    Moderator
  • Thanks Mike,

    Tried it with your connection string and it works!

    Regards

    Suhi

    Friday, December 13, 2013 3:47 PM