SSIS configurations for storing passwords - windows authentication
Hi, My SSIS package has a database connection to a SQL Server database which only supports windows authentication. I'm storing the username and password required for the database connection in a xml config file as the context that the package runs under doesn't not have access rights to the database. My question is, when the database server only supports windows authentication, would SSIS used the supplied username and password in the config file to connect to the database? I'm getting the following error: Login failed for user 'xyz\xyz'
May 31st, 2012 2:16pm

No, the packages uses the user that runs the package (you in visual studio/bids or the user that runs the scheduled job)Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2012 2:24pm

Thanks for your reply. I'm actually using WMI (using VBScript) to remotely execute the package on a remote server using dtexec. I read some posts on the internet that suggested that when a process is started remotely using WMI, it cannot delegate our credentials to the remote machine. This may be causing the SSIS package to run under anonymus login and failing to connect to the database. On the other hand, the confusing part is the SSIS text log shows that the package is running under my credentials: "OnError,ServerName,xyz\xyz(my credentials), TaskName.." Any ideas?
May 31st, 2012 3:24pm

If running this script using a specially created Win NT account (that has access) sing Windows Scheduler is not possible, then consider executing it with the RunAs DOS command, this is similar to using a proxy in in SSIS step of SQL Agent.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2012 3:34pm

I don't have the Secondary Logon service running on the remote server, so the RunAs option cannot be used.
May 31st, 2012 4:36pm

Then what Windows account are you planning to use to connect to SQL Server? Your own? What if the password expires? If you cannot use a proxy account to run the package then you have an unresolvable problem. You have to use a domain proxy to run this package.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2012 10:14pm

Assuming that I create a proxy account and I store the credentials (username and password) of that account in the config file. But since the database only supports windows authentication, would SSIS use the proxy account credentials to connect to the database or the credentials of the user who is runnning the package? The credentials of the user running the package is anonymus since it has been kicked off using WM which does not delegate the credentials. Hope I'm making sense here..
June 1st, 2012 4:14pm

Unfortunately no. You should never store any credentials anywhere. The proxy account would be a Windows NT non-interactive service account with a never expiring password. See the how to here: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/ Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2012 4:19pm

Thanks Arthur. The reson why we are not using SQL Agent is we are having our SSIS on a different server than our SQL database server. So I was looking into different options of remotely executing an SSIS package. However I don't think the WMI method is a good option as it doesn't properly delegate the credentials (specially when the database engine only supports windows authentication).
June 1st, 2012 4:34pm

You have to execute it programmatically then using the Integrated (Windows) security. You may resort to writing a small VB/C# console app to do so, but again it needs to run in the security (account) contest that permits to access the Db, see an example: http://msdn.microsoft.com/en-us/library/ms403355.aspxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 1st, 2012 4:45pm

You have to execute it programmatically then using the Integrated (Windows) security. You may resort to writing a small VB/C# console app to do so, but again it needs to run in the security (account) contest that permits to access the Db, see an example: http://msdn.microsoft.com/en-us/library/ms403355.aspxArthur My Blog
June 1st, 2012 4:46pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics