Constraint Violating Yak Guru
Posted - 12/05/2013 : 16:46:04
| Say that I'm using Package Configurations and storing those configs on SQL server. I create a db called Config and using the wizard, create a table in it called Configs using a connection called Config_Conn. I also have another table in the same DB for other uses, called MyTable, using the same connectiom
Now, suppose that this db lives on a server that I can only access with SQL Server authentication. That is, I've previously setup an OLE DB Connection manager called Config_Conn, which points to the database Config on the SQL Server I'm using, using SQL Server authentication.
Next, I have a database I want to access from my package. Call it MyOtherDB and let is use the connection manager MYOtherDB_Conn. I want to configure the connection manager for this DB at run time using Package Configurations. So, to summarize I have:
Server: MyServer, only supports SQL Server authentication
Database: Config, with a table, Configs that holds my configurations
Tables: Configs, MyTable
Database: MyOtherDB, possibly on another server, that I want to configure using the package configuration
The Package configuration has two entries:
1. Connect_Config, for the connection Config_Conn so that I can get at MyTable at run time
2. Connect_MyOtherDB for the connection MYOtherDB_Conn
My question is this:
When SSIS starts up, how does it authenticate to the server holding the Config database? It seems like a catch-22.