Monday, March 26, 2012

Scheduling a SSIS Package using SQL Agent (passing credentials)

Hi:

I created a fairly simple SSIS package to move data from a remote server which uses SQL Server Authentication to a local server that uses Windows Authentication. Everything works fine when executing the package within BI studio and if I execute the package by connecting to SSIS. If I schedule it as a SQL Server Agent Job, however, the non-integrated security fails unless I manually edit the connection to include the password. Obviously this is insecure because it is in plain text. What can I do?

Thanks.

You might need to change the encryption protection of your package (i.e. encrypt sensitive w/ user key, encrypt sensitive w/ password, etc) as well as setting up your proxy / credentials correctly.

Check out these MS article...

http://support.microsoft.com/kb/912911

http://support.microsoft.com/?kbid=918760

There are also many threads discussing these topics, including:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2068280&SiteID=1

|||

Thanks for your response.

First of all, I did try the encrypt sensitive w / password (the default is w / user key) and I put a password in, saved the package to SQL Server and re-setup the job. It prompted me for passwords as I was setting the job up, but it didn't help at all with the remote connection. Do I need to put my connection information into the command line (user and password) AND encrypt with the password to keep others from seeing the username and password. So far I've just done one or the other.

I also tried the proxy credentials thing, but I am not so sure I understood exactly how it works. Specifically, it was prompting for an AD account, which doesn't help me in the non-integrated (SQL) login environment.

|||

RM,

You have multiple choices, but before choosing one you have to understand the pros and cons of each one. Using Package protection level equal to DonSaveSensitive plus using package configurations is a popular method that works well. You have more details in the method 4 described in one of the KB articles that EWisdahl pointed out( http://support.microsoft.com/?kbid=918760)

Search by package configurations and you will find plenty of options and examples

|||

Interestingly enough,

When I went back into the SQL Agent Job and clicked on command line (where I had added the credentials before) it defaulted back to the original command line. If this is the case, no problem, because the password I typed is hidden, right? Of course, now when I click on the data sources tab I get an error that states my connection string is invalid and it shows no data sources, either? Is the behavior somehow interrelated? The job seems to be running fine, though!?!

No comments:

Post a Comment