Windows Authentication / Trusted Connection

[Mikeg250]

Ken, what is the proper way to connect to a local SQL Server instance
using Windows Authentication (ie: without a username & password)?

I found a few references from previous tutorials (http://
www.codecapers.com/post/A-Ruby-on-Rails-Tutorial-for-NET-Developers.aspx)
where database.yml has:
adapter: sqlserver
mode: odbc
dsn: Driver={SQL Native
Client};Server=.;Database=RubyDemo;Trusted_Connection=yes;

Since your new TinyTDS powered driver doesn't use the DSN line, is
there a corresponding method for configuring the DATASERVER line with
the same "Trusted_Connection=yes" syntax?

I've searched through your github and GoogleGroups, but didn't find
any answer. Let me know if this is possible.

Thank you,

[Ken Collins]

I'm not exactly sure. You are going to want to search "FreeTDS" and "Integrated Security". Then you are going to want to find out if DBLIB which TinyTDS uses, even supports that and report back to me.

- Ken

> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> 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.
>

[Mike G]

Will do -  thanks for the search term tips :)

[Mikeg250]

There's not much out there on DBLIB, but here's what I found:

1. FreeTDS supports Windows Authentication/NTLM: "Domain logins are
recognized by the presence of a backslash (\) character in the
username. See the User Guide for details" (source: http://www.freetds.org/faq.html)

2. But it's unclear from the User Guide if FreeTDS's DSN-less
configuration supports Windows Authentication (http://www.freetds.org/
userguide/dsnless.htm)
If I'm not mistaken, your TinyTDS powered adapter is DSN-less
while your older ODBC powered adapter uses DSN.

3. I did find a great posting on the FreeTDS mailing list from Jan
2010 that posed & answered this same question (I think):
http://lists.ibiblio.org/pipermail/freetds/2010q1/025435.html

If you follow the thread, it looks like Freddy77 created a new .dll to
make it work, but the link is 404: "I uploaded a dll cross compiled at
http://freetds.sf.net/odbc.zip"

I'm out of my league after that, so maybe someone else can get a more
complete answer.

Thanks,

- mike

[Ken Collins]

It's important to note that the term DSN for FreeTDS is not really applicable. FreeTDS is a protocol with 3 ways to interface to their API – CTLIB, DBLIB, and ODBC. A DSN really only applies to the ODBC API that FreeTDS offers up. Things get confusing when consider that FreeTDS has a configuration file (freetds.conf) that can be located in 1 of many different places and can include information to drive or supplement a connection via any of the other API methods, ODBC included.

It was my goal when doing the latest FreeTDS that is cross compiled for Windows to remove the need for anyone to go into some external freetds.conf file and "hook" things up. FreeTDS 0.91 let's us do that. I seem to remember there was a magic freetds.conf setting like "secure" that did this for you automatically. I see nothing in the DB-Library specs that talk about integrated security either. So if this is something that a freetds.conf has to supplement, then you will have to setup a FREETDS environment variable that points to a conf file, configure it, etc. If even possible.

- Ken

[Corey]

I just grabbed a fresh copy of the FreeTDS 0.91 release and can
confirm that the included "tsql" program works just fine in integrated
auth mode without any config file or magic environment variables.

c:\opt\freetds-0.91\src>apps\tsql.exe -H tetautai -p 1433
locale is "English_Australia.1252"
using default charset "CP1252"
1> select SYSTEM_USER;
2> go

NOUMEA\coreyc
(1 row affected)
1> exit

c:\opt\freetds-0.91\src>apps\tsql.exe -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: yes
Sybase binary compatibility: no
Thread safety: yes
iconv library: no
TDS version: 7.1
iODBC: no
unixodbc: no
SSPI "trusted" logins: yes
Kerberos: no

c:\opt\freetds-0.91\src> dir \usr\local\etc
Volume in drive C has no label.
Volume Serial Number is FAB2-5EC9

Directory of c:\usr\local

File Not Found

c:\opt\freetds-0.91\src>



In looking at the code, it would appear that all the magic happens
down in the base TDS code that gets shared between CTLIB and DBLIB:

tds/login.c (starting at line 751)

/* check ntlm */
#ifdef HAVE_SSPI
if (strchr(user_name, '\\') != NULL || user_name_len == 0) {
tds->authentication = tds_sspi_get_auth(tds);
if (!tds->authentication)
return TDS_FAIL;
auth_len = tds->authentication->packet_len;
packet_size += auth_len;
#else
if (strchr(user_name, '\\') != NULL) {
tds->authentication = tds_ntlm_get_auth(tds);
if (!tds->authentication)
return TDS_FAIL;
auth_len = tds->authentication->packet_len;
packet_size += auth_len;
} else if (user_name_len == 0) {
# ifdef ENABLE_KRB5
/* try kerberos */
tds->authentication = tds_gss_get_auth(tds);
if (!tds->authentication)
return TDS_FAIL;
auth_len = tds->authentication->packet_len;
packet_size += auth_len;
# else
return TDS_FAIL;
# endif