none
Enforcing windows authentication via connection string variable

    Question

  • I am trying to enforce windows authentication by passing the required value for "Persist Security Info" setting in a connection string expression. This connection string is being passed from a parent package to a child package. The connection string being passed is:

    "Data Source=xxxx;User ID=xxxxx;Initial Catalog=xxxxxxx;Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=True;Auto Translate=False;"  

    The parent package is then being run from a SQL Agent Job under a different User ID (say yyyyy) as compared to the one mentioned in the connection string.

    We have another package where we use a similar dynamic connection string construction, however there is no child package.

    The second package works fine however the first package fails. I tried to output the connection string that is being passed from the parent to child package and it seems to be exactly the same that I want. However the error log in the job history shows that the package is using the User ID that is being used to run the package in the SQL Agent job in the connection string.  The error is :

    Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user 'yyyyy.".  End Error  Error: 2013-11-11 10:24:26.83     Code: 0xC00291EC     Source: Get Start Time Execute SQL Task     Description: Failed to acquire connection "Source DB". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  Error: 2013-11-11 10:24:26.97     Code: 0xC0202009    

    I cant resolve this puzzle. Why is the child package not using the user id that I want in the connection string and instead using the user ID used to run the step.Can someone advice?

     

    Monday, November 11, 2013 12:30 AM

Answers

  • I cant resolve this puzzle. Why is the child package not using the user id that I want in the connection string and instead using the user ID used to run the step.Can someone advice? 

    The "Persist Security Info" specification is not related to Windows authentication; the setting controls whether the password can be retrieved after the connection is opened. 

    The User ID in the connection string is only used for SQL authentication.  When Windows authentication is specified ("Integrated Security=SSPI"), the security context of the invoking process (SQL Server Agent in this case) is used to authenticate to SQL Server.

    If you need different SQL Server authentication for parent and child packages, consider using SQL authentication instead.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Shub_SSRS Tuesday, November 12, 2013 4:07 AM
    Monday, November 11, 2013 1:13 AM