CODEWITHSUNDEEP
restjdbcsnowflake-cloud-data-platform
Afshan Sheikh
Afshan Sheikh

Reputation: 1

Unable to connect to Snowflake through JAVA REST API

I am unable to connect to Snowflake via Java REST API. Have followed the Snowflake documentation regarding Connecting to Snowflake with JDBC driver but getting the following error on eclipse console:

net.snowflake.client.jdbc.SnowflakeSQLException: Incorrect username or password was specified. at net.snowflake.client.core.SessionUtil.newSession(SessionUtil.java:577) ~[snowflake-jdbc-3.9.2.jar:3.9.2] at net.snowflake.client.core.SessionUtil.openSession(SessionUtil.java:266) ~[snowflake-jdbc-3.9.2.jar:3.9.2] at net.snowflake.client.core.SFSession.open(SFSession.java:510) ~[snowflake-jdbc-3.9.2.jar:3.9.2] at net.snowflake.client.jdbc.SnowflakeConnectionV1.initialize(SnowflakeConnectionV1.java:135) ~[snowflake-jdbc-3.9.2.jar:3.9.2] at net.snowflake.client.jdbc.SnowflakeConnectionV1.(SnowflakeConnectionV1.java:117) ~[snowflake-jdbc-3.9.2.jar:3.9.2] at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:137) ~[snowflake-jdbc-3.9.2.jar:3.9.2] at java.sql.DriverManager.getConnection(Unknown Source) ~[na:1.8.0_151] at java.sql.DriverManager.getConnection(Unknown Source) ~[na:1.8.0_151] at com.system.monitor.registry.RegistryRepository.findAll(RegistryRepository.java:70) ~[classes/:na] at com.system.monitor.registry.RegistryRepository$$FastClassBySpringCGLIB$$1199dc92.invoke() ~[classes/:na] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) ~[spring-aop-5.2.0.RELEASE.jar:5.2.0.RELEASE] at com.system.monitor.registry.RegistryRepository$$EnhancerBySpringCGLIB$$b552203e.findAll() ~[classes/:na] at com.system.monitor.registry.RegistryController.getAllRegistries(RegistryController.java:19) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_151] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_151] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_151] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_151] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:888) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.0.RELEASE.jar:5.2.0.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.0.RELEASE.jar:5.2.0.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1579) [tomcat-embed-core-9.0.27.jar:9.0.27] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.27.jar:9.0.27] at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.8.0_151] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.8.0_151] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.27.jar:9.0.27] at java.lang.Thread.run(Unknown Source) [na:1.8.0_151]

I have followed the steps specified in Snowflake documentation : https://docs.snowflake.net/manuals/user-guide/jdbc.html

I am using the RSA key pair to authenticate with Snowflake as specified in the document. I have also mapped the public key to the Snowflake user that I am using, still facing this issue. I am doubtful if the key decryption is the problem.

Following is the code that I am using to connect to Snowflake:

public class TestSnowflakeConnection {

        public static void main(String[] args)
            throws Exception
        {
          DriverManager.registerDriver(new net.snowflake.client.jdbc.SnowflakeDriver());    

          File f = new File("D:\\....\\Auth-Keys\\rsa_key.p8");
          FileInputStream fis = new FileInputStream(f);
          DataInputStream dis = new DataInputStream(fis);
          byte[] keyBytes = new byte[(int) f.length()];
          dis.readFully(keyBytes);
          dis.close();

          String encrypted = new String(keyBytes);
          String passphrase = System.getenv("----");
          encrypted = encrypted.replace("-----BEGIN ENCRYPTED PRIVATE KEY-----", "");
          encrypted = encrypted.replace("-----END ENCRYPTED PRIVATE KEY-----", "");
          EncryptedPrivateKeyInfo pkInfo = new EncryptedPrivateKeyInfo(Base64.getMimeDecoder().decode(encrypted));
          PBEKeySpec keySpec = new PBEKeySpec(passphrase.toCharArray());
          SecretKeyFactory pbeKeyFactory = SecretKeyFactory.getInstance(pkInfo.getAlgName());
          PKCS8EncodedKeySpec encodedKeySpec = pkInfo.getKeySpec(pbeKeyFactory.generateSecret(keySpec));
          KeyFactory keyFactory = KeyFactory.getInstance("RSA");
          PrivateKey encryptedPrivateKey = keyFactory.generatePrivate(encodedKeySpec);

          String url = "jdbc:snowflake://<account>.snowflakecomputing.com";
          Properties prop = new Properties();
          prop.put("user", "username");
          prop.put("account", "xxx.xxx.xx-southeast-1.aws");
          prop.put("privateKey", encryptedPrivateKey);


          Connection conn = DriverManager.getConnection(url, prop);
          Statement stat = conn.createStatement();
          ResultSet res = stat.executeQuery("select 1");
          res.next();
          System.out.println(res.getString(1));
          conn.close();

        }
        }

Any help would be highly appreciated!!!!!

Upvotes: 0

Views: 1688

Answers (2)

Ankur Srivastava
Ankur Srivastava

Reputation: 923

Here is the working copy of the code .

--------------------------------------------------------------------------------

import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.security.KeyFactory;
import java.security.PrivateKey;
import java.security.spec.PKCS8EncodedKeySpec;
import java.time.Instant;
import java.util.Base64;
import java.util.Set;
import java.util.TreeSet;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

import javax.crypto.EncryptedPrivateKeyInfo;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.PBEKeySpec;

import net.snowflake.ingest.SimpleIngestManager;
import net.snowflake.ingest.connection.HistoryRangeResponse;
import net.snowflake.ingest.connection.HistoryResponse;

public class SDKTest {

    private static final String PRIVATE_KEY_FILE = "<Path to your p8 file>/rsa_key.p8";

    public static class PrivateKeyReader {
        public static PrivateKey get(String filename) throws Exception {
            File f = new File(filename);
            FileInputStream fis = new FileInputStream(f);
            DataInputStream dis = new DataInputStream(fis);
            byte[] keyBytes = new byte[(int) f.length()];
            dis.readFully(keyBytes);
            dis.close();

            String encrypted = new String(keyBytes);
            String passphrase = <YOUR_PASSPHRASE>;
            encrypted = encrypted.replace("-----BEGIN ENCRYPTED PRIVATE KEY-----", "");
            encrypted = encrypted.replace("-----END ENCRYPTED PRIVATE KEY-----", "");
            EncryptedPrivateKeyInfo pkInfo = new EncryptedPrivateKeyInfo(Base64.getMimeDecoder().decode(encrypted));
            PBEKeySpec keySpec = new PBEKeySpec(passphrase.toCharArray());
            SecretKeyFactory pbeKeyFactory = SecretKeyFactory.getInstance(pkInfo.getAlgName());
            PKCS8EncodedKeySpec encodedKeySpec = pkInfo.getKeySpec(pbeKeyFactory.generateSecret(keySpec));
            KeyFactory keyFactory = KeyFactory.getInstance("RSA");
            PrivateKey encryptedPrivateKey = keyFactory.generatePrivate(encodedKeySpec);
            return encryptedPrivateKey;
        }
    }



    private static HistoryResponse waitForFilesHistory(final SimpleIngestManager manager, Set<String> files)
            throws Exception {
        ExecutorService service = Executors.newSingleThreadExecutor();

        class GetHistory implements Callable<HistoryResponse> {
            private Set<String> filesWatchList;

            GetHistory(Set<String> files) {
                this.filesWatchList = files;
            }

            String beginMark = null;

            public HistoryResponse call() throws Exception {
                HistoryResponse filesHistory = null;
                while (true) {
                    Thread.sleep(5000);
                    HistoryResponse response = manager.getHistory(null, null, beginMark);
                    if (response.getNextBeginMark() != null) {
                        beginMark = response.getNextBeginMark();
                    }
                    if (response != null && response.files != null) {
                        for (HistoryResponse.FileEntry entry : response.files) {
                            // if we have a complete file that we've
                            // loaded with the same name..
                            String filename = entry.getPath();
                            if (entry.getPath() != null && entry.isComplete() && filesWatchList.contains(filename)) {
                                if (filesHistory == null) {
                                    filesHistory = new HistoryResponse();
                                    filesHistory.setPipe(response.getPipe());
                                }
                                filesHistory.files.add(entry);
                                filesWatchList.remove(filename);
                                // we can return true!
                                if (filesWatchList.isEmpty()) {
                                    return filesHistory;
                                }
                            }
                        }
                    }
                }
            }
        }

        GetHistory historyCaller = new GetHistory(files);
        // fork off waiting for a load to the service
        Future<HistoryResponse> result = service.submit(historyCaller);

        HistoryResponse response = result.get(2, TimeUnit.MINUTES);
        return response;
    }

    public static void main(String[] args) {
        final String account = "<Snowflake Account Name >";
        final String hostName = "<Snowflake Account URL>";
        final String user = "<Snowflake Account Username>";
        final String pipe = "<Snowflake Pipe Path>";
        try {
            final long oneHourMillis = 1000 * 3600L;
            String startTime = Instant.ofEpochMilli(System.currentTimeMillis() - 4 * oneHourMillis).toString();
            PrivateKey privateKey = PrivateKeyReader.get(PRIVATE_KEY_FILE);
            //PublicKey publicKey =  PublicKeyReader.get(PUBLIC_KEY_FILE);

            @SuppressWarnings("deprecation")
            SimpleIngestManager manager = new SimpleIngestManager(account, user, pipe, privateKey, "https", hostName, 443);



            Set<String> files = new TreeSet<>();
            files.add("<filename>");

            manager.ingestFiles(manager.wrapFilepaths(files), null);
            HistoryResponse history = waitForFilesHistory(manager, files);
            System.out.println("Received history response: " + history.toString());
            String endTime = Instant.ofEpochMilli(System.currentTimeMillis()).toString();

            HistoryRangeResponse historyRangeResponse = manager.getHistoryRange(null, startTime, endTime);
            System.out.println("Received history range response: " + historyRangeResponse.toString());

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}
-----------------------------------------------------------------------------

Upvotes: 0

Simon D
Simon D

Reputation: 6279

A couple of questions:

  • When you created the private key (the one you keep on the client machine) did you encrypt it with a passphrase?
  • When you assigned the public key to the Snowflake user with alter user... command did you make sure to exclude the -----BEGIN ENCRYPTED PRIVATE KEY----- and -----END ENCRYPTED PRIVATE KEY-----?

If you did encrypt the private key with a passphrase then you need to make sure you put the passphrase in an environment variable called PRIVATE_KEY_PASSPHRASE if you are copying the java code from the Snowflake documentation.

In your code you have this:

String passphrase = System.getenv("----");

This is looking for the passphrase for the encrypted private key in a environment variable called ---- which doesn't seem right?

Upvotes: 1

Related Questions