Connect rails application to SQL Server 2005 from Windows

Question

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/