External database connection fails at setup

[Alan Farroll]

Hi,

I am setting up BitBucket server for the first time. I am setting up the bitBucket application on one Windows server and creating the SQL server database on another Windows server.  the database has been created okay and the Bitbucket user login works in SSMS to connect to the database.  The two servers can see each other as I can ping each from the other.  I checked that TCP\IP is enabled in the SQL server engine install.

However, when i attempt to configure the database connection in BitBucket, enter the database details and click "Test" the message states,

"The configuration entered is not valid. A database connection could not be established. Please check your configuration and try again".  Full details are copied in below (IP address hidden on request of my manager).  How do I resolve this please?  Do I setup Firewall rules inbound or outbound on each server?

com.atlassian.stash.internal.db.DefaultDatabaseManager.validateConfiguration(DefaultDatabaseManager.java:217)
	com.atlassian.stash.internal.migration.DefaultDatabaseMigrationService.validateConfiguration(DefaultDatabaseMigrationService.java:85)
	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	...
Failed to obtain JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host XXX.XX.XXX.XX, 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.".
	org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
	com.atlassian.stash.internal.db.DefaultDatabaseValidator.validate(DefaultDatabaseValidator.java:43)
	com.atlassian.stash.internal.db.DefaultDatabaseManager.validateConfiguration(DefaultDatabaseManager.java:214)
	...
The TCP/IP connection to the host XXX.XX.XXX.XX, 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.".
	com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:226)
	com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:277)
	com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2379)
	...

[Moses Thomas]

@Alan Farroll  The  error is clear

• Check if your  microsoft SQL server is actually up and running
• Check SQL server hostname, username and password is correct
• Check there's no firewall rule blocking TCP connection to port 1433
• Check the host is actually reachable
• In  your Database server  look  for what is called my.cnf  in LINUX  it  should be in the MS SQL installation  directory  INSTALLDIR\my.ini  if the bind-address is commented

  Uncomment "bind-address" attribute or change it to one of the following IPs:

  bind-address="127.0.0.1" or database server IP address(static IP for example)

• There is similar issue  here please see step  by  step  how to   allow connection to port 1433  Allow connection to port 1433 for MS SQL 

Best regards,

Mo

[Alan Farroll]

@Moses Thomas 

Thanks for response Mo.  The SQL server is actually running because I can RDP to that server and access the database with the relevant user credentials.

The host, username and password work because I ran a .UDL file on the BitBucket server to the database server with the relevant database user and password and the connection was successful.  So Host is reachable.  I can also ping the BitBucket server from the database server

I checked with my company infrastructure - there is no firewall rules within the organisation domain

I followed the guide to make the TCP\IP protocol in SQL Server Network Configuration manager Active and set to port 1433.  Although there was no IPv6 available only IPv4!!

None of the above resolved the issue.  I am searching for the my.ini file and in fact searching for a file with "bind-address" but so far can find nothing.  Is this only on the database server or the bitbucket server too?

I found this article - https://community.atlassian.com/t5/Bitbucket-questions/the-configuration-entered-is-not-valid-MS-SQL/qaq-p/99209?tempId=eyJvaWRjX2NvbnNlbnRfbGFuZ3VhZ2VfdmVyc2lvbiI6IjIuMCIsIm9pZGNfY29uc2VudF9ncmFudGVkX2F0IjoxNTgzNzkxMDkyMTc1fQ%3D%3D

Where one person references this other article.  Is this a possible please?  https://confluence.atlassian.com/bitbucketserverkb/connecting-to-named-instances-in-sql-server-from-bitbucket-server-779171681.html?_ga=2.163892863.1566668099.1583767789-1510181961.1583497610

[Moses Thomas]

@Alan Farroll   You  are using MS SQL server,  this file should be on the Database server,  but assume this file is empty or  not present.

Let go back  to the error,  After activating TCP connection to port 1433, make sure you apply changes,  restart SQL if prompted  and and SQL Browser Service. After doing this ,  restart Bit-bucket server. if it does not  work ,

Then we may need to check out bind address.

The  idea of bind is that if you have Bit-bucket on the same server as  your MS SQL  then it is  enough to  have bind-address as  local-host IP  127.0.0.1  but if your  MS  SQL  server is  on a different host  then it's better  to  set  the bind address to  the IP address of the MS SQL server( for example 10.3.5.90.x) which could be a static address, then ensure that there  is route(connection) between  Bit-bucket  server and your MS SQL Server.

The  bind-address="127.0.0.1" means connect over local host only (no other networks)

The bind-address="0.0.0.0"  means connect to all networks

more  also you can  bind to public networks or private networks( with certain IP address)

I suggest you go back to  comment i have block "quoted"

Please give feed back !

Kind regards,

Mo

[Alan Farroll]

@Moses Thomas 

Thanks again for more feedback.  So, here is where we are at;I ensured the TCP\IP protocol is enabled
I have ensured in the TCP\IP properties,
      i) The IP4 is active
      ii) The port TCP Port is set to 1433
     (there doesn't appear to be a IP6)
I restarted the SQL server service, the SQL Server Agent and the SQL Server Browser
Restarted the BitBucket Service but still had same issue

I then in the TCP\IP properties
     i) For IP4 changed the IP Address from 127.0.0.1 to 0.0.0.0
Again, I restarted the SQL server service, the SQL Server Agent and the SQL Server Browser
Restarted the BitBucket Service but still had same issue

I went to the SQL Server Service, right clicked and selected properties and took note of the path which was C:\Program Files\Microsoft SQL Server\MSSQL13.SQLServerInstanceName\MSSQL\BINN
Looked in there and found 4 files of type .ini but none of these had a "bind-address" parameter

I also ensured the BitBucket machine had a BITBUCKET_HOME environment variable with a value of C:\Atlassian\Bitbucket\7.0.0 (this was created with the install as a service process)

I have looked at the C:\Atlassian folder and the CREATOR_OWNER has special permissions which in special permissions is Full Control (I am assuming this is enough because the altBitbucket user would set this folder up and be the CREATOR_OWNER

I ensure the altBitbucket user was a member of the Performance Monitoring Group on the server too.

Still cannot find what I am doing wrong!!!

[Moses Thomas]

@Alan Farroll  

I then in the TCP\IP properties
i) For IP4 changed the IP Address from 127.0.0.1 to 0.0.0.0 

Don't  do that!  leave it at it is (  local host IP).

I have questions for you.

• The MS SQL sever  is it on the same host as Bitbucket server ?

1. 1. If  yes  Open a command prompt
   Type in "telnet <IP ADDRESS OF SERVER PC> <PORT>" and press enter.
   type “telnet 127.0.0.1 1433”

               If a blank screen appears then the port is open, and the test is successful

              other wise some is blocking connection to this port. Imagine what is happening

               A=Bitbucket B=Database , 

                A sents a connection request to B, but B is open for a connect(TCP at  1433)

1. 2. If both servers are install on a different host, then ensure that both servers are able to  communicate with each other,  more  also bind-Address will make more  sense, which  will be in the my.ini   on the  MSSQL server,   this  address should be 0.0.0.0  or static ip address of the MSSQL server.

• What is  host XXX.XX.XXX.XX,  in the error ?  is it the same as  local host IP ?

[Alan Farroll]

@Moses Thomas 

Thanks Moses.  Some interesting update now.

Yes, the BitBucket application is on a different server to the database engine (restricted by SQL server license and space on the database server is forcing me to do it this way)

I tried a UDL file and it connected from the BitBucket server to the database on the database server successfully.  HOWEVER - I setup Telnet on the BitBucket server and typed the command

C:\telnet <server Ip address> 1433

The output was "Connecting to <server ip address> ... could not open connection to the host on port 1433"

I am double checking with infrastructure team if anything on network is blocking the connection

Will update when I have information

Cheers.  Alan