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

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

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

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!)

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

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.

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).

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