SSIS configurations for storing passwords - windows authentication

Question

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'

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

I don't have the Secondary Logon service running on the remote server, so the RunAs option cannot be used.

Answer 5

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

Answer 6

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..

Answer 7

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

Answer 8

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).

Answer 9

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.aspx

---

Arthur My Blog