DB2 ODBC driver for windows & security permissions, IM004 error

prz_
prz_ used Ask the Experts™
on
Hi,

I have a .NET web app on a windows 2003 server that connects to several different databases, including DB2 and MSSQL. The DB2 connection is using IBM's ODBC driver for windows and the MSSQL uses ADO.NET. The problem is that the MSSQL connection is supposed to use a service account, which is accomplished by having the line
<identity impersonate="true" userName="domain\id" password="password"/>
in the web.config file, and when I use this security configuration I get an error when trying to connect to the DB2 database.
ERROR [IM004] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
Not using the specific domain account (just using <identity impersonate="true"/>) allows me to access DB2 just fine, but then I don't have the security permissions to access MSSQL.
I haven't been able to dig up a lot on this error for DB2, though it cropps up plenty for Oracle when I search - I've seen some indicaitons that it's something to do with local permissions and that could be related to what I'm seeing, but I haven't found any specifics. What level of local access does the driver require? Or is this caused by something else?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi
if you want to check if the problem is caused by local premission (although it sounds weird) you can try to use it when connected as the administrator account
where does the db2 you connect to reside ?

Commented:
which db2 client level did you install, version 8 or 9?

Commented:
Net.Data, part of the DB2(R) Universal Database and DB2 Connect family, is a set of application development tools designed to help you create and maintain web-based transaction applications. You can use Net.Data(R) to access and alter data stored on a DB2 UDB for Windows(R) NT and Windows 2000.

DB2 .NET Data Provider
------------------------
 The DB2(R) .NET Data Provider extends DB2 support for the ADO.NET interface. The DB2.NET Data Provider delivers high-performing, secure access to DB2 data.

The DB2 .NET Data Provider allows your .NET applications to access the following database management systems:  
      DB2 Universal Database(TM) Version 8 for Windows(R), UNIX(R), and Linux-based computers
      DB2 Universal Database Version 6 (or later) for OS/390(R) and z/OS(TM), through DB2 Connect(TM)
      DB2 Universal Database Version 5, Release 1 (or later) for AS/400(R) and iSeries(TM), through DB2 Connect
      DB2 Universal Database Version 7.3 (or later) for VSE & VM, through DB2 Connect
To develop and run applications that use DB2.NET Data Provider you need the .NET Framework, Version 1.0 or 1.1.

 In addition to the DB2 .NET Data Provider, there is also a collection of add-ins to the Microsoft(R) Visual Studio .NET IDE. These add-ins simplify the creation of DB2 applications that use the ADO.NET interface. You can also use these add-ins to develop server-side objects, such as SQL stored procedures and user-defined functions.

 Sample applications in VB.NET and C#.NET demonstrating the DB2 .NET Data Provider are available at:
  http://www.ibm.com/software/data/db2/udb/ad/v8/samples.html

ODBC .NET Data Provider
-----------------------
The ODBC .NET Data Provider makes ODBC calls to a DB2(R) data source using the DB2 CLIDriver. Therefore, the connection string keywords supported by the ODBC.NET Data Provider are the same as those supported by the DB2 CLI driver. Also, the ODBC.NET Data Provider has the same restrictions as the DB2 CLI driver. There are additional restrictions for the ODBC .NET Data Provider, which are identified in the topic: ODBC.NET Data Provider restrictions.

In order to use the ODBC.NET Data Provider, you must have the .NET Framework Version 1.1 installed. For DB2 Universal Database for AS/400(R) and iSeries(TM), the following fix is required on the server: APAR II13348.

The following are the supported connection keywords for the ODBC.NET Data Provider:
Table 13. ConnectionString keywords for the ODBC.NET Data Provider

 Keyword        Value                Meaning  
---------            -------              ------------------
 DSN           database alias                    The DB2 database alias as cataloged in the database       directory
 UID             user ID              The user ID used to connect to the DB2 server    
 PWD            password              The password for the user ID used to connect to the DB2                   server    

The following is an example of creating an OdbcConnection to  connect to the SAMPLE database:

[Visual Basic .NET]
 Dim con As New OdbcConnection("DSN=sample;UID=userid;PWD=password;")
 con.Open()
 
[C#]
 OdbcConnection con = new OdbcConnection("DSN=sample;UID=userid;PWD=password;");
 con.Open()

regards
Intakhab
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Author

Commented:
I'm using version 8 of the driver, and the DB2 database is on another machine in a data centre somewhere.

The problem also appears to be less consistent than I initially thought. It now appears only sometimes when I use the specific domain account for my app (never when I just use user impersonation, so far) and I cannot reliably reproduce it. I'm trying to find out if something's being done to the server that just coincides with my changes...

Commented:
i dont think db2 can authenticate a service account, if by service account you mean 'Local service'

Author

Commented:
"Service account" is the term that's kicked around here - wat it means in practice is that my web app needs to run under a specific domain account that has the rights to access the Microsoft SQL server I need to use. This is accomplished by having the following line in web.config:
   <identity impersonate="true" userName="ourdomain\sql_id" password="password"/>
The account was given local permissions on the web server using aspnet_regiis. This part works as expected and should have nothing to do with the DB2 ODBC dirver.

However...

When I run in that security configuration, connecting to the DB2 database that I need to use causes a driver error mentioned above:
   ERROR [IM004] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
After a few occurrances of this error, subsequent attempts to connect will hang and will continue to do so until, it seems, the server is rebooted.
The DB2 conneciton does not use any windows security. Here's the connection string:
   Provider=IBMDADB2;DSN=ourdb2dsn;Uid=db2id;Pwd=password;
This has worked fine during development for months with the generic security configuraiton in web.config:
   <identity impersonate="true" />

Commented:
go to control panel, admin tools, data sources, system dsn, select the db name you are tryint to connect to, choose configure, enter the user id and password and select connect. Can you connect like that?
Commented:
Rats, I should've updated this question sooner. We figured it out eventually, although it took forever because the IIS people weren't all over the DB2 requirements whereas the DB2 people weren't all over the IIS needs. So, it turns out that the DB2 driver installation creates its own local user groups that have relevant access to the needed files and adding the domain ID our app uses to this one local group (DB2USERS) solved all issues and we had no more problems.

Thanks to all who tried to help!

Commented:
Closed, 250 points refunded.
Vee_Mod
Community Support Moderator

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial