Cannot connect to Microsoft SQL Server using IntelliJ/Rider

Question

I'm getting the following error when I try to create a database for my project:

[08S01] The TCP/IP connection to the host myhost, port 1433 has failed. Error: "Connection timed out: no further information. 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've tried every possible solution I could find online. I changed the ports in the configuration manager to 1433, checked if the port is blocked by the firewall (it's not), turned on the SQL Server Browse, tried using integratedSecurity = true int the connection string and nothing worked.

Would appreciate any help, thank you

Answer 1

The most probable cause of connection failure is explained in the 3rd comment (by Jura Gorohovsky) on the question and is actually covered in the OP. To repeat, the details are here.

My problem was different and caused by confusion about (localdb). But before that, as this is the first actual answer, I will summarise the steps in the Jetbrains post, referred to by Jura and address the most common issue.

SQL Server (SQLEXPRESS) requires TCP/IP to be enabled for access by Rider (and presumably other Jetbrains products such as Intellij and, in fact any process using JDBC). The following steps are required:

Configuring SQL Server for Rider connections:

1. Open SQL Server Configuration Manager for the approrpiate version - cmd.exe /c start C:\Windows\SysWOW64\SQLServerManager14.msc - SS2017, SQLServerManager13.msc - SS2016, SQLServerManager12.msc - SS2014. The snap-ins are also found in System32.
2. The detail panel for SQL Server Services should show SQL Server (SQLEXPRESS).
3. for SQL Server Network Configuration->Protocols for SQLEXPrESS ensure that the detail panel shows the line TCP/IP as Enabled.
4. In TCP/IP->Properties->IP Addresses (right click TCP/IP). Set IPAll->TCP Dynamic Ports to blank and IPAll->TCP Port to 1433 (or other if you have changed the SS config).
5. Confirm all and restart SQL Server (SQLEXPRESS) (right click on SQL Server Services->SQL Server (SQLEXPrESS)) and you should be able to connect with the url jdbc:jtds:sqlserver://localhost;instance=SQLEXPRESS. You can also use the Tds driver.

I assme there is nothing special about SQLEXPRESS and any SS instance you have created can be opened up in the same way.

Note that you may encounter problems if you use a port other than the default of 1433. (I can't currently find the docs.) If you specify the port number in the Rider connection page then the instance will be ignored and connection will be made to the default instance. The instance that you connect to from Rider must be either set as the default or have a port number of 1433.

My problem: I enabled TCP/IP early on but ran into the problem that I could only connect to some sort of default instance which I know nothing about. This was caused by my failing to distinguish sufficiently between (localdb) and SQL Server proper, not being sure whether SQLEXPRESS was a separate product or an instance of SS and being unfamiliar with the SS Configuration snap-in. This will also present itself to the unwary as simply being unable to connect - if you don't happen to have SQLEXPRESS or other SS installed.

This InfoQ article summarises the role of (localdb). It's a kind of light weight version of SQL server but with none of its process admin and not using the same discoverability or transport. It will not show up in the SS Configuration snap-in.

Once you have taken on board the distinction between (localdb)/SQLLocalDB.exe and the other flavours of SS such as SQLEXPRESS the connection from Rider is quite simple and covered here.

Connectiong Rider to (localdb) - summary of post referred to above:

1. Run SQLLocalDB -s MSSQLLocalDB
2. In Rider right hand panel do Database->+->Data Source->SQL Server.
3. Select Driver as SQL Server (Tds)
4. Select LocalDB from the dropdown to the right of the URL.
5. Select MSSQLLocalDB (or other available instance) from the Instance drop down.
6. Optionally specify a database in the text box.
7. Provide credentials if not using pass through authentication
8. Do Test Connection etc.

The transport is a named pipe. The other end is provided by the SQLLocalDB.exe process. If Rider remains unconnected for more than a few minutes then SQLLocalDB.exe will exit and you will need to rerun it next time you use Rider. This can be addressed with this solution.

Tested with Rider 2018.2.3.