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/default...;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/default...;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.sqlmonster.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|PWD=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/default...b;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.sqlmonster.com
Not 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
SQLMonster.com" <forum@.SQLMonster.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/default...;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/default...;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.sqlmonster.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|PWD=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/default...b;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

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

programatically create a DSN for SQL-Server with non-standard port-number

Hi all,

I have to create a DSN for SQL-Server. Therefor I use the function "SQLConfigDataSource" from "ODBCCP32.DLL". But the problem is that the SQL-Server does not use the Standard-Portnumber 1433. I've tried to make the DSN with "SERVERNAME xxxx" where xxxx is the Portnumber, sometimes it works, but often it does not. When it does not work, then the Checkbox in the ODBC-Configuration-Tool is checked, so the connection is searching for the standard-port. How can I tell the system that it shall NOT use dynamic configuration?

Thanks for help,

Franz

Hi,

if you are using something like this : Creating DSN to SQL Server in the code

you could insert this attribute: Attributes = Attributes & "Address=190.190.200.100,1433" & Chr(0)

cu

tosc

|||

Hi!

Sorry, but this doesn't work with me. I've added this line, but then the connection was not TCP/IP but VIA ?!?

I've the following code:

sprintf(attributes, "DSN=WinCCUsr");
iLen = strlen(attributes) + 1;

sprintf(attributes + iLen, "Server=IND-FRANSCHITZ/WINCC"); //Servername
iLen += strlen(attributes + iLen) + 1;
sprintf(attributes + iLen, "Database=%s","WinCCUsr");
iLen += strlen(attributes + iLen) + 1;
sprintf(attributes + iLen, "Description=Userdaten WinCC");
iLen += strlen(attributes + iLen) + 1;
sprintf(attributes + iLen, "Trusted_Connection=yes");
iLen += strlen(attributes + iLen) + 1;
// sprintf(attributes + iLen, "Address=192.168.0.44,1148",iSqlPortNr);
sprintf(attributes + iLen, "Address=192.168.0.44");
iLen += strlen(attributes + iLen) + 1;
attributes[iLen] = '\0';

if(SQLConfigDataSource(0,ODBC_ADD_DSN,"SQL Server", attributes))
{
DSNCreated = TRUE;
printf("DSN Created\r\n");
}
else
{
printf("DSN Create Error\r\n");
AddSysErrorText("DSN Create Error");

}

Without the Address-Line the DSN is created, but with standard-port 1433.

|||

Hi,

sprintf(attributes + iLen, "Network=dbmssocn");

sprintf(attributes + iLen, "Address=192.168.0.44,1148");

__

Name Network library dbnmpntw Named Pipes dbmssocn Winsock TCP/IP dbmsspxn SPX/IPX dbmsvinn Banyan Vines dbmsrpcn Multi-Protocol (Windows RPC)

|||

Thanks a lot,

now it works. But do you know, why the entry in the ODBC-configuration-utility now is "others" and not "TCP/IP"?

|||

Hi,

I'm not sure, but it might be the network libary config "networt=dbmssocn".

CU

tosc

InsideSQL.de