SonarQube Connection to SQL Database using Windows Authentication

MadhuTeli · December 11, 2018, 4:05pm

Hi,

SonarQube 7.3 is installed on Windows machine. SonarQube server is connected to Microsoft SQL database (using SQL Authentication) and therefore, $SONARQUBE-HOME/conf/sonar.properties is configured as below:

sonar.jdbc.username=sonarqube
sonar.jdbc.password=mypassword
sonar.jdbc.url=jdbc:sqlserver://hostname;databaseName=DatabaseName;instanceName=InstanceName

I don’t want to use ‘SQL Authentication’ and use ‘Windows Authentication’ instead. How can i achieve this?

From online resources, i found out that jTDS Driver supports Windows authentication to SQL database.

I downloaded jTDS - SQL Server and Sybase JDBC Driver and copied “jtds-1.3.1.jar” file to
Sonarqube 7.3\extensions\jdbc-driver\mssql" directory (created ‘mssql’ directory manually).

Commented sonar.jdbc.username and sonar.jdbc.password properties.

sonar.jdbc.url=jdbc:sqlserver://hostname;integratedSecurity=true;databaseName=DatabaseName;instanceName=InstanceName

When i restarted the sonarqube instance, it’s getting errored out. Is this the right way? If not, how can i achieve windows authentication to SQL database?

Thanks,
Madhu

Colin · December 11, 2018, 4:11pm

Madhu,

Let me recommend that you read through our "Installing the Database" documentation, specifically the section on Microsoft SQL Server.

Colin

MadhuTeli · January 24, 2019, 9:34am

Hi Colin,

Thank you. Successfully implemented Windows authentication. Could you please guide me on how to use SSO with crowd?

Thanks,
Madhu

halobolah · October 3, 2019, 11:57am

Hi, can you tell me how did you configure this windows authentication ?? I have a problem with this.

I’ve got errors:

Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path: [/usr/java/packages/lib, /usr/lib64, /lib64, /lib, /usr/lib]

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.

Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication.

Caused by: java.lang.IllegalStateException: Can not connect to database. Please check connectivity and settings (see the properties prefixed by ‘sonar.jdbc.’)

My connection string looks like:

sonar.jdbc.url=jdbc:sqlserver://IP;databaseName=sonar;integratedSecurity=true

user and password - commented

I don’t know what im doing wrong. SQL Auth is working fine

khopithan · January 29, 2021, 5:10am

Hi Colin,

Could you please tell that the meaning of this “folder in your path”. I did not get which path they are mentioning. SonarQube home path or ?

Regards,
Khopi

j4v13r · April 28, 2021, 6:14pm

Hi,
this the same that me question. Any recommended.

anon67236913 · April 29, 2021, 5:35am

Hi Khopi,

path means %PATH%, see contents with cmd > echo %PATH%
In case of doubt put the dll into \bin folder of the java installation Sonarqube runs on.

Gilbert

sgupta · February 16, 2022, 6:41am

Hi @anon67236913

I copied the dll in java bin folder but still getting the same error. It is openjdk 11.

Please note that I am running SonarQube in Docker container and SQL server is outside the container on the host machine. I am able to connect to SQL server with username and password but it fails when trying to use windows authentication.

Any help would be great.

Thanks!

anon67236913 · February 16, 2022, 9:17am

Hi,

i’m no expert for running Sonarqube in Docker, have not yet tried it.
What is your Sonarqube and MSSQL version ?
What is the concrete error message ?
Do you get a java.lang.UnsatisfiedLinkError ?

Gilbert

sgupta · February 16, 2022, 4:54pm

SonarQube is 8.9.7 and SQL Server 2019.

Here is the error

2022.02.16 07:00:50 INFO  web[][o.s.p.ProcessEntryPoint] Starting web
2022.02.16 07:00:51 INFO  web[][o.a.t.u.n.NioSelectorPool] Using a shared selector for servlet write/read
2022.02.16 07:00:52 INFO  web[][o.s.s.e.EsClientProvider] Connected to local Elasticsearch: [http://localhost:9001]
2022.02.16 07:00:52 INFO  web[][o.s.s.p.LogServerVersion] SonarQube Server / 8.9.7.52159 / 1728baf4ab32b2357b8f85a8125c27d0faf6e4c2
2022.02.16 07:00:52 INFO  web[][o.sonar.db.Database] Create JDBC data source for jdbc:sqlserver://host.docker.internal;databaseName=sonar;integratedSecurity=true;
2022.02.16 07:01:07 ERROR web[][o.s.s.p.PlatformImpl] Web server startup failed
java.lang.IllegalStateException: Fail to connect to database
	at org.sonar.db.DefaultDatabase.start(DefaultDatabase.java:86)
	at org.sonar.core.platform.StartableCloseableSafeLifecyleStrategy.start(StartableCloseableSafeLifecyleStrategy.java:40)
	at org.picocontainer.injectors.AbstractInjectionFactory$LifecycleAdapter.start(AbstractInjectionFactory.java:84)
	at org.picocontainer.behaviors.AbstractBehavior.start(AbstractBehavior.java:169)
	at org.picocontainer.behaviors.Stored$RealComponentLifecycle.start(Stored.java:132)
	at org.picocontainer.behaviors.Stored.start(Stored.java:110)
	at org.picocontainer.DefaultPicoContainer.potentiallyStartAdapter(DefaultPicoContainer.java:1016)
	at org.picocontainer.DefaultPicoContainer.startAdapters(DefaultPicoContainer.java:1009)
	at org.picocontainer.DefaultPicoContainer.start(DefaultPicoContainer.java:767)
	at org.sonar.core.platform.ComponentContainer.startComponents(ComponentContainer.java:136)
	at org.sonar.server.platform.platformlevel.PlatformLevel.start(PlatformLevel.java:90)
	at org.sonar.server.platform.platformlevel.PlatformLevel1.start(PlatformLevel1.java:166)
	at org.sonar.server.platform.PlatformImpl.start(PlatformImpl.java:213)
	at org.sonar.server.platform.PlatformImpl.startLevel1Container(PlatformImpl.java:172)
	at org.sonar.server.platform.PlatformImpl.init(PlatformImpl.java:86)
	at org.sonar.server.platform.web.PlatformServletContextListener.contextInitialized(PlatformServletContextListener.java:43)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4763)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5232)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1429)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1419)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.IllegalStateException: Can not connect to database. Please check connectivity and settings (see the properties prefixed by 'sonar.jdbc.').
	at org.sonar.db.DefaultDatabase.checkConnection(DefaultDatabase.java:118)
	at org.sonar.db.DefaultDatabase.start(DefaultDatabase.java:83)
	... 24 common frames omitted
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication. ClientConnectionId:56d90df2-60df-4c37-99e8-6820c4ab77ed)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544)
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753)
	at org.sonar.db.profiling.NullConnectionInterceptor.getConnection(NullConnectionInterceptor.java:31)
	at org.sonar.db.profiling.ProfiledDataSource.getConnection(ProfiledDataSource.java:317)
	at org.sonar.db.DefaultDatabase.checkConnection(DefaultDatabase.java:115)
	... 25 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:56d90df2-60df-4c37-99e8-6820c4ab77ed
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3208)
	at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<init>(AuthenticationJNI.java:72)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4017)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4006)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2768)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2418)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2265)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1291)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:881)
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55)
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:115)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:665)
	... 30 common frames omitted
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-9.2.0.x64 in java.library.path: [/usr/java/packages/lib, /usr/lib64, /lib64, /lib, /usr/lib]
	at java.base/java.lang.ClassLoader.loadLibrary(Unknown Source)
	at java.base/java.lang.Runtime.loadLibrary0(Unknown Source)
	at java.base/java.lang.System.loadLibrary(Unknown Source)
	at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<clinit>(AuthenticationJNI.java:51)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4016)
	... 42 common frames omitted
2022.02.16 07:01:07 WARN  web[][o.a.c.l.WebappClassLoaderBase] The web application [ROOT] appears to have started a thread named [pool-1-thread-1] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:\n java.base@11.0.13/sun.nio.ch.EPoll.wait(Native Method)\n java.base@11.0.13/sun.nio.ch.EPollSelectorImpl.doSelect(Unknown Source)\n java.base@11.0.13/sun.nio.ch.SelectorImpl.lockAndDoSelect(Unknown Source)\n java.base@11.0.13/sun.nio.ch.SelectorImpl.select(Unknown Source)\n app//org.apache.http.impl.nio.reactor.AbstractMultiworkerIOReactor.execute(AbstractMultiworkerIOReactor.java:343)\n app//org.apache.http.impl.nio.conn.PoolingNHttpClientConnectionManager.execute(PoolingNHttpClientConnectionManager.java:221)\n app//org.apache.http.impl.nio.client.CloseableHttpAsyncClientBase$1.run(CloseableHttpAsyncClientBase.java:64)\n java.base@11.0.13/java.lang.Thread.run(Unknown Source)
2022.02.16 07:01:07 WARN  web[][o.a.c.l.WebappClassLoaderBase] The web application [ROOT] appears to have started a thread named [I/O dispatcher 1] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:\n java.base@11.0.13/sun.nio.ch.EPoll.wait(Native Method)\n java.base@11.0.13/sun.nio.ch.EPollSelectorImpl.doSelect(Unknown Source)\n java.base@11.0.13/sun.nio.ch.SelectorImpl.lockAndDoSelect(Unknown Source)\n java.base@11.0.13/sun.nio.ch.SelectorImpl.select(Unknown Source)\n app//org.apache.http.impl.nio.reactor.AbstractIOReactor.execute(AbstractIOReactor.java:255)\n app//org.apache.http.impl.nio.reactor.BaseIOReactor.execute(BaseIOReactor.java:104)\n app//org.apache.http.impl.nio.reactor.AbstractMultiworkerIOReactor$Worker.run(AbstractMultiworkerIOReactor.java:591)\n java.base@11.0.13/java.lang.Thread.run(Unknown Source)
2022.02.16 07:01:07 WARN  web[][o.a.c.l.WebappClassLoaderBase] The web application [ROOT] appears to have started a thread named [I/O dispatcher 2] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:\n java.base@11.0.13/sun.nio.ch.EPoll.wait(Native Method)\n java.base@11.0.13/sun.nio.ch.EPollSelectorImpl.doSelect(Unknown Source)\n java.base@11.0.13/sun.nio.ch.SelectorImpl.lockAndDoSelect(Unknown Source)\n java.base@11.0.13/sun.nio.ch.SelectorImpl.select(Unknown Source)\n app//org.apache.http.impl.nio.reactor.AbstractIOReactor.execute(AbstractIOReactor.java:255)\n app//org.apache.http.impl.nio.reactor.BaseIOReactor.execute(BaseIOReactor.java:104)\n app//org.apache.http.impl.nio.reactor.AbstractMultiworkerIOReactor$Worker.run(AbstractMultiworkerIOReactor.java:591)\n java.base@11.0.13/java.lang.Thread.run(Unknown Source)
2022.02.16 07:01:07 INFO  web[][o.s.s.a.EmbeddedTomcat] HTTP connector enabled on port 9000
2022.02.16 07:01:07 INFO  web[][o.s.p.ProcessEntryPoint] Hard stopping process

anon67236913 · February 16, 2022, 5:02pm

It seems you’re using the wrong version of the dll, Sonarqube 8.9.7 ships with
mssql-jdbc-9.2.0.jre11.jar, get it here

and put it on path

sgupta · February 17, 2022, 3:33am

@anon67236913 - Do you mean that I should not be using 9.2.0 and instead use 9.2.1 jar?

I tried copying 9.2.0 dll in all the folders as mentioned in the java.library.path but nothing worked.

anon67236913 · February 17, 2022, 6:59am

No, mssql-jdbc_auth-9.2.0.x64.dll is the right one.
Thought it might be because of using the wrong version.
Did you already try with copy the dll to $JAVA_HOME\bin (the java version Sonarqube runs on) ?

sgupta · February 17, 2022, 7:00am

I tried copying the dll to /opt/java/openjdk/bin but that didn’t work either.

anon67236913 · February 17, 2022, 8:20am

Seems you can’t use integrated authentication for MSSQL in a linux container, see

so you should stick with user and password.