Snowflake database connection with keypair not recognising schema

​@brendanneal , 
Did you have a connection configured before moving to KeyPair that worked for you? Using this lowercase role name?

I’m wondering if the error is a red herring...

Another thing to check is whether the role name is correct in the connection parameters. Upper case? You might be getting the default role that doesn’t have permissions on the schema dbo...

I have not tested with a lowercase schema value before… usually, lowercase names are wrapped with  quotes : i.e. “myschema“.

So you might want to try schema=”dbo”… 

I know we don’t have a lowercase schema name in our Snowflake environment so I’d have to test that works… you are my remote tester here. ;) 

Keep us posted… and if this persists, please open a support ticket with us.

 

Hi, this is a new migration to snowflake and DBA have only set up keypairs. The role name is PWA_READER in capital's, I just tested with lowercase and I’m getting the same error with the schema. As mentioned in original message and have just tried again, I have tried all these options for the schema dbo “dbo” DBO “DBO”. The error message with double quotes lowercase “dbo” is a little different to the first error message, interesting that it is saying current schema is ‘dbo’, so I take it, it recognises that schema but is different to what is in the connection string?  COM.safe.fme.jdbc.TranslationFailureException: java.lang.IllegalStateException: Current schema 'dbo' does not match the schema specified in connection settings '"dbo"'. Make sure that schema '"dbo"' specified in connection settings exists and is accessible by the user

Further to this, yesterday I have been able to set up an ODBC connection and have just used the lowercase schema=dbo with no quotes and have been able to connect in FME through and ODBC 3.x reader and also through Excel. But preferred method would be JDBC

​@brendanneal,

I did some testing.  I created a role and schema in lowercase in Snowflake.

In FME Database Connection I could pass the lowercase values with no quotes and could connect.

In DBeaver, I couldn’t quote the schema (it would error), but I had to double quote the role.

In FME, the connection string is printed in the log. If you could, when testing the connection, once you get the error, click on the Show Log button and look for “creating connection”.  It will be around line 7.
Example: 
Snowflake Spatial Reader: Creating connection using connection string 'jdbc:snowflake://safesystems.snowflakecomputing.com/' and properties '{schema="dbo", password=************, tracing=OFF, role="test_support", application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, warehouse="#####", user=####, db="#####", GEOMETRY_OUTPUT_FORMAT=EWKB}'

Ensure the DBA reviews the ‘case’ for all parameters being passed in the connection string, not just the schema.

I feel like the error you are getting back is incorrect and likely is permissions related. My best guess is that the role is not correct and is defaulting to your profile’s default role.

Feel free to open a case if you can’t move this forward.  We can jump on a call.
I think you are close.

Hi, ​@steveatsafe 

Below is a DBeaver screen grab, the current role is definitely uppercase and I have 3 schemas which I checked with second screen grab, one uppercase, 2 lowercase. In FME I tried to connect to the INFORMATION_SCHEMA , voilà! I have connected fine, connection string below, no quotes needed for role or schema, as soon as I change schema to lowercase I start getting the error, connection string for dbo and “dbo” also below and the error. I still believe there is an issue as to how FME is passing the lowercase schema parameter?? but you are able to connect with lowercase, so not sure what is going on. I will have to check with DBA more about the schema and how it was created or does it need to be lowercase.

I also tried the PUBLIC role which has permissions to same schema’s and the same result. If I double quote the role parameter, I connect to uppercase schema and get same error for lowercase schema

Connection String role=PWA_READER, schema=INFORMATION_SCHEMA

Creating connection using connection string 'jdbc:snowflake://********************.snowflakecomputing.com/' and properties '{schema=INFORMATION_SCHEMA, private_key_file=C:/TEMP/database_keys/rsa_key_pwa/rsa_key_pwa.p8, role=PWA_READER, application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, warehouse=RESILIENCE_GENERAL_WH, user=DAC_RA_SNOWFLAKE_PWA_READER@DAC.NSW.GOV.AU, GEOMETRY_OUTPUT_FORMAT=EWKB, db=IC_PROD}'

Snowflake Spatial Reader: Connection established with current database 'IC_PROD' and current schema 'INFORMATION_SCHEMA'

Connection String role=PWA_READER, schema=dbo

Creating connection using connection string 'jdbc:snowflake://*****************.snowflakecomputing.com/' and properties '{schema=dbo, private_key_file=C:/TEMP/database_keys/rsa_key_pwa/rsa_key_pwa.p8, role=PWA_READER, application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, warehouse=RESILIENCE_GENERAL_WH, user=DAC_RA_SNOWFLAKE_PWA_READER@DAC.NSW.GOV.AU, GEOMETRY_OUTPUT_FORMAT=EWKB, db=IC_PROD}'

Error: COM.safe.fme.jdbc.TranslationFailureException: java.lang.IllegalStateException: Connection established with no current schema. Make sure that schema 'dbo' specified in connection settings exists and is accessible by the user

Connection String role=PWA_READER, schema=”dbo”

Creating connection using connection string 'jdbc:snowflake://****************.snowflakecomputing.com/' and properties '{schema="dbo", private_key_file=C:/TEMP/database_keys/rsa_key_pwa/rsa_key_pwa.p8, role=PWA_READER, application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, warehouse=RESILIENCE_GENERAL_WH, user=DAC_RA_SNOWFLAKE_PWA_READER@DAC.NSW.GOV.AU, GEOMETRY_OUTPUT_FORMAT=EWKB, db=IC_PROD}'

Error: COM.safe.fme.jdbc.TranslationFailureException: java.lang.IllegalStateException: Current schema 'dbo' does not match the schema specified in connection settings '"dbo"'. Make sure that schema '"dbo"' specified in connection settings exists and is accessible by the user