1,105,391 Community Members

Making an ODBC Application

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Correction! Code above is for a Trusted Connection - using Windows Security, having difficulty myself with SQL security

Member Avatar
TnTinMN
Practically a Master Poster
640 posts since Jun 2012
Reputation Points: 418 [?]
Q&As Helped to Solve: 152 [?]
Skill Endorsements: 16 [?]
 
0
 

Good job John!

I never thought to to search for an api function to create a user DSN (Doh!) and have done it by directly modifying the the registry.

For those interested in the registry route, I have an example for creating/deleting a Dbase DSN at this link:

Click Here

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Thanks - but... I can't take that prop, the OP found it, I'm just trying to get the SQL Auth to work - Windows Security works like a champ, but not so much with SQL Security

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

I am unable to find any documentation stating UID or PWD are valid Key/Value pairs (attributes) in SQLConfigDataSource().

I can change my attributes to use TRUSTED_CONNECTION=NO and as long as I do not pass the UID & PWD pairs - I still get the ODBCAD32 dialog. As soon as I try to pass those pairs, the connection returns an error.

My best suggestion is to use Windows Authentication, or use the ODBCAD32 dialog to input the UserID and Password for the connection (That does work!)

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

FWIW, the key/value pairs are shown as a byte[] array in a SQL Trace... :\

Member Avatar
androidz
Junior Poster
158 posts since Aug 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

sorry for the late reply been busy this following days. Anyways I tried the code it only works for the windows configuration. Maybe there is a substitute prop for UID and PWD hope there is :)

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Use Trusted_Connection = NO, leave off UID and PWD, invoke the dialog and go from there.

I would use Windows Security and make SQL User Groups instead, good luck.

Member Avatar
androidz
Junior Poster
158 posts since Aug 2010
Reputation Points: -2 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

what do you mean by use windows security and make sql user groups?yes i tried already trusted connection = NO but still it wont work.

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

what do you mean by use windows security and make sql user groups

I mean, use TRUSTED_CONNECTION=YES, that will force the DSN to use Windows Security (your users' windows login identity), then you do not have to pass the UID & PWD pair - which is apparently not supported by SQLConfigDataSource().

Alternatively, if you have to use SQL Security (users are setup individually in SQL Server with UID & PWD) - you use the Connection Attribute setup as detailed here, but change the first parameter from NULL to Me.Handle (as detailed in the code comments) to invoke the ODBCAD32 dialog to input username (UID) and password (PWD).

I've also found some sample code here at MSDN that uses SQLDriverConnect() to connect to a SQL Server - that API does prompt for a Username and Password if not supplied. I just ran that sample (C++ code) and am evaluating it to determine whether I can trim it down to just the UID & PWD prompt, then import it as a VB project reference (compiled as C++ DLL).

Member Avatar
john.knapp
Posting Whiz in Training
221 posts since Oct 2012
Reputation Points: 25 [?]
Q&As Helped to Solve: 22 [?]
Skill Endorsements: 4 [?]
 
0
 

Bump... SQLDriverConnect looks to be the way to go.

SQLConfigDataSource() will not accept the UID/PWD key value pair, and in fact will not even show the ODBCAD32 dialog if you try to pass in even just the UID.

The example code at MSDN, mentioned in the previous post here, does prompt for UID & PWD, which is what we need. Be aware however, you will not be able to save those values in the DSN.

The ODBC API documents some other methods as well, including at least one that will write to ODBC.ini

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: