locked
SQL Server Agent account settings RRS feed

  • Question

  • I'm a bit confused on the security options for the agent account.
    We have some SQL 2005 servers that have a new domain account for the Agent/Engine.
    These accounts don't have any rights into other SQL/Win servers.

    If my BIDS packages read/write to other servers would I have to set these touchpoints for my Agent account?
    Could I create a config file that stores the password that is not in plain text?

    Is there a reference that can help me out?

    Thanks

    Andy
    Monday, September 28, 2009 9:07 PM

Answers

  • You have two main options if you want to use Windows Authentication (no password storage required).
    1. Set up your SQL Agent accout to have enough permissions to connect to the various servers that the SSIS package needs to connect to (ie. your touchpoints), and use Trusted windows authentication in your database connection strings to those various servers.

    2. Instead, still use Trusted Authenication, but set up a credential (a domain windows account and password that has access to all your various servers, which is encrypted in MSDB) and the corresponding proxy account (allows the saved credential account to run SSIS jobs), and set the "Run As" setting on the job step as the proxy account. This will allow you more control over what permissions you give to the proxy account without opening all SQL Agent jobs to touch your important servers. When the job runs, your DTExec.exe (look in task manager on the processes tab) will run under the security token of the specified proxy account credential.

    Video here http://msdn.microsoft.com/en-us/library/dd440761.aspx
    How To KB http://support.microsoft.com/kb/912911
    More text here http://msdn.microsoft.com/en-us/library/ms190698.aspx

    3. If you need to use SQL authentication (or other systems if you use Oracle/DB2/etc) you can use configurations for non-windows accounts to set up the connection strings and save those in configurations (in the file system, or in a database table) to abstract that information outside the SSIS package design.
    http://msdn.microsoft.com/en-us/library/cc895212.aspx
    http://www.mssqltips.com/tip.asp?tip=1405

    Some folks have used creative solution to either encrypt the folder where file configurations live, or encrypt a table and decrypt the results with a view, and use the view to store configuration values in a table within SQL Server. It won't hide the password from SSIS or the person who runs the packages, but it can encrypt the storage of the passwords.


    Thx, Jason
    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance
    • Marked as answer by andrewn2000 Tuesday, September 29, 2009 8:02 PM
    Tuesday, September 29, 2009 4:36 AM