Link Server - ODBC (Informix) - Help!

[sharpied79]

We need to access an Informix database residing on a SCO Unix server.

We were provided with a 32bit ODBC driver (part of the Esker Tun Plus Suite) by the people who look after our Informix and Sco Unix. We were advised to use this particular ODBC driver as the official IBM Informix ODBC driver as part of the SDK is not properly compatible.

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, which is also a member of the local administrators group on the Windows 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), when I try to run the same query it just freezes (sits there trying to run 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 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 section of the linked server, that too works!

I even tested this by creating a simple SQL account that had no specific rights other than what is granted by default and that too worked fine.


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.

I know I could setup the users as SQL authenticated users, but this kind of defeats the object, I would ideally like them to authenticate using Windows authentication.


Any help would be much appreciated.

Thanks.

 

[ptheriault]

What is the security mode you are using to make your connection? Self-mapping, delegation or Remote Credentials?

- Paul
- Database performance looks fine, it must be the Network!

 

[sharpied79]

Not 100% sure, I must apologise I am a bit of a newcomer when it comes to SQL full stop. Only had very limited exposure to SQL2000 and 2005.

As I mentioned in the post the issue appears to be when using a Windows authenticated account rather than a SQL account.

 

[ptheriault]

This link might offer some help. There is a section on security in it. It sounds like the domain account does not have access to the DSN. Have you tried to just add the domain account as a user on the user not and admin?

http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c08ppcsq.mspx

- Paul
- Database performance looks fine, it must be the Network!

 

[sharpied79]

Had a look at the info but it is mainly tailored to SQL Server 2005 and remote servers, which SQL Server 2005 only support as a backward compatibility.

As you mentioned it does indeed appear to be something to do with rights on the actual Windows Server regarding access to the system DSN.

If the Windows user (connecting from a remote client PC using SQL Management Studio) is a local administrator of the remote Windows Server, therby inheriting sysadmin permissions on the SQL Server as well, the query works.

Remove the user from the local administrators group on the Windows server and the query fails, even if you still allow them SYSADMIN rights in SQL Server.

Using a SQL account works perfectly whether it is a sysadmin on the SQL server or just a plain user. I can only assume that when using a SQL account to login in, it must either impersonate an account on the Windows server to access the SYSTEM DSN or bypass Windows security.

It's driving me mad and I really don't want to have to stump up £200 to Microsoft to help me out....