Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3449
  • Last Modified:

Connect to DB2 Express via ASP (DSN-less)

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
brgivens
Asked:
brgivens
  • 12
  • 9
1 Solution
 
momi_sabagCommented:
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
 
sachinwadhwaCommented:
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
 
brgivensAuthor 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?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
momi_sabagCommented:
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
 
brgivensAuthor Commented:
I found the log file (thank-you) but no errors are being logged from the connection attempt.
0
 
brgivensAuthor 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?
0
 
momi_sabagCommented:
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
 
brgivensAuthor 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.
0
 
momi_sabagCommented:
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
 
brgivensAuthor Commented:
I'm using Server authentication.  I'm developing on localhost.  How do I check the authentication parameters?
0
 
momi_sabagCommented:
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
 
brgivensAuthor 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
0
 
momi_sabagCommented:
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
 
brgivensAuthor Commented:
I tested the connection with the db2admin user.
0
 
momi_sabagCommented:
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
 
brgivensAuthor 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
0
 
momi_sabagCommented:
ok
so we are getting somewhere
can you check why the iis locks ?
0
 
brgivensAuthor 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.
0
 
momi_sabagCommented:
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
 
brgivensAuthor 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?
0
 
brgivensAuthor 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.
0
 
brgivensAuthor 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.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now