Solved

Integrated Login for database

  • 7 January 2022
  • 4 replies
  • 224 views
A
Badge +1
  • Akash
  • 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.

 

icon

Best answer by Maksim Zhelyazkov 11 January 2022, 16:30

View original

4 replies

M
Rank
Userlevel 2
Badge +4

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

maykwok_hamilton
Userlevel 2
Badge +2

@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? 

maykwok_hamilton
Userlevel 2
Badge +2

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
Rank
Userlevel 6
Badge +7

Thanks @maykwok_hamilton for sharing the solution here 🙌🏻

Check out Our Community Recommended Journey to get started on the community 🙋‍♀️

Reply


Terms & ConditionsCookie settings