Connect rails application to SQL Server 2005 from Windows

Question

Hi guys.

I (sadly) have to deploy a rails application on Windows XP which has to connect to Microsoft SQL Server 2005.

Surfing in the web there are a lot of hits for connect from Linux to SQL Server, but cannot find out how to do it from Windows.

Basically I followed these steps:

• Install dbi gem
• Install activerecord-sql-server-adapter gem

My database.yml now looks like this:

development:
  adapter: sqlserver
  mode: odbc
  dsn: test_dj
  host: HOSTNAME\SQLEXPRESS
  database: test_dj
  username: guest
  password: guest

But I'm unable to connect it. When I run rake db:migrate I get

IM002 (0) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I'm not a Windows user, so cannot understand really well the meaning of dsn element or so. Does someone have an idea how to solve this?

Thanks in advance

---

With Alexander tips now I've modified my database.yml to:

development:
  adapter: sqlserver
  mode: odbc
  dsn: Provider=SQLOLEDB;Data Source=SCADA\SQLEXPRESS;UID=guest;PWD=guest;Initial Catalog=test_dj;Application Name=test

But now rake db:migrate returns me:

S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer length

Am I missing something?

Answer 1

this is a sample DSN, that connects to the database using the Windows user account (best when corporate network with domain login)

Provider=SQLOLEDB;Data Source=MyServer\MyInstance;Integrated Security=SSPI;Initial Catalog=MyDatabase;Application Name=My Application Name that will show up in the trace

So this uses the OLEDB provider for SQL Server. SQLNCLI can also be used, haven't tried it with ODBC. Actually this DSN isn't quite tested (have to wait for my admin to give me the necessary rights), but it was copied from a working script, that used SQLNCLI as the provider. The Data source is the server, and if it has a named instance, it has to be specified, so it's either just Server or Server\Instance. The Integrated Security=SSPI tells it you want to use Windows Authentication. Otherwise you specify the user and password to use using UID=MyUser;PWD=MyPassword. UID, User, Username, Password - I think all of these parameters work.

There is a great site over the internet that provides all kinds of DSN samples, just can't find it anywhere. If I find it, I will let you know.

If you haven't got any provider in Rails, check out if Rails supports Windows Component Object Model (COM). And if it does, you can even initialize the ADODB.Connection COM class, work with ADO thereon.

Ah, here is that site I was talking about: http://www.connectionstrings.com/

Answer 2

Hi Enrico,

I don't see how you can use the SQLServer OLEDB Provider (SQLOLEDB) to make the ODBC connection the rails dbi gem is attempting, so you must be using the SQLServer ODBC Driver with a suitable ODBC DSN configured. I note in your original connect string you specified a DSN of "test_dj" , is this configured as a user or system ODBC DSN in the ODBC Administrator tool for windows ? As I would recommend it be created as a system DSN for system wide access by any user/process rather than a user DSN which limits it to process running as that user. Or if supported by the active record sqlserver adpater gem make a DSN'less connect string of the form:

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase; Uid=myUsername;Pwd=myPassword;

As detailed in the SQLServer connnectstring page.

Note, you might also want to try the Rails ODBC Adapter for Active Record, which is an alternative Adapter with generic ODBC support for connectivity between Rails and other ODBC accessible data stores.

Best Regards
Hugh Williams
Professional Services
OpenLink Software
Web: http://www.openlinksw.com
Support: http://support.openlinksw.com
Forums: http://boards.openlinksw.com/support
Twitter: http://twitter.com/OpenLink