We need to access information in an Informix database.
We were provided with a 32bit ODBC driver by the people who look after our Informix database (they informed us due to changes made by themselves that it is the only ODBC driver that will work correctly, so using IBM's Informix SDK will not work).
We have a 32bit Windows 2003 Server running SQL Server 2005 (32bit – SP1) and the ODBC driver is installed on this server and setup as a SYSTEM DSN.
Within SQL Server 2005 we have setup a link server using the Microsoft OLE DB provider for ODBC and the linked server’s data source points to the SYSTEM DSN.
If I make an “openquery” to the SQL 2005 server from my PC using SQL Server Management Studio logged in as our domain admin account (using Windows authentication), which is also a member of the local administrators group on the server (and it’s also the account that installed SQL Server) the query works fine.
If I login to SQL Server Management Studio from my PC as myself (I have added my Windows/AD account and given myself the sysadmin rights within SQL Server), when I try to run the same query it just freezes (well actually it sits there just executing the query) and brings back no results. However I find if I add myself as a local administrator on the Windows server and re-run the query it works fine.
I can only assume that this is something to do with Windows permissions/authentication on the actual Windows server in regards to accessing the SYSTEM DSN using the ODBC driver?
If I login from my PC using SQL Server Management Studio using a SQL account such as “sa” as long as there is a remote mapping with the correct username and password in the security of the linked server, that too works! (My guess is SQL accounts bypass any form of Windows authentication??)
I really do not want to give users access as local administrators on the Windows server as this would give them full control over Windows Server and by inheritance full rights over the SQL Server as well (as the BUILTIN\Administrators is still set in SQL).
(SQL Server 2005 is pretty much installed with default settings).
Any help would be much appreciated.
Thanks