How to use activerecord-sqlserver-adapter with TinyTDS *and* an Integrated Security connection on Windows *without* saving a password in plain text

Question

I'm trying to use Rails 3.1. with the activerecord-sqlserver-adapter (3.1.1) and tiny_tds (0.4.5) on a Windows machine. In reading about TinyTDS and it's use of FreeTDS it looks like I can use Integrated Security (aka Windows Integrated security/NTLM) by putting a domain-qualified name as the user name (e.g. DOMAIN\userbob). But the docs still want me to type my domain user's password in the database.yml file. That's bad practice because it's insecure and doesn't take advantage of single-sign on, which is part of the point for Integrated Security.

Can I connect without saving a password in plain text in a file? e.g.

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost
    database: dev_db
    username: DOMAIN\userbob
#    password: no_no_please_dont_make_me_type_it_here

But, even if I put a password I get the following error:

TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist
    from C:/Ruby192/lib/ruby/gems/1.9.1/gems/tiny_tds-0.4.5-x86-mingw32/lib/tiny_tds/client.rb:60:in `connect'
    from C:/Ruby192/lib/ruby/gems/1.9.1/gems/tiny_tds-0.4.5-x86-mingw32/lib/tiny_tds/client.rb:60:in `initialize'

I know my server is running and the current user context can connect because this works:

sqlcmd -S localhost -d dev_db -E

Any insights? Is it possible? If not, it should be.

Answer 1

Enable TCP/IP in "SQL Server Network Configuration" in SQL Server Configuration Manager utility. Then restart SQL Server service.

Regarding security you will need to supply your own credentials otherwise it will use sa account.

Answer 2

Apparently SQL Server 2008 is setup by default to ONLY allow Windows Authentication. To change that you have to open the Management Studio, Right click on your server and select Properties. Select Security and click the "SQL Server and Windows Authentication mode" under Server authentication. This will at least allow you to connect directly to the server until TinyTDS makes the needed changes to allow Windows Authentication.

Answer 3

I think the code making the connection is trying to make the process impersonate the security context supplied by the credentials instead of passing the default credentials through to SQL server.

This would allow you to specify different credentials to the current security context. I've used this trick to connect to a server requiring SSIS from an untrusted domain.

I don't know TinyTDS/FreeTDS, perhaps null or empty credentials would make it use the default security context. Try:

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost
    database: dev_db

or

developement:
    adapter: sqlserver
    mode: dblib
    dataserver: localhost
    database: dev_db
    username:
    password: