Change SAP authentication to DuckDB Secret manager

[jrosskopf]

The DuckDB secrets manager provides a unified user interface for secrets across all backends that use them. Secrets can be scoped, so different storage prefixes can have different secrets, allowing for example to join data across organizations in a single query. Secrets can also be persisted, so that they do not need to be specified every time DuckDB is launched.

We want to use the secrets manager available since v0.9.2 to resplace the SET commands. Meaning instead of

# Setup connection parameters
SET sap_ashost = '[HOST IP ADRESS]';
SET sap_sysnr = '00';
SET sap_user = '[USERNAME]';
SET sap_password = '[PASSWORD]';
SET sap_client = '001';
SET sap_lang = 'EN';

One can do then

CREATE SECRET (
	TYPE SAP_RFC,
	ASHOST '${HOST_IP_ADDRESS}',
	SYSNR '00',
        CLIENT '001',
	USER '${USERNAME}',
        PASSWORD '${PASSWORD}',
        LANG 'EN'
)

[gregorwolf]

Will this also fix the issue that right now I get the error:

D PRAGMA sap_rfc_ping;
IO Error: Error during SAP RFC logon: RFC_LOGON_FAILURE: Name or password is incorrect (repeat logon)

when using a password that contains $?

[jrosskopf]

No, unfortunately we were not aware of that issue. And we have to really look into if if is a problem on our side. Maybe you can open a separate issue? Does `select * from duckdb_settings();` output the correct password? Cheers, Joachim Am Mi., 19. Juni 2024 um 16:26 Uhr schrieb Gregor Wolf < ***@***.***>:

…

Will this also fix the issue that right now I get the error: D PRAGMA sap_rfc_ping; IO Error: Error during SAP RFC logon: RFC_LOGON_FAILURE: Name or password is incorrect (repeat logon) when using a password that contains $? — Reply to this email directly, view it on GitHub <#35 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAGP6JOM2CDURJ7YUXXUTF3ZIGIK3AVCNFSM6AAAAABI63SJSGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNZYHA2TGMZQHE> . You are receiving this because you were assigned.Message ID: ***@***.***>

[gregorwolf]

Right now I've used the SET sap_password = '...' option. To get the value passed correctly I had to excape with \$.