Informatica data ingestion

Question

Informatica data ingestion

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 2, 2022 at 8:57 am

#4071251

Hi Experts,
As per informatica they need SYSADMIN to collect data, is there any workaround not to give SA?
https://docs.informatica.com/integration-cloud/cloud-mass-ingestion/current-version/mass-ingestion-databases/mass-ingestion-databases/mass-ingestion-databases-sources---preparation-and-usage-conside/microsoft-sql-server-and-azure-sql-database-sources.html

TIA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

start with the lowest permission they mention - and then give it a try, with a trace in place to see which commands it is trying to execute.

then grant permissions to more commands as needed until it has all required permissions.

trial and error until you get the correct set of permissions identified. And if you end up requiring a lot more than what you wish to give, but if it is still below sysadmin e.g. if it requires control server (nearly sysadmin) then you can grant it while denying some other privs.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

It clearly states that you do NOT need to grant sys_admin privs...

source connection must have the sysadmin role, OR have the db_owner role and the SELECT permission on the master.sys.fn_dblog function. Use the following SQL statements if you want the user to have the db_owner role:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

Jeff Moden wrote:

It clearly states that you do NOT need to grant sys_admin privs...
source connection must have the sysadmin role, OR have the db_owner role and the SELECT permission on the master.sys.fn_dblog function. Use the following SQL statements if you want the user to have the db_owner role:

yes but... "NOTE: For SQL Server on-premises instances, the user must have the sysadmin role." just below that quote above.

hence my suggestion.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

Thanks Federico & Jeff ,

As mentioned in the note on-premise need sysadmin , the initial load worked with minimum permission they mentioned but not the incremental. Threw below errro.

HTTP response status code is 500, Error while execute supplemental logging scripts for tables: [USE "TEST";EXECUTE sys.sp_cdc_enable_db; -- Error

org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [USE "TEST";EXECUTE sys.sp_cdc_enable_db;];

SQL state [HY000]; error code [22902]; [informatica][SQLServer JDBC Driver][SQLServer]Caller is not authorized to initiate the requested action.

Sysadmin privileges are required.; nested exception is java.sql.SQLException: [informatica][SQLServer JDBC Driver][SQLServer]

Caller is not authorized to initiate the requested action. Sysadmin privileges are required.,

USE "TEST";IF 0 = (SELECT is_tracked_by_cdc FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id AND s.name = N'dbo' WHERE t.name = N'Informatica')

EXECUTE sys.sp_cdc_enable_table

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

try to enable cdc on database manually before kicking the process.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 6

frederico_fonseca wrote:

Jeff Moden wrote:
It clearly states that you do NOT need to grant sys_admin privs...
source connection must have the sysadmin role, OR have the db_owner role and the SELECT permission on the master.sys.fn_dblog function. Use the following SQL statements if you want the user to have the db_owner role:
yes but... "NOTE: For SQL Server on-premises instances, the user must have the sysadmin role." just below that quote above.
hence my suggestion.

Crud... not sure what's going on but the link in the original post no longer works for me in either FF or Edge.

Anyway, thank you for the correction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16154 More actions · Answer 7

new link

https://docs.informatica.com/integration-cloud/cloud-mass-ingestion/current-version/mass-ingestion-databases/mass-ingestion-databases/mass-ingestion-databases-sources---preparation-and-usage/microsoft-sql-server-and-azure-sql-database-sources.html

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 8

Thanks, and, yes... it's there clear as day. Thanks Frederico.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)