SQL Server: Determine port of named instance using SQL Server Browser

[buhlmann-thomas-rix]

When using MS SQL Server with a named instance as database server, connecting via port 1433 does not work, because named instances run on different ports. By default, these run on random high ports that change everytime the database engine is restarted.

Using Redmine with SQL Server and named instances currently requires to set the port of a SQL Server instance to a fixed value via SQL Server configuration, because Redmine requires this port to connect to the database server.

The SQL Server provides the SQL Server Browser service that will return the actual port an instance is running on. Would it be possible to configure Redmine/Ruby in a way that it uses the Server Browser to determine the actual SQL Server port and use that for database connection attempts?

[LaurentGoderre]

Have you tried leaving the port variable empty?

[buhlmann-thomas-rix]

Have you tried leaving the port variable empty?

Yes, I tried to comment out the port line from database.yml, but then the DB connection cannot be established. Also setting it to an empty string value does not work as well. Error message in both cases below:

redmine-1  | rake aborted!
redmine-1  | TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist (dns-name-of-sql-server) (TinyTds::Error)
redmine-1  | /usr/local/bundle/gems/tiny_tds-2.1.7/lib/tiny_tds/client.rb:60:in `connect'
redmine-1  | /usr/local/bundle/gems/tiny_tds-2.1.7/lib/tiny_tds/client.rb:60:in `initialize'
redmine-1  | /usr/local/bundle/gems/activerecord-sqlserver-adapter-6.1.3.0/lib/active_record/connection_adapters/sqlserver_adapter.rb:73:in `new'
redmine-1  | /usr/local/bundle/gems/activerecord-sqlserver-adapter-6.1.3.0/lib/active_record/connection_adapters/sqlserver_adapter.rb:73:in `dblib_connect'
redmine-1  | /usr/local/bundle/gems/activerecord-sqlserver-adapter-6.1.3.0/lib/active_record/connection_adapters/sqlserver_adapter.rb:66:in `new_client'
redmine-1  | /usr/local/bundle/gems/activerecord-sqlserver-adapter-6.1.3.0/lib/active_record/sqlserver_base.rb:11:in `sqlserver_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in `public_send'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:882:in `new_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:926:in `checkout_new_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:905:in `try_to_checkout_new_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:866:in `acquire_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:588:in `checkout'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:428:in `connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/connection_pool.rb:1128:in `retrieve_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_handling.rb:327:in `retrieve_connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/connection_handling.rb:283:in `connection'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/tasks/database_tasks.rb:237:in `migrate'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/railties/databases.rake:92:in `block (3 levels) in <top (required)>'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/railties/databases.rake:90:in `each'
redmine-1  | /usr/local/bundle/gems/activerecord-6.1.7.7/lib/active_record/railties/databases.rake:90:in `block (2 levels) in <top (required)>'
redmine-1  | /usr/local/bundle/gems/rake-13.2.1/exe/rake:27:in `<top (required)>'
redmine-1  | Tasks: TOP => db:migrate
redmine-1  | (See full trace by running task with --trace)
redmine-1 exited with code 0