Can SonarQube connect Azure SQL Database?

My SonarQube is running on VM instance of Microsoft Azure. Now the SonarQube's database is MySQL, and I'm trying to change MySQL to Azure SQL Database, but SonarQube couldn't connect Azure SQL Database by some erroes which occured at ActiveRecord component. I know SonarQube doesn't support Azure SQL Database officially and support SQL Server below, nevertheless Azure SQL Database is compatible with SQL Server, so It's may possible to connect Azure SQL Database. Please tell me how to connect Azure SQL Database if anybody knows.

SonarQube's Support Database

sonar.properties:

sonar.jdbc.username=sonar@<server>
sonar.jdbc.password=<password>
sonar.jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver
jdbc:jtds:sqlserver://<server>.database.windows.net:1433/<databasename>;ssl=require

error trace:

org.jruby.rack.RackInitializationException: no connection available
    from arjdbc/jdbc/RubyJdbcConnection.java:517:in `set_native_database_types'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection.rb:61:in `initialize'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/adapter.rb:31:in `initialize'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection_methods.rb:6:in `jdbc_connection'
    from org/jruby/RubyKernel.java:2227:in `send'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `new_connection'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in `checkout_new_connection'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in `checkout'
    from org/jruby/RubyKernel.java:1519:in `loop'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in `checkout'
    from jar:file:/opt/sonar/web/WEB-INF/lib/jruby-complete-1.7.9.jar!/META-INF/jruby.home/lib/ruby/1.8/monitor.rb:191:in `mon_synchronize'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in `checkout'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in `connection'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in `retrieve_connection'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in `retrieve_connection'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in `connection'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:440:in `initialize'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:401:in `up'
    from /opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:383:in `migrate'
    from /opt/sonar/web/WEB-INF/lib/database_version.rb:62:in `upgrade_and_start'
    from /opt/sonar/web/WEB-INF/lib/database_version.rb:74:in `automatic_setup'
    from /opt/sonar/web/WEB-INF/config/environment.rb:239:in `(root)'
    from org/jruby/RubyKernel.java:1094:in `load'
    from file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:1:in `(root)'
    from file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:25:in `load_environment'
    from file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails_booter.rb:79:in `load_environment'

    at org.jruby.rack.RackInitializationException.wrap(RackInitializationException.java:29)
    at org.jruby.rack.RackApplicationFactoryDecorator.init(RackApplicationFactoryDecorator.java:98)
    at org.jruby.rack.RackServletContextListener.contextInitialized(RackServletContextListener.java:50)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4939)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5434)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.jruby.exceptions.RaiseException: (ConnectionNotEstablished) no connection available
    at arjdbc.jdbc.RubyJdbcConnection.set_native_database_types(arjdbc/jdbc/RubyJdbcConnection.java:517)
    at RUBY.initialize(/opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection.rb:61)
    at RUBY.initialize(/opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/adapter.rb:31)
    at RUBY.jdbc_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-jdbc-adapter-1.1.3/lib/arjdbc/jdbc/connection_methods.rb:6)
    at org.jruby.RubyKernel.send(org/jruby/RubyKernel.java:2227)
    at RUBY.new_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:223)
    at RUBY.checkout_new_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:245)
    at RUBY.checkout(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:188)
    at org.jruby.RubyKernel.loop(org/jruby/RubyKernel.java:1519)
    at RUBY.checkout(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:184)
    at RUBY.mon_synchronize(jar:file:/opt/sonar/web/WEB-INF/lib/jruby-complete-1.7.9.jar!/META-INF/jruby.home/lib/ruby/1.8/monitor.rb:191)
    at RUBY.checkout(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:183)
    at RUBY.connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:98)
    at RUBY.retrieve_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_pool.rb:326)
    at RUBY.retrieve_connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:123)
    at RUBY.connection(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/connection_adapters/abstract/connection_specification.rb:115)
    at RUBY.initialize(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:440)
    at RUBY.up(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:401)
    at RUBY.migrate(/opt/sonar/web/WEB-INF/gems/gems/activerecord-2.3.15/lib/active_record/migration.rb:383)
    at RUBY.upgrade_and_start(/opt/sonar/web/WEB-INF/lib/database_version.rb:62)
    at RUBY.automatic_setup(/opt/sonar/web/WEB-INF/lib/database_version.rb:74)
    at RUBY.(root)(/opt/sonar/web/WEB-INF/config/environment.rb:239)
    at org.jruby.RubyKernel.load(org/jruby/RubyKernel.java:1094)
    at RUBY.(root)(file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:1)
    at RUBY.load_environment(file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails/environment2.rb:25)
    at RUBY.load_environment(file:/opt/sonar/web/WEB-INF/lib/jruby-rack-1.1.13.2.jar!/jruby/rack/rails_booter.rb:79)

Thanks

回答1:

It's possible, I just discovered, after spending 6 pretty frustrating hours on this issue. Here are the issues I encountered and the solutions I came up with for each:

Couldn't connect to the Azure SQL Server database -- connection would just time out. The solution was to add ";ssl=require" to the connection URL and -Djsse.enableCBCProtection=false (see Connecting to Azure SQL DB via JTDS on an Android device) to the server's java parameters (in conf/wrapper.conf). Here's what my database configuration looks like:
```
sonar.jdbc.username=<username>@SERVER
sonar.jdbc.password=<password>
sonar.jdbc.url=jdbc:jtds:sqlserver://SERVER.database.windows.net:1433/DATABASE;ssl=require;SelectMethod=Cursor
```
I also upgraded the jTDS driver in extensions/jdbc-driver/mssql from 1.2.8 to 1.3.1, but that might not be necessary.
SonarQube creates some tables without clustered indexes, which Azure SQL Server doesn't support (see Tables without a clustered index are not supported in this version of SQL Server). To solve that issue, I needed to install SonarQube locally using a local SQL Server Express instance. After doing that I ran the following DDL to convert the problematic tables to use clustered indexes:
```
CREATE UNIQUE CLUSTERED INDEX Idx_schema_migrations ON dbo.schema_migrations(version);
CREATE UNIQUE CLUSTERED INDEX Idx_groups_users ON dbo.groups_users(user_id,group_id);
```
Finally, now that the database is Azure-compatible, inside SSMS I right-clicked on the database, selected Tasks/Deploy Database to SQL Azure, and deployed it to my Azure server.
SonarQube's database upgrader doesn't support SQL Server 2014 (you get a "Deprecated feature 'DROP INDEX with two-part name' is not supported in this version of SQL Server" error while it's upgrading from 4.3 to 4.4). I had to do a fresh install to get around that issue, though it's probably possible to either figure out the necessary upgrade DML/DDL yourself or upgrade SonarQube's ActiveRecord JDBC MSSQL adapter (hoping they'll do that in their next version).

Simple, huh? :p

回答2:

Azure SQL is not the same as a local MS SQL Server install.

Differences are listed here: http://msdn.microsoft.com/en-us/library/azure/ff394115.aspx

Also the documentation of Sonarqube only shows up to MS SQL Server 2008. Azure will be based on a system similar (not identical) to MS SQL Server 2014 at the time of this writing. (They are always updating their system).

回答3:

For me it works only when database name is separated in properties and without jtds e.g.

sonar.jdbc.username=<username>@SERVER
sonar.jdbc.password=<password>
sonar.jdbc.url=jdbc:sqlserver://SERVER.database.windows.net:1433;database=DATABASE;ssl=require;SelectMethod=Cursor

Sonar Qube version 5.3