Sql Server database connection

[AndrewF1ynn (Andrew Flynn)]

Must-share information (formatted with Markdown):

• Wrapper (Version 3.2.3)
• Instalation with SQL Server database
• Tried follwoing instructions, the command windows becomes unresponsive and the sonar server cannot be reached in the web browser

C:\SonarQube\bin\windows-x86-64>startsonar.bat
wrapper | → Wrapper Started as Console
wrapper | Launching a JVM…
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | 2021.02.17 15:26:48 INFO app[o.s.a.AppFileSystem] Cleaning or creating temp directory C:\SonarQube\temp
jvm 1 | 2021.02.17 15:26:48 INFO app[o.s.a.es.EsSettings] Elasticsearch listening on [HTTP: 127.0.0.1:9001, TCP: 127.0.0.1:49723]
jvm 1 | 2021.02.17 15:26:49 INFO app[o.s.a.ProcessLauncherImpl] Launch process[[key=‘es’, ipcIndex=1, logFilenamePrefix=es]] from [C:\SonarQube\elasticsearch]: C:\Program Files\Java\jdk-11.0.2\bin\java -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -Djava.io.tmpdir=C:\SonarQube\temp -XX:ErrorFile=…/logs/es_hs_err_pid%p.log -Des.networkaddress.cache.ttl=60 -Des.networkaddress.cache.negative.ttl=10 -XX:+AlwaysPreTouch -Xss1m -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djna.nosys=true -XX:-OmitStackTraceInFastThrow -Dio.netty.noUnsafe=true -Dio.netty.noKeySetOptimization=true -Dio.netty.recycler.maxCapacityPerThread=0 -Dio.netty.allocator.numDirectArenas=0 -Dlog4j.shutdownHookEnabled=false -Dlog4j2.disable.jmx=true -Djava.locale.providers=COMPAT -Des.enforce.bootstrap.checks=true -Xmx512m -Xms512m -XX:MaxDirectMemorySize=256m -XX:+HeapDumpOnOutOfMemoryError -Delasticsearch -Des.path.home=C:\SonarQube\elasticsearch -Des.path.conf=C:\SonarQube\temp\conf\es -cp lib/* org.elasticsearch.bootstrap.Elasticsearch
jvm 1 | 2021.02.17 15:26:49 INFO app[o.s.a.SchedulerImpl] Waiting for Elasticsearch to be up and running
jvm 1 | Java HotSpot™ 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
jvm 1 | 2021.02.17 15:27:09 INFO app[o.s.a.SchedulerImpl] Process[es] is up
jvm 1 | 2021.02.17 15:27:09 INFO app[o.s.a.ProcessLauncherImpl] Launch process[[key=‘web’, ipcIndex=2, logFilenamePrefix=web]] from [C:\SonarQube]: C:\Program Files\Java\jdk-11.0.2\bin\java -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djava.io.tmpdir=C:\SonarQube\temp -XX:-OmitStackTraceInFastThrow --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED -Xmx512m -Xms128m -XX:+HeapDumpOnOutOfMemoryError -Dhttp.nonProxyHosts=localhost|127.|[::1] -cp ./lib/common/;C:\SonarQube\lib\jdbc\mssql\mssql-jdbc-7.4.1.jre11.jar org.sonar.server.app.WebServer C:\SonarQube\temp\sq-process16685572902874969561properties

[anon67236913]

Welcome

at first glance there’s nothing suspicious, all normal.
What’s your Sonarqube version, which edition ?
Out of the box http://localhost:9000 should bring up the Sonarqube login page.
We need to check the logs in $SONARQUBE_HOME\logs to see what’s going on.
Just attach the logs, don’t forget to sanitize sensitive contents.

Gilbert

[AndrewF1ynn (Andrew Flynn)]

logs.zip (13.0 KB)

Please see attached zip file

[anon67236913]

OK, web.log has

2021.02.17 11:52:46 ERROR web[o.s.s.p.PlatformImpl] Web server startup failed
java.lang.IllegalStateException: Fail to connect to database

…

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

…

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

You didn’t mention your Sonarqube version and edition (Community, Developer, Enterprise).
Did you change the builtin H2 database setting ?
If yes, what’s your jdbc connection string in SONARQUBE_HOME\conf\sonar.properties ?
Did you check common connection problems like firewall, proxies … etc. ?

Maybe it’s also related to a missing dll when using integrated authentication for jdbc connection !?
e.g. noticed this yesterday when updating from Sonarqube 8.6.0 to 8.7, i had to upgrade to a recent version of this dll to make it work.
Try setting loglevel=DEBUG in SONARQUBE_HOME\conf\sonar.properties and restart to get
more infos. Perhaps you’ll get similar log entries if those are only seen in debug level, as i use always
loglevel debug in test.

[AndrewF1ynn (Andrew Flynn)]

Hi Thank you for your reply.

I’m using Sonar Community edition. Windows Server 2019, I have disabled the windows firewall while trying to resolve this issue.

I’ve attached the debug logs.logs (2).zip (20.2 KB)

[anon67236913]

Hi,

seems you have changed the jdbc connection string in the meantime.
The first error was:

2021.02.17 12:54:43 INFO web[o.sonar.db.Database] Create JDBC data source for jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true
2021.02.17 12:54:57 ERROR web[o.s.s.p.PlatformImpl] Web server startup failed
java.lang.IllegalStateException: Fail to connect to database

…

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”.

but no entry about ‘no suitable driver’. Maybe it’s the wrong port, AFAIK 1433 is the default port and it’s
recommended to change that. Maybe you’re not authorized for DB access ?

The most recent error has another jdbc connection string and the ‘no suitable driver’ error message

Caused by: java.sql.SQLException: Cannot create JDBC driver of class 'com.microsoft.sqlserver.jdbc.SQLServerDriver' for connect URL 'jdbc:sqlserver:\trg-devops-win-SQLEXPRESS;databaseName=sonar;integratedSecurity=true'
	at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:75)
	at org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:472)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:538)
	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: java.sql.SQLException: No suitable driver
	at org.apache.commons.dbcp2.DriverFactory.createDriver(DriverFactory.java:68)
	... 31 common frames omitted

Also not sure if Sonarqube supports MSSQL Express database !?

Gilbert

[AndrewF1ynn (Andrew Flynn)]

documentation says MS SQL is compatible and I’ve followed the instructions.

[Colin]

Please make sure your JDBC URL is correct.

@anon67236913

jdbc:sqlserver:\trg-devops-win-SQLEXPRESS;databaseName=sonar;integratedSecurity=true

The proper format is like below:

jdbc:sqlserver://localhost;databaseName=sonar;integratedSecurity=true

[anon67236913]

@Colin is right, good catch, missed that at first glance.
Your logs show two different jdbc connection strings, when =
the first one has the right syntax, but either the wrong port, missing permissions or general network problems.
the second one has the wrong syntax and no suitable driver,
see Building the connection URL - SQL Server | Microsoft Docs