Solved

Connect to DB2 Express via ASP (DSN-less)

Posted on 2006-10-19
22
3,376 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now