Connection Problems with TinyTDS

[Jeffrey Schoolcraft]

I'm trying to do just a quick proof of concept to connect to a SQL Server database from an external system.  In this case the external system is my Mac and the SQL Server database is on a VM with a bridged network configured, and I'm able to get to the Server from the web (500 errors when browsing to a page on the VM where SQL Server is hosted).

Here's my Gemfile:

source :rubygems

# for the db

gem 'tiny_tds'

gem 'activerecord-sqlserver-adapter', '~> 3.1.0'

gem 'activerecord', '~> 3.1.0', :require => "active_record"

gem 'pry', :require => 'pry'

Here's my code:

require "rubygems"

require "bundler/setup"

require 'active_record'

ActiveRecord::Base.table_name_prefix = 'dbo.'

ActiveRecord::Base.establish_connection(

    :adapter => "sqlserver",

    :database => "foobar",

    :username => "sa", # Administrator

    :password => "Yagni!23",

    :host => "192.168.1.4",

    :port => 1433

  )

class Product < ActiveRecord::Base

end

def print_status

  puts "product records:"

  ::Product.all.each {|r| puts r.inspect }

end

print_status

And the error:

product records:

/Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68:in `connect': Unable to connect: Adaptive Server is unavailable or does not exist (TinyTds::Error)

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68:in `initialize'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-sqlserver-adapter-3.1.7/lib/active_record/connection_adapters/sqlserver_adapter.rb:401:in `new'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-sqlserver-adapter-3.1.7/lib/active_record/connection_adapters/sqlserver_adapter.rb:401:in `connect'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-sqlserver-adapter-3.1.7/lib/active_record/connection_adapters/sqlserver_adapter.rb:198:in `initialize'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-sqlserver-adapter-3.1.7/lib/active_record/connection_adapters/sqlserver_adapter.rb:35:in `new'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-sqlserver-adapter-3.1.7/lib/active_record/connection_adapters/sqlserver_adapter.rb:35:in `sqlserver_connection'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:304:in `send'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:304:in `new_connection'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:323:in `checkout_new_connection'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `checkout'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:261:in `loop'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:261:in `checkout'

from /Volumes/Master/jschoolcraft/.rvm/rubies/ruby-1.8.7-p249/lib/ruby/1.8/monitor.rb:242:in `synchronize'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:260:in `checkout'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:162:in `connection'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:185:in `with_connection'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:92

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:106:in `call'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:106:in `default'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:106:in `[]'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:106

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/base.rb:717:in `call'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/base.rb:717:in `default'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/base.rb:717:in `[]'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/base.rb:717:in `columns_hash'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/locking/optimistic.rb:145:in `locking_enabled?'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/relation.rb:111:in `to_a'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/relation/finder_methods.rb:159:in `all'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/base.rb:441:in `__send__'

from /Volumes/Master/jschoolcraft/.rvm/gems/ruby-1.8.7-p249/gems/activerecord-3.1.4/lib/active_record/base.rb:441:in `all'

from extract.rb:23:in `print_status'

from extract.rb:26

It feels like it's timing out.  As when I put in a wrong ip it returns almost instantly.  On the correct IP it times out.

[Ken Collins]

I suggest that you debug the connection at a low level first using a simple IRB prompt with TinyTDS first. Something like this.

$ irb
> require 'rubygems'
> require 'tiny_tds'
> client = TinyTds::Client.new :username => 'sa', :password => 'secret', :host => 'mydb.host.net'

To that end, I suspect your SQL Server is not listening to your VMWare's IP address. To check and/or remedy that, open up the "SQL Server Configuration Manager". Then select the "SQL Server 2005 Network Configuration" => "Protocols for MSSQLSERVER". A list should appear on the right hand side. Right click on "TCP/IP" and go to "Properties...". In that window, select the "IP Addresses" tab and have at it.


- Ken

[Bill]

I am having a similar problem.  I use the irb method you describe and it works on my local box, however it does not work on any other rails box I have tried.

This app has been working using TinyTDS for years, however one of our providers is upgrading to a new version of MS SQL server.

On my local linux box I get the following:

ruby-1.9.2-p290 :004 > client = TinyTds::Client.new(:username => 'fred', :password => 'george', :host => '11.11.11.11')

 => #<TinyTds::Client:0x8becdd4>

On my dev and production linux boxes, I get the following:

irb(main):003:0> client = TinyTds::Client.new(:username => 'fred', :password => george', :host => '11.11.11.11')

TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist

        from /usr/local/lib/ruby/gems/1.9.1/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68:in `connect'

        from /usr/local/lib/ruby/gems/1.9.1/gems/tiny_tds-0.5.1/lib/tiny_tds/client.rb:68:in `initialize'

        from (irb):3:in `new'

        from (irb):3

        from /usr/local/bin/irb:12:in `<main>'

As far as I have been able to tell, they have all the same versions of gems and configuration files.

Thank you for any help you may be able to give.

Bill

[Bill]

Also, I checked with the provider, they are not doing any IP address filtering.

Bill

[Jim Hogue]

Bill,

Here is a short script I used to test get my azure connection working with tinytds from my mac and linux boxes.

Just put it in a file named foo.rb, put in your info, and then ruby foo.rb.

require 'rubygems'

require 'tiny_tds'

client = TinyTds::Client.new(

                             :username => "your_login@your_host",

                             :host     => "your_host.database.windows.net",

                             :password => "your_password",

                             :database => "yourDB",

                             :azure    => true)

if client.active?

  puts "we are connected"

else

  puts "we are not connected"

end

result = client.execute("select * from one-of_your_tables;")

puts "Got back - #{result.count} records"

--
You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.

To view this discussion on the web visit https://groups.google.com/d/msg/rails-sqlserver-adapter/-/e4izDQbuLesJ.

To post to this group, send email to rails-sqlse...@googlegroups.com.
To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.

[Bill]

I added the azure option to my test and got the error "Azure connections not supported in this version of FreeTDS".

I checked the version of FreeTDS on my boxes and my dev and prod boxes show 0.82 while my local linux box shows 0.82-7.

I am able to connect using the local box without the azure option, but not from dev or prod.

Do I need to upgrade to the latest freetds?  I will have to be careful as this is a currently running production application that we are switching SQL servers on.

Bill

To unsubscribe from this group, send email to rails-sqlserver-adapter+unsub...@googlegroups.com.

[k...@metaskills.net]

Yes, we document that 0.91 is needed TinyTDS read me.

https://github.com/rails-sqlserver/tiny_tds/#using-tinytds-with-azure

Remember, 0.82 is about 5 or more years old.

 - Ken

To view this discussion on the web visit https://groups.google.com/d/msg/rails-sqlserver-adapter/-/TB6DuavyymIJ.

To post to this group, send email to rails-sqlse...@googlegroups.com.

To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.

[Bill]

Thank you for all of your help so far.

I updated to the 0.91 of FreeTDS on all of our boxes and straitened out some firewall issues on the MS SQL server end, I can now connect to the new database using irb and the test script with no error.

However, when I start the server (thin) I get the same error "/usr/local/lib/ruby/gems/1.9.1/gems/tiny_tds-0.4.5/lib/tiny_tds/client.rb:60:in `connect': TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist (Sequel::DatabaseConnectionError)".

Do you have any ideas?

The two boxes that are having issues are Debian 5.0

The box that works is 11.04

Thank you

Bill

[k...@metaskills.net]

You should debug with the latest TinyTDS, we are in 0.5.2 prerelease now.

 - Ken

To view this discussion on the web visit https://groups.google.com/d/msg/rails-sqlserver-adapter/-/NQaXcFe2UA4J.

To post to this group, send email to rails-sqlse...@googlegroups.com.

To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.

[Bill]

I get the same error with 0.5.2.rc1.

Bill

[Bill]

Could the version of OpenSSL make a difference?

I am using "OpenSSL 0.9.8o 01 Jun 2010" on my local box.

I am using "OpenSSL 0.9.8g 19 Oct 2007" on the two boxes that are having the connection issue.

Thank You

Bill

[k...@metaskills.net]

Should not make a difference, tho I have 1.0.1c myself. FWIW, have you read the wiki page and looked over previous old issues with azure in the keywords to see if your not dealing with an issue someone else has?

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-Azure

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/issues

https://github.com/rails-sqlserver/tiny_tds/issues

 - Ken

To view this discussion on the web visit https://groups.google.com/d/msg/rails-sqlserver-adapter/-/d4I6s_QFx04J.

To post to this group, send email to rails-sqlse...@googlegroups.com.

To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.

[Bill]

Turns out my last issue was a gem version issue with the sequal gem. My local box had 3.25 and the servers had 3.24.  After updating to the the latest, 3.40, everything works.

Thank You for all of your help!

Bill