Linked Server Errors linking to MS-SQL 2005

I have successfully created an OLE DB connection (linked server) from an MS-SQL 2005/2008 Server to our HP-UX/Informix server to using the INFORMIX user and Infxoledbc provider. I can open the linked server connection, see the views I need and do a select query on them to return the proper data sets. However, I am trying to increase security by not using the INFORMIX user, so I have created a user on the HP-UX called ‘mssqluse’ and has been added to the INFORMIX Group. The user has been granted connect privileges and the appropriate ‘select’ privileges on the databases\\\\views needed. When I create a linked server using the security options of remote login and password from ‘mssqluse’ instead of INFORMIX, the link is successfully established, I can drill down to the views and see them and I can script the ‘select’ statement to retrieve the data. But when I go to retrieve the data, I get the following errors: OLE DB provider "Ifxoledbc" for linked server "LINKED SERVER NAME" returned message "EIX000: (-111) ISAM error: no record found.". OLE DB provider "Ifxoledbc" for linked server "LINKED SERVER NAME" returned message "EIX000: (-387) No connect permission. (sysusers)". I am assuming that this is an errant error message since the user ‘mssqluse’ has connect privileges to the INFORMIX database. Out of attempts at solving this error, I also gave him connect privileges to the ‘sysusers’ database with negative results. What makes this more bizarre is that this connection does work from my desktop server (the only difference being is that I am using MS-SQL 2008 on my desktop as opposed to MS-SQL 2005 in Q&A and Production). I have found nothing on the web to chase down this error and have made several posts to resolve with no answers as of yet. Although this is not ‘absolutely’ critical, I would like to get away from using the INFORMIX user for obvious security reasons. Does anybody have any suggestions? Thank you for your assistance in advance.

I don't see how this can make a difference, but you should specify the Informix engine version you're using and the client versions you're using (on your desktop and your prod/qa environment). You should also specify the logging mode of your databases (ANSI?) The database locale and DB_LOCALE settings would be nice also... And you definetly should NOT put that user into the "informix" group. Unless of course you configured your instance to be accessible only by "informix" group members (which doesn't make much sense). But to be honest I currently don't have a clue on what the problem may be... A quick search found only two inconclusive cases (customers closed them because the problem didn't happen again). Apparently both were with V9.x... If you find a way, please make sure the user is not being used in UPPERCASE... You could of course setup the engine to generate an AF when it finds the error -387, but that could also generate shared memory dumps (depending on the instance configuration), but I would not do that in production and without first reporting it to tech support. Maybe they have some clue... Regards. On Tue, Feb 23, 2010 at 11:03 PM, BRIAN JACOBS <bjacobs@co.fairbanks.ak.us>wrote: > I have successfully created an OLE DB connection (linked server) from an > MS-SQL 2005/2008 Server to our HP-UX/Informix server to using the INFORMIX > user and Infxoledbc provider. I can open the linked server connection, see > the > views I need and do a select query on them to return the proper data sets. > However, I am trying to increase security by not using the INFORMIX user, > so I > have created a user on the HP-UX called ‘mssqluse’ and has been added to > the > INFORMIX Group. The user has been granted connect privileges and the > appropriate ‘select’ privileges on the databases\\\\views needed. > When I create a linked server using the security options of remote login > and > password from ‘mssqluse’ instead of INFORMIX, the link is successfully > established, I can drill down to the views and see them and I can script > the > ‘select’ statement to retrieve the data. But when I go to retrieve the > data, I > get the following errors: > > OLE DB provider "Ifxoledbc" for linked server "LINKED SERVER NAME" returned > message "EIX000: (-111) ISAM error: no record found.". > OLE DB provider "Ifxoledbc" for linked server "LINKED SERVER NAME" returned > message "EIX000: (-387) No connect permission. (sysusers)". > > I am assuming that this is an errant error message since the user > ‘mssqluse’ > has connect privileges to the INFORMIX database. Out of attempts at solving > this error, I also gave him connect privileges to the ‘sysusers’ database > with > negative results. What makes this more bizarre is that this connection does > work from my desktop server (the only difference being is that I am using > MS-SQL 2008 on my desktop as opposed to MS-SQL 2005 in Q&A and Production). > I > have found nothing on the web to chase down this error and have made > several > posts to resolve with no answers as of yet. Although this is not > ‘absolutely’ > critical, I would like to get away from using the INFORMIX user for obvious > security reasons. Does anybody have any suggestions? Thank you for your > assistance in advance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016367fb6c100b65604804d4806

Did you execute coledbp.sql on the IDS server? If not, look for it in the etc directory of your CSDK installation. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Tuesday, February 23, 2010 5:55 PM > To: ids@iiug.org > Subject: Re: Linked Server Errors linking to MS-SQL 2005 [19105] > > I don't see how this can make a difference, but you should specify the > Informix engine version you're using and the client versions you're > using > (on your desktop and your prod/qa environment). > You should also specify the logging mode of your databases (ANSI?) > The database locale and DB_LOCALE settings would be nice also... > > And you definetly should NOT put that user into the "informix" group. > Unless > of course you configured your instance to be accessible only by > "informix" > group members (which doesn't make much sense). > > But to be honest I currently don't have a clue on what the problem may > be... > A quick search found only two inconclusive cases (customers closed them > because the problem didn't happen again). Apparently both were with > V9.x... > > If you find a way, please make sure the user is not being used in > UPPERCASE... > You could of course setup the engine to generate an AF when it finds > the > error -387, but that could also generate shared memory dumps (depending > on > the instance configuration), but I would not do that in production and > without first reporting it to tech support. Maybe they have some > clue... > > Regards. > > On Tue, Feb 23, 2010 at 11:03 PM, BRIAN JACOBS > <bjacobs@co.fairbanks.ak.us>wrote: > > > I have successfully created an OLE DB connection (linked server) from > an > > MS-SQL 2005/2008 Server to our HP-UX/Informix server to using the > INFORMIX > > user and Infxoledbc provider. I can open the linked server > connection, see > > the > > views I need and do a select query on them to return the proper data > sets. > > However, I am trying to increase security by not using the INFORMIX > user, > > so I > > have created a user on the HP-UX called 'mssqluse' and has been added > to > > the > > INFORMIX Group. The user has been granted connect privileges and the > > appropriate 'select' privileges on the databases\\\\views needed. > > When I create a linked server using the security options of remote > login > > and > > password from 'mssqluse' instead of INFORMIX, the link is > successfully > > established, I can drill down to the views and see them and I can > script > > the > > 'select' statement to retrieve the data. But when I go to retrieve > the > > data, I > > get the following errors: > > > > OLE DB provider "Ifxoledbc" for linked server "LINKED SERVER NAME" > returned > > message "EIX000: (-111) ISAM error: no record found.". > > OLE DB provider "Ifxoledbc" for linked server "LINKED SERVER NAME" > returned > > message "EIX000: (-387) No connect permission. (sysusers)". > > > > I am assuming that this is an errant error message since the user > > 'mssqluse' > > has connect privileges to the INFORMIX database. Out of attempts at > solving > > this error, I also gave him connect privileges to the 'sysusers' > database > > with > > negative results. What makes this more bizarre is that this > connection does > > work from my desktop server (the only difference being is that I am > using > > MS-SQL 2008 on my desktop as opposed to MS-SQL 2005 in Q&A and > Production). > > I > > have found nothing on the web to chase down this error and have made > > several > > posts to resolve with no answers as of yet. Although this is not > > 'absolutely' > > critical, I would like to get away from using the INFORMIX user for > obvious > > security reasons. Does anybody have any suggestions? Thank you for > your > > assistance in advance. > > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --0016367fb6c100b65604804d4806 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.