Programmatically create DSN usind SQL Server Authentication

Hi
I am trying to programmatically create a DSN on a target system for SQL
Server using SQL Server Authentication.
There are documented solutions for using Windows authentication, but the
SQL Server authentication evades me.
1. Hard-coding in the registry:
Microsoft illustrates this in
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
KHLM/SOFTWARE/ODBC/ODBC.INI/<dsn-name> allows me to set LastUser, but I
have not found a way to specify the user's password in the registry (if at
all possible).
2. Windows API:
Microsoft's document HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
is off-topic for my purpose because it only shows Windows authentication.
ODBCCP32.DLL has a function SQLConfigDataSource, and that works fine,
provided that Windows Authentication is used. It does not work for SQL
Server Authentication, as documented in
http://www.droptable.com/Uwe/Forum.../1335/Problems-
setting-DSN-from-csharp-when
3. It looks like odbcconf.exe might be a possibility, with something like
odbcconf /A {CONFIGDSN "SQL Server"}
but I could not yet get it working. and I cannot find documentation or a
good working example. Adding SQL Server attributes just result in the
message "DSN Attributes are missing"
odbcconf /A {CONFIGDSN "SQL Server|DSN=Test|DATABASE=pubs|UID=nnn|PW
D=x
xx"}
4. I cannot use the RegisterDatabase function in VB as documented in
HOWTO: Set Up ODBC Data Sources When Distributing Apps
http://support.microsoft.com/defaul...kb;EN-US;123008
I need a solution I can implement in C#, C++ and Installshield's
Installscript. This solution also documents only Windows authentication.
5. Letting the user configure this manually, e.g. by starting the control
panel applet, is not an option. Using DSN-less connection options is also
out.
Can anyone tell me whether it is at all possible to programmatically create
a DSN for SQL Server, using SQL Server Authentication, and how I can
specify the UID and PWD for the user?
Thanks
Willem Semmelink
Message posted via http://www.droptable.comNot when you create the DSN programmatically. You may find
some old references on how to do it creating keys in the
registry but the driver will ignore those. From the
application side, you'd have to pass in the user id and
password in the connection string or from the application
that references the DSN.
-Sue
On Wed, 02 Feb 2005 12:48:34 GMT, "Willem Semmelink via
droptable.com" <forum@.droptable.com> wrote:

>Hi
>I am trying to programmatically create a DSN on a target system for SQL
>Server using SQL Server Authentication.
>There are documented solutions for using Windows authentication, but the
>SQL Server authentication evades me.
>1. Hard-coding in the registry:
>Microsoft illustrates this in
>HOWTO: Programmatically Create a DSN for SQL Server with VB
>http://support.microsoft.com/defaul...b;EN-US;q184608
> KHLM/SOFTWARE/ODBC/ODBC.INI/<dsn-name> allows me to set LastUser, but I
>have not found a way to specify the user's password in the registry (if at
>all possible).
>2. Windows API:
>Microsoft's document HOWTO: Create and Remove a DSN in Visual Basic
>http://support.microsoft.com/defaul...b;EN-US;q171146
>is off-topic for my purpose because it only shows Windows authentication.
>ODBCCP32.DLL has a function SQLConfigDataSource, and that works fine,
>provided that Windows Authentication is used. It does not work for SQL
>Server Authentication, as documented in
>http://www.droptable.com/Uwe/Forum.../1335/Problems-
>setting-DSN-from-csharp-when
>3. It looks like odbcconf.exe might be a possibility, with something like
>odbcconf /A {CONFIGDSN "SQL Server"}
>but I could not yet get it working. and I cannot find documentation or a
>good working example. Adding SQL Server attributes just result in the
>message "DSN Attributes are missing"
>odbcconf /A {CONFIGDSN "SQL Server|DSN=Test|DATABASE=pubs|UID=nnn|PW
D=
xxx"}
>4. I cannot use the RegisterDatabase function in VB as documented in
>HOWTO: Set Up ODBC Data Sources When Distributing Apps
>http://support.microsoft.com/defaul...kb;EN-US;123008
>I need a solution I can implement in C#, C++ and Installshield's
>Installscript. This solution also documents only Windows authentication.
>5. Letting the user configure this manually, e.g. by starting the control
>panel applet, is not an option. Using DSN-less connection options is also
>out.
>Can anyone tell me whether it is at all possible to programmatically create
>a DSN for SQL Server, using SQL Server Authentication, and how I can
>specify the UID and PWD for the user?
>Thanks
>Willem Semmelink