Snowflake Will Block Single-Factor Password Authentication for Business Objects

[JohnClark]

Snowflake Will Block Single-Factor Password Authentication by November 2025 

Snowflake released this announcement Dec 01, 2024. Our DBA team has brought this to our attention that we will need to be making changes.

Has anyone else looked into this to determine what capabilities Business Objects has to accommodate this?

From a cursory look, it looks like an ODBC connection will support the key-pair authentication method but it may require a newer Snowflake driver than is currently officially supported by SAP for Business Objects.

[Joe_Peters1]

John, have you found any more info on this? All the other platforms that we work with are supporting keypair, but not BO. It'll be an issue for us if it's not introduced before Snowflake disables user/password in November.

[JohnClark]

Joe, no we haven't found out any more. We are waiting for our DBAs to decide which method they prefer to use if they are going to choose one over the other. We are working on an upgrade too so I haven't had a log of time to look into this specifically. Once our DBA Team makes a decision, work on this will ramp up.

[Joe_Peters1]

So it looks like there is a workaround in which you make a registry change to point the ODBC driver to the key file. Not ideal, but we might have to go with that option.

[JohnClark]

Is that workaround from SAP or from Snowflake?

[Joe_Peters1]

Snowflake. It's a hack to manually add the keypair settings to an ODBC driver: https://community.snowflake.com/s/article/How-to-set-up-ODBC-Driver-with-key-pair-authentication-to-... I don't know if there's a better way, but this is what I've found so far.

[JohnClark]

Thanks. I'll add that to our list of things to look through.

[Joe_Peters1]

There's a kb article for this now: 3608512 - How to configure BI Platform for Snowflake connections using Key-Pair (Private-Public key)...

This essentially just uses the Snowflake ODBC driver, and requires connection string parameters to be updated to include the keypair file location and passphrase.

I've tested this, and it works, but I have a couple of concerns:

• If you're using IDT in "server middleware" mode, then the private key can be located on the server and the local workstation doesn't need a copy.  But if you're using UDT or CR, the private key has to be copied to the users' workstations. 
• Any universe developer with rights to "download the connection" can view the connection parameters, which includes the passphrase.  

Both of these create a security risk.  Any way to mitigate them?

[JohnClark]

I think for the universe connection, you can force you users to use their own "personal connection" and have their own key-pair to use while they are working modifying the universe and then change the connection back to the "Public" connection before they export it.  We do this sort of because we tell our Universe Developers to create a personal connection that is OLEDB for SQL Server so the Integrity Check runs better.  It should work for Snowflake as well.
If you use an ODBC DSN on the server for the ODBC connection instead of the connection string option, then your key-pair information is in the ODBC DSN on the server and not in the connection.  This prevents your users from being able to download it.  We have our connection set up with this method.

[Joe_Peters1]

hmmm. That could work, but I can see it being a real pain. Each user would need their own Snowflake account, since a service account can have only a maximum of two keypairs.

[JohnClark]

It can be a pain. Most, if not all, of our users have their own Snowflake account already anyway because they use it for other things so it isn't as big a deal for us.

[Joe_Peters1]

Thanks. Are you putting the Snowflake keypair parameters in the universe connection string or the ODBC driver parameters?

[JohnClark]

We are putting the key-pair parameters in the ODBC DSN. We are not using the connection string option. We also have to implement the registry change referenced earlier in this thread.

[amitrathi239]

You can also use Key-Pair  through creating  an entry under the Generic drivers.

Download the latest Snowflake drivers.

Goto at Location dir\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers location and create “generic” folder and place the downloaded jar file. 

Goto at Location dir\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc location and open jdbc.sbo file

 Update the path of jar file where it stored.

<Path>Location Dir\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\generic\snowflake-jdbc-3.22.0.jar</Path> 

Goto at C:\Users\username\AppData\Local location and create new folder “snowflake” 

Create connection.toml file and add all the below details in the file

[default]
account = 'Snowflake url'
user = 'snowflake user name'
private_key_file = 'C:/Users/username/AppData/Local/Snowflake/business_objects.p8'
warehouse = 'warehouse name'
database = 'database name'
schema = 'PUBLIC'
protocol = 'https'
port = '443'

Open IDT and create new connection. Newly created driver will be available under the generic 

Select and next screen enter below details.

Username: snowflake username 

Password=keep blank

JDBC URL: jdbc❄️//snowflake url/?private_key_file=location of P8 file 

JDBC Class: net.snowflake.client.jdbc.SnowflakeDriver 

During connection test IDT will take the password from P8 file

How to import & configure certificate for SAP business objects connectivity to snowflake

[JohnClark]

Thanks for the information. I was able to get an ODBC connection to work using the registry edit information posted earlier. That is the preference for what our DBA team wants us to use. this is useful information still if we run into other problems.