First time setup, need MSSQL help please

Hi there.

I’m installing SonarQube 8.7 onto a Windows Server 2019 server in Azure and am trying to simply get the MSSQL database built out. I’m running MSSQL 2019 Express. My problem is that when I start StartSonar.bat for the first time, it never adds the database schema to my database. The console doesn’t seem to have any errors either.

I made a blank database called “sonar” and create a user, “sonarqube”, and given it db_owner permissions over the sonar database. I also made sure both the collation and READ_COMMITED_SNAPSHOT settings are proper.

In my server.properties file, I have the following connection strings…

sonar.jdbc.url=jdbc:sqlserver://VF-APPS-01\SONARQUBE;databaseName=sonar
sonar.jdbc.username=sonarqube
sonar.jdbc.password=mypassword

I’ve confirmed the user can open the database perfectly fine so it’s not a permission issue. I run the .bat file as admin so not that. I have tried using “VF-APPS-01/SONARQUBE” or “VF-APPS-01\SONARQUBE”, nothing seems to work.

My console just sits and looks like this…

Any help would be very much appreciated.

Steven

Hi,

Welcome to the community!

What do your server logs say?

 
Ann

Are you referring to Event Viewer or another log location? I don’t see much of anything in Event Viewer. I did find another log file named “sonar.20210318.log” at …\SonarQube\logs. It seems the log repeat themselves a bit to hit the max 25kb file size.

Blockquote
2021.03.18 16:53:16 INFO app[o.s.a.AppFileSystem] Cleaning or creating temp directory E:\SonarQube\temp
2021.03.18 16:53:16 INFO app[o.s.a.es.EsSettings] Elasticsearch listening on [HTTP: 127.0.0.1:9001, TCP: 127.0.0.1:53072]
2021.03.18 16:53:16 INFO app[o.s.a.ProcessLauncherImpl] Launch process[[key=‘es’, ipcIndex=1, logFilenamePrefix=es]] from [E:\SonarQube\elasticsearch]: C:\Program Files\AdoptOpenJDK\jre-11.0.10.9-hotspot\bin\java -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -Djava.io.tmpdir=E:\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=E:\SonarQube\elasticsearch -Des.path.conf=E:\SonarQube\temp\conf\es -cp lib/* org.elasticsearch.bootstrap.Elasticsearch
2021.03.18 16:53:16 INFO app[o.s.a.SchedulerImpl] Waiting for Elasticsearch to be up and running
OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
2021.03.18 16:53:17 INFO app[o.s.a.SchedulerImpl] Process[es] is up
2021.03.18 16:53:17 INFO app[o.s.a.ProcessLauncherImpl] Launch process[[key=‘web’, ipcIndex=2, logFilenamePrefix=web]] from [E:\SonarQube]: C:\Program Files\AdoptOpenJDK\jre-11.0.10.9-hotspot\bin\java -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djava.io.tmpdir=E:\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 -Xmx1024m -Xms512m -XX:+HeapDumpOnOutOfMemoryError -Dhttp.nonProxyHosts=localhost|127.|[::1] -cp ./lib/common/;E:\SonarQube\lib\jdbc\mssql\mssql-jdbc-9.2.0.jre11.jar org.sonar.server.app.WebServer E:\SonarQube\temp\sq-process6512856684629995571properties
Machine is shutting down.
2021.03.18 16:53:59 WARN app[o.s.a.p.AbstractManagedProcess] Process exited with exit value [es]: 143
2021.03.18 16:53:59 INFO app[o.s.a.SchedulerImpl] Process[es] is stopped
2021.03.18 16:53:59 WARN app[o.s.a.p.AbstractManagedProcess] Process exited with exit value [web]: 143
2021.03.18 16:53:59 INFO app[o.s.a.SchedulerImpl] Process[web] is stopped
2021.03.18 16:53:59 INFO app[o.s.a.SchedulerImpl] SonarQube is stopped
← Wrapper Stopped
→ Wrapper Started as Service
Launching a JVM…
Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
2021.03.18 16:54:35 INFO app[o.s.a.AppFileSystem] Cleaning or creating temp directory E:\SonarQube\temp
2021.03.18 16:54:35 INFO app[o.s.a.es.EsSettings] Elasticsearch listening on [HTTP: 127.0.0.1:9001, TCP: 127.0.0.1:49731]
2021.03.18 16:54:35 INFO app[o.s.a.ProcessLauncherImpl] Launch process[[key=‘es’, ipcIndex=1, logFilenamePrefix=es]] from [E:\SonarQube\elasticsearch]: C:\Program Files\AdoptOpenJDK\jre-11.0.10.9-hotspot\bin\java -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -Djava.io.tmpdir=E:\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=E:\SonarQube\elasticsearch -Des.path.conf=E:\SonarQube\temp\conf\es -cp lib/* org.elasticsearch.bootstrap.Elasticsearch
2021.03.18 16:54:35 INFO app[o.s.a.SchedulerImpl] Waiting for Elasticsearch to be up and running
OpenJDK 64-Bit Server VM warning: Option UseConcMarkSweepGC was deprecated in version 9.0 and will likely be removed in a future release.
2021.03.18 16:54:58 INFO app[o.s.a.SchedulerImpl] Process[es] is up
2021.03.18 16:54:58 INFO app[o.s.a.ProcessLauncherImpl] Launch process[[key=‘web’, ipcIndex=2, logFilenamePrefix=web]] from [E:\SonarQube]: C:\Program Files\AdoptOpenJDK\jre-11.0.10.9-hotspot\bin\java -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djava.io.tmpdir=E:\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 -Xmx1024m -Xms512m -XX:+HeapDumpOnOutOfMemoryError -Dhttp.nonProxyHosts=localhost|127.|[::1] -cp ./lib/common/;E:\SonarQube\lib\jdbc\mssql\mssql-jdbc-9.2.0.jre11.jar org.sonar.server.app.WebServer E:\SonarQube\temp\sq-process16408894237817848428properties

I found the logs you’re looking for… my bad, still learning…

I’ve made sure my Windows Firewall is off. My connection string looks like this…

I’ve tried many variations of this, with and without integrated security, all have the same issue. Here’s my info…

Server = localhost, 10.0.10.6
SQL Instance = SONARQUBE
Database = sonar
TCP/IP Port = 1433

I’ve tried…

sonar.jdbc.url=jdbc:jtds:sqlserver://localhost/sonar;selectmethod=cursor;instance=SONARQUBE
sonar.jdbc.url=jdbc:jtds:sqlserver://localhost\SONARQUBE;databaseName=sonar
sonar.jdbc.url=jdbc:jtds:sqlserver://10.0.10.6\SONARQUBE;databaseName=sonar
sonar.jdbc.url=jdbc:jtds:sqlserver://localhost:1433;databaseName=sonar
…and more…

I’m completely lost as to why this will not connect.

Hi,

Can you check out this thread & see if it helps?

 
Ann

1 Like

Reading through that post, it talks about struggles with integrated security not working, but I can’t even get SQL authentication to work. I’ve tried both integrated and SQL and both fail with the same errors.

Here’s a copy of my logs. I don’t know how to paste the text here and make it easily readable and formatted well. You can see me trying various different connection strings with no luck.

sonarlogs.txt (42.7 KB)

To add to my last post, I also tried installing MSSQL Express 2017 to see if maybe the issue was with 2019 and that has the same issue. My next troubleshooting step is to try a lower version of SonarQube at this point. I don’t understand why such a basic connection is so difficult…

Hi,

Your screenshot of your JDBC URL includes ;integratedSecurity=true, which is why I thought that thread might help you.

Looking at your log files, I think the first thing to check is that your DB is up and available at the expected port:

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.

 
Ann

@StevenAtMainstay Try creating an empty text file on the Windows 2019 server and rename file extension to .udl. Follow the instructions in this article and use your SQL 2019/2017 Express settings to confirm connectivity.

If the UDL doesn’t connect, you have a connectivity issue between the Windows 2019 server and 2019/2017 Express box. Have you configured SQL Server Express to allow remote connections? If not, here is a Stack Overflow article describing the process.

If you have connectivity, please see the @ganncamp referenced above. I solved the issue by downloading the MSSQL JDBC 9.2.0 driver and copying the auth dll into the JDK /bin directory.

3 Likes

@jtbatzer @ganncamp Thank you both for your help so far, but still no luck. I’ve been able to confirm database connectivity using your suggested UDL test, both from localhost and from another server on the same network.

Here’s everything I’ve done…

  • I’ve placed the SQL JDBC auth .dll file in my JDK bin folder as seen below…
    image

  • I tried upgrading the mssql-jdbc-auth jar to 9.2.1. I also placed the x64 .dll file in the same directory. No change there.
    image

Am I not placing these files in the correct locations? Every single post or article I’ve seen never specifically says the directory the place files in. They just get generic or vague locations such as “in your JDK bin” or “in your directory”. To someone that’s never done this before, it’s very frustrating and not direct enough.

Here’s an updated log and copy of my server.properties files.

web.log (22.4 KB)
sonar.properties.txt (20.1 KB)

Again, thank you both so much for your help.

Hi,

you need mssql-jdbc_auth-9.2.0.x64.dll on path for Sonarqube 8.7
Just put in the jdk\bin folder and it should work. Did you try this already ?

Gilbert

Sorry for the vague response. I too have faced the same frustration when I can’t decipher when there are vague instructions.

I put my mssql-jdbc_auth-9.2.0.x64.dll file in my server’s Java JDK bin folder which was %ProgramFiles%\Java\jdk-12.0.1 and it worked fine for me.

Hope that helps

@Rebse @jtbatzer Thank you both for this clarification! I did this and it still didn’t work. According to SonarQube’s documentation, that .dll file is only needed if integrated security is being used, which I am not currently trying to use, however I did want to use this in the future, so thank you for helping me with that piece (more on that later).

Suspecting something is missing from SQ’s documentation, I looked up Microsoft’s article on JDBC connections (Building the connection URL - SQL Server | Microsoft Docs) and found “instanceName” was missing from their page: Install the Server | SonarQube Docs

Sure enough, I changed my server.properties from…

sonar.jdbc.url=jdbc:sqlserver://localhost:1433;databaseName=sonar

to…

sonar.jdbc.url=jdbc:sqlserver://localhost;instanceName=SONAR;databaseName=sonar

… it finally worked.

To prove that my server.properties change was the root of the issue all along, I removed the mssql…dll file from my Java bin folder and tried again and still got a successful connection. I then tried integrated security and it didn’t work without the .dll file, as expected. I re-added the .dll and tried agian with integrated security and it works now as well (thanks again for helping me with that piece).

Any SonarSource employees that see this, please add instanceName to the jdbc connection string documentation or at least have some examples of working connection strings so people can reference them. This should not have taken me this long to figure this out.

1 Like

Hi,

the jdbc connection string depends on the setup of your database.
i.e. we use MSSQL 2017 and my connection string looks like that
jdbc:sqlserver://SDBxxxx\MSSQLxx;databaseName=xxxx_SonarSource_xx;integratedSecurity=true

All the details here https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url

Gilbert

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.