Bamboo 6.10 connection to MSSQL 2016

Question

I'm about to pull my hair out.  And, that's pretty bad since I don't have much to pull...

After using Bamboo for a month or so, I'm trying to get it ready for production and setup to work with MSSQL 2016.

I have followed the Bamboo docs of setting up the server for authentication, setting up a database first, and I have tried the sa user and a separate user I setup, BambooUser, both with the all rights to the bamboo database.  During the Bamboo setup, there were no open queries connected to the bambooDB in SSMS.  We have a commercial product we sell which uses MSSQL as a backend and the Bamboo install is on the same server, same instance.  I even let our commercial software create the BambooDB database, then I dropped all tables so it was clean.  Still no connection from Bamboo.

The Microsoft docs say when using the jdbc connection, since the default port is 1433, it is optional.  But all of the Bamboo docs say to add it in.  So, I have tried both.

The log always states:

Validating database configuration...

Validating connection to url:

Could not find token mapped to token name: token

Then, it fails with the "Could not successfully test your database:" and then "Error: Connection refused:"

Thinking this was a connection issue with the jdbc connect string, I have tried the following:

jdbc:sqlserver://[IPAddress]:1433;databaseName=bambooDB

jdbc:sqlserver://[IPAddress];databaseName=bambooDB

jdbc:sqlserver://[ComputerName\Instance]:1433;databaseName=bambooDB

jdbc:sqlserver://[ComputerName\Instance];databaseName=bambooDB

jdbc:sqlserver://localhost:1433;instanceName=MyInstance;databaseName=bambooDB

jdbc:sqlserver://[ComputerName]:1433;databaseName=bambooDB

jdbc:sqlserver://[ComputerName];databaseName=bambooDB

jdbc:sqlserver://localhost:1433;databaseName=bambooDB

jdbc:sqlserver://localhost;databaseName=bambooDB

All fail with the same error, Connection refused.

Is there any type of Bamboo utility to diagnose the connection problem with MSSQL?

Since the default DB is not production ready, this is a show-stopper for us.  And, since we use practically the full suite of Atlassian software, our higher-ups want to use Bamboo.

Thanks,

Carl

Carl Davis_kj94gl · Answer

I entered this response last night but it does not look like the site updated this entry.

Thank you Jey.

Update:

Just about the only version of the above connection strings I did not try, is the following:

jdbc:sqlserver://localhost;instanceName=NameOfInstance;databaseName=BambooDB

Removing the port number and specifying the instance name solved the connection issue. Sorry for not including the link, but I saw somewhere on the Bamboo site, if you have a named instance, use ;instance=nameoftheinstance. Actually, the correct property is instanceName=.

So, after wiping out Bamboo_Home and rerunning bamboo, the log shows the connection to the database was finally successful. Now, the log file shows other issues:

2020-01-26 17:55:44,610 INFO [performSetupDatabaseConnectionBackgroundThread] [DefaultHibernateConfigurator] Creating Bamboo schema...
2020-01-26 17:55:45,482 WARN [performSetupDatabaseConnectionBackgroundThread] [SqlExceptionHelper] SQL Warning Code: 1945, SQLState: S0001
2020-01-26 17:55:45,482 WARN [performSetupDatabaseConnectionBackgroundThread] [SqlExceptionHelper] Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK__CWD_PROP__3EC056C87EF4637E' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.

The maximum key length in MSSQL 2014 is 900, but the key length for 2016 was increased to 1700 bytes, so from this warning, it would seem to me that Bamboo is not getting the correct version of my install of MSSQL 2016. In detail it is:

Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Is Atlassian positive that Bamboo 6.10.4 is really compatible with MSSQL 2016 SP2???

Thanks,

Carl

Jeyanthan I · Answer

Hi @Carl Davis ,

The error message "Connection refused" usually means that the port that accepts database connection isn't open. Have you double checked if the port 1433 (from your JDBC string) is open between the application and database server?

If bamboo is running on windows, you can use the following PowerShell command to check if the port is open:

$server="SERVER"; $port=1433; echo ((new-object Net.Sockets.TcpClient).Connect($server,$port)) "$server is listening on TCP port $port";

If you see that the port is open and you still get this error, please check if the database user has the right permissions to read and write to the db.

As you mentioned that you use our Atlassian stack, I assume you have a valid license to contact Atlassian Support. Feel free to reach us on support.atlasssian.com for further assistance.

Good luck :)

Cheers,
Jey

Carl Davis_kj94gl · Answer

Update:

Just about the only version of the above connection strings I did not try, is the following:

jdbc:sqlserver://localhost;instanceName=NameOfInstance;databaseName=BambooDB

Removing the port number and specifying the instance name solved the connection issue.  Sorry for not including the link, but I saw somewhere on the Bamboo site, if you have a named instance, use ;instance=nameoftheinstance.  Actually, the correct property is instanceName=.

So, after wiping out Bamboo_Home and rerunning bamboo, the log shows the connection to the database was finally successful.  Now, the log file shows other issues:

2020-01-26 17:55:44,610 INFO [performSetupDatabaseConnectionBackgroundThread] [DefaultHibernateConfigurator] Creating Bamboo schema...
2020-01-26 17:55:45,482 WARN [performSetupDatabaseConnectionBackgroundThread] [SqlExceptionHelper] SQL Warning Code: 1945, SQLState: S0001
2020-01-26 17:55:45,482 WARN [performSetupDatabaseConnectionBackgroundThread] [SqlExceptionHelper] Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK__CWD_PROP__3EC056C87EF4637E' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.

The maximum key length in MSSQL 2014 is 900, but the key length for 2016 was increased to 1700 bytes, so from this warning, it would seem to me that Bamboo is not getting the correct version of my install of MSSQL 2016. If we look at the key, it contains PROPERTY_KEY and PROPERTY_NAME, both are nvarchar(255), which is a total of 510, but when nvarchars are used in a key, the size is doubled to 1020.  In MSSQL 2016, this should still be ok, but the log file says it is larger than 900 thinking it is MSSQL 2014 or something.

In detail it is:

Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Is Atlassian positive that Bamboo 6.10.4 is really compatible with MSSQL 2016 SP2???

Thanks,

Carl