Skip to main content
Solved

Integrated Login for database

A
Universe Traveller
Forum|alt.badge.img+2
  • AkashUniverse Traveller
  • Universe Traveller
  • 3 replies

Hi Team,

In Ataccama IDE, while connecting to database we need to provide connection string. Also, username and password. In other tools like SQL server, we can use windows authentication to login so no need to enter username and password.

Could you please help me how we can achieve the same integrated login for database connection.

 

Best answer by Maksim Zhelyazkov

Hello Akash,

You can also use Windows Authentication for MSSQL inside of the Ataccama IDE. But you will still need to provide the windows username and password for your user. How that works depends on the OS of your IDE. 

  •  For Windows, you have to first paste the auth.dll from [Build]runtime\lib\jdbc\mssql\auth to [Build]/jre/bin. Then, in the IDE type in the standard jdbc string, but with ;integratedsecurity=true parameter in the end. In Username you provide your windows username like you do in SQL server, in password you type your windows password;

  • For Linux it is more complicated as Linux cannot use Windows authentication out of the box. You would need to configure kerberos authentication on your Linux server. This is not Ataccama related configuration, so you will have to find a guide online.
    After setting up kerberos, you need the pass the krb.conf location to the one-desktop.ini file located in the Build directory. You can do it by adding the following parameter in the .ini: 
    -Djava.security.krb5.conf=/home/krb.conf .
    Finally, pass three parameters in the connection string of the IDE, instead of the one in Windows - integratedsecurity=true;authenticationScheme=JavaKerberos;serverSpn=(you can get the SPN by running in the terminal: klist -c krb5cc). In username you would use username@domainname and again your windows password in the password field. 


    Regards,
    Maksim

View original
    Did this topic help you find an answer to your question?

    4 replies

    M
    Ataccamer
    Forum|alt.badge.img+2

    Hello Akash,

    You can also use Windows Authentication for MSSQL inside of the Ataccama IDE. But you will still need to provide the windows username and password for your user. How that works depends on the OS of your IDE. 

    •  For Windows, you have to first paste the auth.dll from [Build]runtime\lib\jdbc\mssql\auth to [Build]/jre/bin. Then, in the IDE type in the standard jdbc string, but with ;integratedsecurity=true parameter in the end. In Username you provide your windows username like you do in SQL server, in password you type your windows password;

    • For Linux it is more complicated as Linux cannot use Windows authentication out of the box. You would need to configure kerberos authentication on your Linux server. This is not Ataccama related configuration, so you will have to find a guide online.
      After setting up kerberos, you need the pass the krb.conf location to the one-desktop.ini file located in the Build directory. You can do it by adding the following parameter in the .ini: 
      -Djava.security.krb5.conf=/home/krb.conf .
      Finally, pass three parameters in the connection string of the IDE, instead of the one in Windows - integratedsecurity=true;authenticationScheme=JavaKerberos;serverSpn=(you can get the SPN by running in the terminal: klist -c krb5cc). In username you would use username@domainname and again your windows password in the password field. 


      Regards,
      Maksim

    may_kwok
    Star Blazer L3
    Forum|alt.badge.img+4
    • may_kwokStar Blazer L3
    • Star Blazer L3
    • 82 replies
    • November 23, 2023

    @Maksim Zhelyazkov a follow on question on this please.

    I’m on 14.2.

    I’m trying this on windows and I have pasted the mssql-jdbc_auth-9.2.1.x64.dll file into build/jre/bin, and then I have also added the ;integratedSecurity=true tag to the end of the jdbc string. But I am getting this when I tried to test connection:

     

    com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:cd70c361-a0f7-4ebc-bbbc-dc25344cb470
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3680)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2047)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3204)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936)
	at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:168)
	at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:77)
	at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:43)
	at com.ataccama.dqc.commons.sql.util.SimpleDataSource.getConnection(SimpleDataSource.java:67)
	at com.ataccama.dqc.internal.commons.sql.AtcDataSource.getConnection(AtcDataSource.java:38)
	at com.ataccama.dqc.commons.sql.DataSourceImpl.getRawConnection(DataSourceImpl.java:105)
	at com.ataccama.dqc.commons.sql.DataSourceImpl.getNonTransactionalConnection(DataSourceImpl.java:73)
	at com.ataccama.dqc.gui.ui.wizards.database.DatabaseConnectionModel.testConnection(DatabaseConnectionModel.java:340)
	at com.ataccama.dqc.gui.ui.wizards.database.NewDatabasePage$2.run(NewDatabasePage.java:264)
	at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:122)
Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131)
	at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:353)
	at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:296)
	at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:291)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:654)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:473)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:369)
	at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:392)
	at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:443)
	at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:421)
	at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:183)
	at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:172)
	at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1506)
	at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1416)
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:456)
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:427)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1955)
	... 15 more
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:439)
	at java.base/sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:306)
	at java.base/sun.security.validator.Validator.validate(Validator.java:264)
	at java.base/sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:313)
	at java.base/sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:233)
	at java.base/sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:110)
	at com.microsoft.sqlserver.jdbc.TDSChannel$HostNameOverrideX509TrustManager.checkServerTrusted(IOBuffer.java:1636)
	at java.base/sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1510)
	at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:638)
	... 27 more
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
	at java.base/sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)
	at java.base/sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)
	at java.base/java.security.cert.CertPathBuilder.build(CertPathBuilder.java:297)
	at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:434)
	... 35 more

     

    Do I need some other certificate from somewhere? Or am I missing something in the onedesktop.ini file? 

    may_kwok
    Star Blazer L3
    Forum|alt.badge.img+4
    • may_kwokStar Blazer L3
    • Star Blazer L3
    • 82 replies
    • November 24, 2023

    Thanks to @chandankaushal from the Support team we have figured out what we needed.

    In v14.2, I needed to update MSSQL driver to version 12.4.2 (the latest one available as of now), and then the jdbc string I used was:

    jdbc:sqlserver://[serverName]:1433;databaseName=[databaseName];integratedSecurity=true;trustServerCertificate=true

    I needed to use both the integratedSecurity and trustServerCertificate properties.

    My main driver jar is mssql-jdbc-12.4.1.jre8.jar and my auth dll is mssql-jdbc_auth-12.4.1.x64.dll

    I don’t even need to enter my windows creds into the fields, the connection works without entering those details.

    Cansu
    1 person likes this
    • Cansu
      Cansu
    Cansu
    Community Manager
    Forum|alt.badge.img+3
    • CansuCommunity Manager
    • Community Manager
    • 625 replies
    • November 27, 2023

    Thanks @maykwok_hamilton for sharing the solution here 🙌🏻

    Check out our Quick Start Guide to get started on the community 🙋‍♀️

    Reply


    Most Liked this week

    Community Terms of Use & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings