locked
SQL server login fails in a custom task RRS feed

  • Question

  • HI, we've made some custom tasks that are basically wrappers around stored procedures. The tasks retreive the SQL Server connection informations via connection managers. Our problem is that when we use Windows Authentication, the task is working fine. But as soon as we switch to SQL Server authentication, we get Login failed for user... We included the password into dtsconfig file at the end of the connection string and we included "Persist security=true;" at the end of the connection string (so we are using OLE DB connection manager).

    Is there limitations or special treatment for using SQL Server authentication with custom tasks in SSIS?

    Thanks,
    Christian
    Saturday, October 10, 2009 12:20 PM

Answers

  • Passwords inside the Connection Managers in SSIS are considered "sensitive" information.  As such, when you reference a Connection Manager from inside a Custom Task, you aren't allowed to read the password.  When you ask for the Connection Manager's connection string, you get everything... except for the password.  That's why when you attempt to read the connection string and use it, you're not able to connect.  You should have been able to see that when debugging...

    This should be no problem, however - because you shouldn't be creating your own connection to the database.  That's what the Connection Manager is for, after all.  Instead of retrieving the connection string, constructing your own connection object, and Opening it, you should simply ask the Connection Manager for a connection.  You'll get one of the connections from the connection pool that SSIS is managing for you, and if you use an ADO.Net Connection Manager, your Task will be able to participate in a DTC transaction managed by SSIS - no additional work for you.

    Use the AcquireConnection method of the Connection Manager, like this (assuming it's a SQL connection):

    SqlConnection conn = (SqlConnection)Dts.Connections["Your Connection Manager"].AcquireConnection(null);
    Todd McDermid's Blog
    • Proposed as answer by COZYROC Tuesday, October 13, 2009 2:54 PM
    • Marked as answer by ccoteMVP Wednesday, October 14, 2009 8:18 PM
    Saturday, October 10, 2009 11:41 PM
  • It can work with an OLE DB Connection Manager with a caveat - your Script won't participate in a DTC transaction.  See here for details: Accessing OLEDB Connection Managers in a Script.
    Todd McDermid's Blog
    • Marked as answer by ccoteMVP Wednesday, October 14, 2009 8:19 PM
    Wednesday, October 14, 2009 3:55 PM

All replies

  • Passwords inside the Connection Managers in SSIS are considered "sensitive" information.  As such, when you reference a Connection Manager from inside a Custom Task, you aren't allowed to read the password.  When you ask for the Connection Manager's connection string, you get everything... except for the password.  That's why when you attempt to read the connection string and use it, you're not able to connect.  You should have been able to see that when debugging...

    This should be no problem, however - because you shouldn't be creating your own connection to the database.  That's what the Connection Manager is for, after all.  Instead of retrieving the connection string, constructing your own connection object, and Opening it, you should simply ask the Connection Manager for a connection.  You'll get one of the connections from the connection pool that SSIS is managing for you, and if you use an ADO.Net Connection Manager, your Task will be able to participate in a DTC transaction managed by SSIS - no additional work for you.

    Use the AcquireConnection method of the Connection Manager, like this (assuming it's a SQL connection):

    SqlConnection conn = (SqlConnection)Dts.Connections["Your Connection Manager"].AcquireConnection(null);
    Todd McDermid's Blog
    • Proposed as answer by COZYROC Tuesday, October 13, 2009 2:54 PM
    • Marked as answer by ccoteMVP Wednesday, October 14, 2009 8:18 PM
    Saturday, October 10, 2009 11:41 PM
  • ccote:
    This thread should be also helpful for you:
    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/3b58552e-5193-4391-adb9-5a4dc769dfdd


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Wednesday, October 14, 2009 9:28 AM
  • HI Todd,
    thanks for the reply. Can it work with oledb connection manager or does it need an ado.net connection manager? Right now, when I use an oledb connection manager, it fails. Here is the code I use:

    OleDbConnection conn = new OleDbConnection();
                conn = (OleDbConnection)connections[ConnectionManager].AcquireConnection(null);


    Here is the error I get:

    [ETLSystemLog-Execute] Error: Task Errored: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

       at ETLFramework.Ssis.Task.ETLSystemLog.ETLSystemLog.Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, Object transaction)

    Is there something I am missing?

    Thanks,
    Christian

    Wednesday, October 14, 2009 2:54 PM
  • It can work with an OLE DB Connection Manager with a caveat - your Script won't participate in a DTC transaction.  See here for details: Accessing OLEDB Connection Managers in a Script.
    Todd McDermid's Blog
    • Marked as answer by ccoteMVP Wednesday, October 14, 2009 8:19 PM
    Wednesday, October 14, 2009 3:55 PM
  • Hi Todd, worked like a charm, thanks! Since you're very knowlegeable on the scripting/custom task topics, you should consider wrtiting a book on it :-).

    Thanks again,
    Christian

    Wednesday, October 14, 2009 8:22 PM