cloudfoundry-attic/mssql-server-broker

Cloud Foundry service broker for Microsoft SQL Server

mssql-server-broker

This is a Cloud Foundry service broker for Microsoft SQL Server. It currently supports multiple database instances within a SQL Server instances running external to CF.

This version should be considered a beta product, and has been tested against CF Elastic Runtime v1.9

Prerequisites

The service broker requires an existing SQL Server install. It has been tested against the docker-based version described here.

The Modules

broker

This module contains the broker code. Its readme contains information on how to build, configure, and deploy the broker.

connector

This module contains spring-cloud-connector code that can optionally be used by consumers of a brokered service.

client-example

A sample project that demos usage of the broker and the connector. See its readme for more details.

util

Shared utilities for interacting with the SQL Server backend.

mssql-server-broker's Issues

Broker cannot connect

With this application manifest:

---
applications:
- name: mssql-server-broker
  memory: 1G
  instances: 1
  buildpack: java_buildpack
  path: target/mssql-server-broker.jar
  env:
    SPRING_PROFILES_ACTIVE: cloud
    SQL_HOST: sql
    SQL_PORT: 1433
    SQLSERVER_USERNAME: sa
    SQLSERVER_PASSWORD: pass
    SPRING_SECURITY_USER_NAME: 93207046-c552-4971-87ee-fedfcc173398
    SPRING_SECURITY_USER_PASSWORD: pass

This error is thrown:

2018-11-12T14:00:40.01-0700 [APP/PROC/WEB/0] OUT com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host sql.int.reboot3times.org, port 1433 has failed. Error: "Connection refused. 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.".

I can verify with DataGrip, VSCode, and sqlcmd from remote sources that the database is remotely accessible.

✗ sqlcmd -S sql,1433 -U sa -P 'pass'
1> select @@version
2> go


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)
        Oct 18 2018 23:11:05
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

(1 rows affected)
1> quit

The broker was build with mvn clean install -DskipTests with this version of Java:

✗ java -version
openjdk version "1.8.0_181"
OpenJDK Runtime Environment (build 1.8.0_181-8u181-b13-1ubuntu0.18.04.1-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)

It was deployed with the java-buildpack-cflinuxfs2-v4.15 buildpack.

Error in building the broker using Maven

When I attempt to build the broker using the command line :
mvn clean install

I get this error:

[INFO] --------------------< io.pivotal.cf.broker:broker >---------------------
[INFO] Building broker 2.0.0
[INFO] --------------------------------[ jar ]---------------------------------
[WARNING] The POM for io.pivotal.cf.broker:connector:jar:2.0.0 is missing, no dependency information available
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.326 s
[INFO] Finished at: 2018-05-11T11:39:29-05:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal on project broker: Could not resolve dependencies for project io.pivotal.cf.broker:broker:jar:2.0.0: Failure to find io.pivotal.cf.broker:connector:jar:2.0.0 in https://repo.spring.io/libs-milestone was cached in the local repository, resolution will not be reattempted until the update interval of milestone has elapsed or updates are forced -> [Help 1]

Please advise.
Thanks

Missing Documentation On Schema Instantiation

The broker requires several tables to exist in a MSSQL server in order to function: instances, bindings, serviceinstance_parms, binding_parms, and binding_creds.

The current test suite provides a way to instantiate this schema via spring.jpa.hibernate.ddl-auto here but we do not provide this setting in the broker application properties by default. While it is a best practice to not allow Hibernate to initialize databases in production settings, end users need some sort of documentation that these tables must be pre-created before the service broker will function and perhaps a SQL file to help them along.

README says that it's tested with "Enterprise Runtime 1.9"

By Enterprise Runtime, do you mean Elastic Runtime, which I believe has now been renamed "Pivotal Application Service" (https://pivotal.io/platform/pivotal-application-service)? Also, the latest version of elastic runtime on pivnet is 1.12...

[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compile (default-compile) on project connector: Fatal error compiling: java.la ng.ExceptionInInitializerError: com.sun.tools.javac.code.TypeTags -> [Help 1]

[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary for mssql-server-broker 2.0.0:
[INFO]
[INFO] mssql-server-broker ................................ SUCCESS [ 0.799 s]
[INFO] connector .......................................... FAILURE [ 4.344 s]
[INFO] broker ............................................. SKIPPED
[INFO] client-example ..................................... SKIPPED
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 5.582 s
[INFO] Finished at: 2019-03-26T08:21:24-04:00
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compile (default-compile) on project connector: Fatal error compiling: java.la
ng.ExceptionInInitializerError: com.sun.tools.javac.code.TypeTags -> [Help 1]

I am logging this in case anyone else runs in to this issue. JDK 8 is required, not 11