Connect to Azure DB (SQL Server via TinyTds)

[shrekuntu]

My company moved to a cloud ERP and I need to connect to it but having some issues.
First to say that I can successfully connect via tsql command line

tsql -H rodb.erpsaas.com -D db_name -U username -p 50135 -P 'password'

I need to point out that this command only worked by enclosing password in the single quotes. Maybe because it contains special characters such as \ and | which I don't think I can change, but it works as posted above

Now, in my roda code I have the following:

CLOUD = Sequel.connect(:adapter => "tinytds", :username => 'username', :password => 'password', :host => "rodb.erpsaas.com", :port => 50135, :azure => true, :ansi => true, :textsize => 64000000)

and I'm getting an error:

TinyTds::Error: Login failed for user 'username@rodb'.

This is an old legacy app using sequel 4.48 if that matters.

[jeremyevans]

Looks like a tiny_tds issue. If you can get this to work using tiny_tds directly, but still cannot get it to work using Sequel, post how you can get it to work using tiny_tds.

[shrekuntu]

Here it is (with generic names):

``
tsql -H rodb.erpsaas.com -D db_name -U username -p 50135 -P 'password'

locale is "en_US.UTF-8"

locale charset is "UTF-8"

using default charset "UTF-8"

Setting db_name as default database in login packet

1> select partnum from db_name.erp.part where partnum='5CS13';

2> go

partnum

5CS13

(1 row affected)

1>
``

[jeremyevans]

That is using the tsql command line tool, not the tiny_tds gem. Can you get a valid connection using the tiny_tds gem?: https://github.com/rails-sqlserver/tiny_tds?tab=readme-ov-file#tinytdsclient-usage

[shrekuntu]

Here it is:

$ irb
2.4.0 :001 > require 'tiny_tds'
 => true 
2.4.0 :002 > client = TinyTds::Client.new username: 'username', password: 'password', host: 'rodb.erpsaas.com', port: 50135, database: 'db_name'
 => #<TinyTds::Client:0x00000000b67380 @message_handler=nil, @query_options={:as=>:hash, :symbolize_keys=>false, :cache_rows=>true, :timezone=>:local, :empty_sets=>true}> 
2.4.0 :003 > result = client.execute("select partnum from db_name.erp.part where partnum like '%CS13'")
 => #<TinyTds::Result:0x00000000cd7918 @query_options={:as=>:hash, :symbolize_keys=>false, :cache_rows=>true, :timezone=>:local, :empty_sets=>true}> 
2.4.0 :004 > result.each do |row|
2.4.0 :005 >   puts row['partnum']
2.4.0 :006?> end
4CS13
6CS13
5CS13
 => [{"partnum"=>"4CS13"}, {"partnum"=>"6CS13"}, {"partnum"=>"5CS13"}] 
2.4.0 :007 > 

[shrekuntu]

Jeremy, I think I found a solution. In my test with tiny_tds gem I noticed that I didn't use azure flag which worked, once I removed it from my Sequel.connect() it started working.
Thank you very much for all your help. Your dedication and help have been greatly appreciated all these years and I've been using sequel since 2012 now.

[jeremyevans]

Glad to hear you found the solution!