We help IT Professionals succeed at work.

Connect to DB2 Express via ASP (DSN-less)

brgivens
brgivens used Ask the Experts™
on
Hi,

I'm having quite a time trying to connect to my newly-installed DB2 Express database via ASP.  Here's my connection string:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={IBM DB2 ODBC DRIVER};Hostname=localhost;Database=SAMPLE;UID=db2admin;PWD=<password>"

The resulting error:

Specified driver could not be loaded due to system error 5


The ASP page and the database reside on the same box.  I've added db2admin and the IUSR account to the list of DB Users for the SAMPLE database and given both those accounts full access to the DB2 directory (and cascaded permissions through sub-folders).

TIA
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
hi
did you catalog the database in the odbc manager of windows ? (you can do it by going to the control panel, then administrative tools then odbc)
you have to add it there first if you want to use it

try to use this connection string
str = "Provider=ibmdadb2; DSN=sample; UID=userid; PWD=password"

momi
It is best to use following command to create DSN for db2 databases:

CATALOG [SYSTEM/USER] ODBC DATA SOURCE <<Database Name>>

http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/core/r0002030.htm

Read this Microsoft Tecnote of System error 5:

http://support.microsoft.com/kb/883842


and I would also check event logs and see if there are any security errors.

Author

Commented:
I created a System DSN for the database and I am able to successfully test the connection within the ODBC Administrator.  When I attempt to connect within ASP, I still get the "system error 5" message.  The connection string I used for the System DSN is:

"DSN=SAMPLE;UID=db2admin;PWD=<password>"

I cannot use the IBMDADB2 provider as it is not installed.

How do I check for security errors?
hi

 search for a file named db2diag.log (it should be under /program files/ibm/sqllib/DB2 unless you are not using all the defaults) and maybe you'll see messages there

Author

Commented:
I found the log file (thank-you) but no errors are being logged from the connection attempt.

Author

Commented:
Update:

I found that granting Read, Write, List & Execute privileges to the IUSR account on the /program files/ibm/sqllib folder and its subfolders got me past the system error 5 problem.  The error message is now "Catastrophic Failure".  I assume that there's still some sort of problem with permissions as I can connect to the database through the ODBC Administrator, but attempting to connect in ASP via a system DSN also results in the "Catastrophic Failure".  In an attempt to just get past this problem, I've granted Full Control to IUSR, IWAM & db2admin on /db2, /ibm & /program files/ibm/sqllib (cascaded through all subfolders) to no avail.

Any ideas?
hi

did you add the user you are tring to connect with to the user group under the windows user&groups administration ?
the user should have the connect privilege

Author

Commented:
I'm attempting to connect with the db2admin user.  DB2 added this user to Windows during setup and I have given the user Full Control to the folders listed above.

Another Update:

I found this MS kb article: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q827559 and implemented the workaround - adding IWAM, IUSR & db2admin to the Impersonate a client after authentication policy and issuing gpupdate /force & iisreset.  This did not solve the problem, but is perhaps in the right direction.
hi

what method of authentication are you using ?
what is the value of the authentication parameter on the client machine and on the server machine ?

Author

Commented:
I'm using Server authentication.  I'm developing on localhost.  How do I check the authentication parameters?
you open a command prompt (start->run-> db2cmd.exe)

you enter this command :
db2 get dbm cfg

and in the output look for the AUTHENTICATION parameter

momi

Author

Commented:
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO
in that case, when you use server authentication, then you should not try to solve your problems with microsoft kerberos

when you test the odbc connection, which user are you using ?

Author

Commented:
I tested the connection with the db2admin user.
what happens if you try to connect using odbc from another application, for example microsoft excel ?

you can try to import data using database query and try connect through the odbc system dsn you created
can you try it ?

Author

Commented:
Excel didn't like the system DSN I had created but I was able to connect with this connection string:

Provider=IBMDADB2.DB2COPY1;Persist Security Info=False;Data Source=CLINIC;Mode=ReadWrite

I tried this string in ASP:

Provider=IBMDADB2.DB2COPY1;Data Source=CLINIC;User ID=db2admin;Password=<passowrd>;Mode=ReadWrite

but the page locks up - it locks IIS up and I have to do an iisreset to get it back
ok
so we are getting somewhere
can you check why the iis locks ?

Author

Commented:
I turned on logging in IIS and verified that it does create a log entry for the {IBM DB2 ODBC DRIVER} connection string but it does not log anything before hanging on the IBMDADB2 connection string.
sorry, but i don't think i will be able to help you from here
i don't think the problem is with db2 since you did manage to connect using odbc through excel
have you tried to connect using microsoft odbc driver for db2 ?
or maybe some other odbc driver and some other database (like oracle ) ?

Author

Commented:
I appreciate your effort.  I had no problem setting up my application to work with Oracle, SQL Server, MySQL & Access.  Well, Oracle was a bit of a pain, but nothing like this.

I don't have the Microsoft ODBC Driver for DB2 installed.  I downloaded the ODBC driver from IBM's site, but it doesn't come with an installer or even a readme file - any idea on how to install?

Author

Commented:
Apparently the Microsoft ODBC Driver for DB2 is only available with Microsoft Host Integration Server.  Please let me know if you are aware of it being available elsewhere.

I eventually learned how to install the driver I had downloaded from IBM's site.  It turns out that it is IBM's driver and not Microsoft's.

Author

Commented:
The solution turned out to be adding the IUSR account to the Windows local user group DB2USRS (Adiministrative Tools -> Computer Management -> Local Users and Groups -> Groups -> DB2USRS -> right-click -> Add to Group -> Add IUSR_computername):

http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?nav=false&forum=805&thread=140289&start=0&msRange=15&cat=19&message=13882968#13882968

Thanks to Keith Eberle for the solution.

Points to momi in appreciation of your effort.