Has anyone configured an SQL Server connection via ODBC using Windows Authentication instead of username/password access?

We have numerous ODBC connections to SQL Server and they all work perfectly well but they all use username/password access. I now need access to another server and they will only allow windows authentication.

I can create the ODBC connection and test it successfully on the server but by the time the query reaches the SQL server the username has been lost and the login is denied.

I have found several threads on here talking about using Kerberos, would this work with ODBC? I have had a call raised with internal tech support for several months, and they in turn have a case open with IB but I am not getting anywhere. IB are saying they don't support ODBC connections for SQL Server and we have to buy the adaptor, but are also saying buying the adaptor won't necessarily solve our problem.

I directed IB to a thread on this forum talking about Kerberos to see if it would provide a solution but they are not answering the question. It seems I need to find out what the solution is so I can tell IB 'can you help us implement this'!

Any advice or experiences you guys have would be very helpful.

Thanks

This message has been edited. Last edited by: <Kathryn Henning>,

To the best of my knowledge, the ODBC adapter does not support password pass-through. Password pass-through is what will allow WF to forward user credentials to the back end adapter. A Kerberos configuration will also allow this (via password pass-through), but again, not for ODBC.

Have you thought of using OLE DB to connect instead?

Thanks for your response.

I know an ODBC connection will work with windows authorisation, I can successfully create an ODBC connection from Excel and I don't need to login to the database it's happy to accept my login id (it doesn't send a password). It also used to work back in ye olde days when we were on v4.3.6 (so I'm told by someone who used to use it then) however our configuration has substantially changed since then.

The issue is I think due to the number of times the userid has to be passed before reaching the SQL Server. I've seen people talking on here about two-hops being the maximum. I don't pretend to understand all this, I'm just an end-user who knows a few things as I'm supposed to be able to rely on our external IT support to solve any problems I have!

I've not used OLE DB before, I'll do some reading on that and see what I can make of it. Thanks again.