M
Maria-Luz Munoz
Guest
We have two environments for the DWH; one is Production and another is UAT. The requirement is to keep them in synchronisation by both being updated from the Production business DBs. The UAT/Test business DBs are not a good source for this purpose; refreshes cannot be done as we need, replicas are out of question.
We have SSIS packages run by the SQL Server agent, in both DWH servers.
From both DWH servers, the packages run under their own SQL server agent service account. Both accounts had been given access to all remote Business Dbs, the SSIS packages read.
SQL server agent service account in the DWH UAT server was deleted from the various remote Production Business Dbs, and so we are looking for the best way to set the access to the various remote Business Dbs from DWH UAT and DWH Production servers.
We are attempting to use the Proxy account to run the packages with a credential windows account that is setup with read access in all remote dbs, but we do not have access to the password. ICT suggests to create one account for each remote DB which will be different between them.
The understanding is that Credential/proxy only allows to be setup with one windows account. If ICT sets multiple accounts depending on the remote DB, can we setup multiple windows accounts in the credentials?
Would the database scoped credentials be beneficial, understanding that it is available since SQL Server 2008.
The DWH servers run SQl server 12 and 14. The remote servers also run SQL servers.
What would be the recommendation as to how the packages should be setup in relation to remote DBs access?
Continue reading...
We have SSIS packages run by the SQL Server agent, in both DWH servers.
From both DWH servers, the packages run under their own SQL server agent service account. Both accounts had been given access to all remote Business Dbs, the SSIS packages read.
SQL server agent service account in the DWH UAT server was deleted from the various remote Production Business Dbs, and so we are looking for the best way to set the access to the various remote Business Dbs from DWH UAT and DWH Production servers.
We are attempting to use the Proxy account to run the packages with a credential windows account that is setup with read access in all remote dbs, but we do not have access to the password. ICT suggests to create one account for each remote DB which will be different between them.
The understanding is that Credential/proxy only allows to be setup with one windows account. If ICT sets multiple accounts depending on the remote DB, can we setup multiple windows accounts in the credentials?
Would the database scoped credentials be beneficial, understanding that it is available since SQL Server 2008.
The DWH servers run SQl server 12 and 14. The remote servers also run SQL servers.
What would be the recommendation as to how the packages should be setup in relation to remote DBs access?
Continue reading...