Adaptive Server is unavailable when connecting to local instance

[Portalboy]

I'm getting the following error when attempting to connect to my instance of SQL Server on Windows 10:

Unable to connect: Adaptive Server is unavailable or does not exist (127.0.0.1:1433) (TinyTds::Error)

Note that I have tried this with a plethora of connection settings, a loopback address (127.0.0.1), the localhost alias, my internal ipv4 address (198.168.x.x), nothing is working. This is my current code:

`` require 'tiny_tds'

puts 'test'       
client = TinyTds::Client.new username: 'foo', password: 'bar', dataserver: '192.168.x.x:1433'

puts 'connection set, trying query...'
results = client.execute("SELECT @@Version")  
puts 'iterating...'
results.each do |row|  
puts row  
end

``

I never make it to the second puts, I error out during the connection phase. I've tried using host: 'foo', port: 'bar', using dataserver: 'localhost/MSSQLSERVER' and using dataserver: '192.168.x.x:1433'

I have ensured that my instance is using port 1433 through SQL Server Configuration Manager, and I have sqlservr.exe allowed in Firewall for both Domain and Private networks (I'm on a Domain network). I'm using FreeTDS bundled with the Windows TinyTDS gem. It's freeTDS version 1.0.4. I'm trying to connect to an instance of Microsoft SQL Server 2016 on my local machine, running Windows 10. I have been trying recommendations from other posts with similar errors, but none of the suggestions have been working for me.

Does anyone know what the problem could be?

[aharpervc]

You definitely have TCP/IP enabled in SQL Server Configuration Manager, right?

[Portalboy]

Yes, it's definitely enabled. I also set the port to 1433 and Static there.

[metaskills]

Can you use :host and :port option vs putting them together into the :dataserver? The way the code is written (https://github.com/rails-sqlserver/tiny_tds/blob/master/lib/tiny_tds/client.rb#L41-L42) it would end up tacking on another port to that and might be the source of your issue.

[Portalboy]

I've tried that too, still no cigar.

[metaskills]

A few more thoughts:

• Have you tried localhost\MSSQLSERVER? Notice how that is a back slash vs. forward slash.
• Have you made sure the user/password is a SQL Server user and has the proper permissions to the DB, etc?
• Have you tested the tsql command line option with the same settings?
• What platform are you on again?

[Portalboy]

A few things: I updated Ruby to the latest stable version (2.3.1p112, 2016-04-26 revision 54768) and reinstalled all gems, including TinyTDS. This fixed the given error. Of course, afterwards I ran into issues where my instance wasn't set up for Mixed authentication mode, but I eventually solved that. I can now get results back from a SELECT @@Version query. If at all possible, it would be great to have a less ambiguous error message than "Login failed for user" when SQL Server is in mixed authentication mode, as debugging this took ages before I realized this was the issue. I don't know if this is a FreeTDS Limitation though.

In response to your question, in case it helps troubleshooting:

• I hadn't tried that, but the :host, :port wasn't working either, and that should have accomplished the same thing.
• The user did have proper permissions set up, and was set as db_owner (gasp!) for the database I was trying to access.
• I did try tsql, but I don't remember the results, sorry.
• Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Express Edition (64-bit) on Windows 10 Pro 6.3 (Build 14393: ) with Ruby 2.3.1p112, 2016-04-26 revision 54768 and TinyTDS 2.6.7

[metaskills]

Thanks! I added a note to the issue template.

[alhowarth]

You definitely have TCP/IP enabled in SQL Server Configuration Manager, right?

Thank you!