Solved

Connect to DB2 Express via ASP (DSN-less)

Posted on 2006-10-19
22
3,422 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:brgivens
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
22 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17771934
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
0
 
LVL 7

Expert Comment

by:sachinwadhwa
ID: 17772317
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.
0
 
LVL 7

Author Comment

by:brgivens
ID: 17795867
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?
0
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 17796317
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

0
 
LVL 7

Author Comment

by:brgivens
ID: 17796491
I found the log file (thank-you) but no errors are being logged from the connection attempt.
0
 
LVL 7

Author Comment

by:brgivens
ID: 17809705
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?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17809878
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

0
 
LVL 7

Author Comment

by:brgivens
ID: 17812259
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.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17812389
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 ?
0
 
LVL 7

Author Comment

by:brgivens
ID: 17812491
I'm using Server authentication.  I'm developing on localhost.  How do I check the authentication parameters?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17812521
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
0
 
LVL 7

Author Comment

by:brgivens
ID: 17812555
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
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17812598
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 ?
0
 
LVL 7

Author Comment

by:brgivens
ID: 17812625
I tested the connection with the db2admin user.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17812742
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 ?
0
 
LVL 7

Author Comment

by:brgivens
ID: 17813026
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
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 17813062
ok
so we are getting somewhere
can you check why the iis locks ?
0
 
LVL 7

Author Comment

by:brgivens
ID: 17813155
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.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 250 total points
ID: 17813440
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 ) ?
0
 
LVL 7

Author Comment

by:brgivens
ID: 17815008
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?
0
 
LVL 7

Author Comment

by:brgivens
ID: 17816351
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.
0
 
LVL 7

Author Comment

by:brgivens
ID: 17819538
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.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question