Create System DSN for SQL 2000 DB on remote box

[DerekS]

Hi,
I've been pulling my hair out trying to write a simple method to
programatically create a system DSN with all parameters on a remote machine.
I have ensured I have the correct permissions on that remote machine. I
have also review many MSDN and codeproject.com articles.

Any help would be great.

Thanks,
Derek

[Hans Kesting]

DerekS wrote:

Hi,
I've been pulling my hair out trying to write a simple method to
programatically create a system DSN with all parameters on a remote
machine. I have ensured I have the correct permissions on that remote
machine. I have also review many MSDN and codeproject.com articles.

Any help would be great.

Thanks,
Derek

It *is* possible to connect without a DSN, or do you have
specific reasons for using it?

Hans Kesting

[DerekS]

Yes.. It must be created.

"Hans Kesting" <ne***********@spamgourmet.com> wrote in message
news:OQ**************@TK2MSFTNGP10.phx.gbl...

DerekS wrote:

Hi,
I've been pulling my hair out trying to write a simple method to
programatically create a system DSN with all parameters on a remote
machine. I have ensured I have the correct permissions on that remote
machine. I have also review many MSDN and codeproject.com articles.

Any help would be great.

Thanks,
Derek

It *is* possible to connect without a DSN, or do you have
specific reasons for using it?

Hans Kesting

[Nicholas Paldino [.NET/C# MVP]]

Hans,

I assume that you are calling the ConfigDSN function in ODBC.dll (I
think that is the dll)? If so, can you show the declarations, as well as
the call for the function?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DerekS" <ds********@planview.com> wrote in message
news:eO*************@TK2MSFTNGP14.phx.gbl...

Yes.. It must be created.

"Hans Kesting" <ne***********@spamgourmet.com> wrote in message
news:OQ**************@TK2MSFTNGP10.phx.gbl...

DerekS wrote:

> Hi,
> I've been pulling my hair out trying to write a simple method to
> programatically create a system DSN with all parameters on a remote
> machine. I have ensured I have the correct permissions on that remote
> machine. I have also review many MSDN and codeproject.com articles.
>
> Any help would be great.
>
> Thanks,
> Derek

It *is* possible to connect without a DSN, or do you have
specific reasons for using it?

Hans Kesting

[Nicholas Paldino [.NET/C# MVP]]

Hans,

I think I see part of the problem. Your definition for
SQLConfigDataSource should be:

[DllImport("ODBCCP32.DLL", CharSet=CharSet.Ansi)]
static extern bool SQLConfigDataSource(
IntPtr parent,
[MarshalAs(UnmanagedType.U2)
short request,
string driver,
string attributes);

You had request defined as an int, which was shifting the driver and
attributes parmaeters by two bytes.

I've also updated the definition on pinvoke.net, which was incorrect as
well.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - sh*************@hotmail.com-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
"DerekS" <ds********@planview.com> wrote in message
news:uD**************@tk2msftngp13.phx.gbl...

I've attached the class.

Here's the basic idea.

I have a simple form that gathers:

Server Name

New DSN Name

I know it SQL Server

I know the server is called 'csgsql2000'

and the user/password is 'ip'/'ip'

I know the remote server the DSN is getting created on is called 'CSAPP13'

See small attached from MSDN

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:ub*************@TK2MSFTNGP14.phx.gbl...

Hans,

I assume that you are calling the ConfigDSN function in ODBC.dll (I
think that is the dll)? If so, can you show the declarations, as well as
the call for the function?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DerekS" <ds********@planview.com> wrote in message
news:eO*************@TK2MSFTNGP14.phx.gbl...

> Yes.. It must be created.
>
> "Hans Kesting" <ne***********@spamgourmet.com> wrote in message
> news:OQ**************@TK2MSFTNGP10.phx.gbl...
>> DerekS wrote:
>> > Hi,
>> > I've been pulling my hair out trying to write a simple method to
>> > programatically create a system DSN with all parameters on a remote
>> > machine. I have ensured I have the correct permissions on that
>> > remote
>> > machine. I have also review many MSDN and codeproject.com articles.
>> >
>> > Any help would be great.
>> >
>> > Thanks,
>> > Derek
>>
>> It *is* possible to connect without a DSN, or do you have
>> specific reasons for using it?
>>
>> Hans Kesting
>>
>>
>
>

[DerekS]

Great That is getting me closer.... That you SO much. I only have one more
question. I an running this on my local machine however the 'SERVER='
parameter is now correctly showing the correct SQL server, HOWEVER it is
creating this System DSN on my local box. I actually need to remotely
create it on another server that is not the SQL server, and do so from
executing this app from my local box.

Make sense?

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:%2****************@TK2MSFTNGP11.phx.gbl...

Hans,

I think I see part of the problem. Your definition for
SQLConfigDataSource should be:

[DllImport("ODBCCP32.DLL", CharSet=CharSet.Ansi)]
static extern bool SQLConfigDataSource(
IntPtr parent,
[MarshalAs(UnmanagedType.U2)
short request,
string driver,
string attributes);

You had request defined as an int, which was shifting the driver and
attributes parmaeters by two bytes.

I've also updated the definition on pinvoke.net, which was incorrect as well.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - sh*************@hotmail.com-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);

VB Signature:
"DerekS" <ds********@planview.com> wrote in message
news:uD**************@tk2msftngp13.phx.gbl...

I've attached the class.

Here's the basic idea.

I have a simple form that gathers:

Server Name

New DSN Name

I know it SQL Server

I know the server is called 'csgsql2000'

and the user/password is 'ip'/'ip'

I know the remote server the DSN is getting created on is called 'CSAPP13'
See small attached from MSDN

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:ub*************@TK2MSFTNGP14.phx.gbl...

Hans,

I assume that you are calling the ConfigDSN function in ODBC.dll (I
think that is the dll)? If so, can you show the declarations, as well as the call for the function?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DerekS" <ds********@planview.com> wrote in message
news:eO*************@TK2MSFTNGP14.phx.gbl...
> Yes.. It must be created.
>
> "Hans Kesting" <ne***********@spamgourmet.com> wrote in message
> news:OQ**************@TK2MSFTNGP10.phx.gbl...
>> DerekS wrote:
>> > Hi,
>> > I've been pulling my hair out trying to write a simple method to
>> > programatically create a system DSN with all parameters on a remote >> > machine. I have ensured I have the correct permissions on that
>> > remote
>> > machine. I have also review many MSDN and codeproject.com articles. >> >
>> > Any help would be great.
>> >
>> > Thanks,
>> > Derek
>>
>> It *is* possible to connect without a DSN, or do you have
>> specific reasons for using it?
>>
>> Hans Kesting
>>
>>
>
>

[Nicholas Paldino [.NET/C# MVP]]

Hans,

I don't think that the ODBC APIs allow you to create DSN names on remote
machines. Because of this, you will have to create a service that runs on
the other boxes, which you can connect to through remoting, or COM+, or some
other notification mechanism, and run the code on that box.

I am curious, doesn't AD allow for something like this?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DerekS" <ds********@planview.com> wrote in message
news:OI**************@TK2MSFTNGP12.phx.gbl...

Great That is getting me closer.... That you SO much. I only have one
more
question. I an running this on my local machine however the 'SERVER='
parameter is now correctly showing the correct SQL server, HOWEVER it is
creating this System DSN on my local box. I actually need to remotely
create it on another server that is not the SQL server, and do so from
executing this app from my local box.

Make sense?

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:%2****************@TK2MSFTNGP11.phx.gbl...

Hans,

I think I see part of the problem. Your definition for
SQLConfigDataSource should be:

[DllImport("ODBCCP32.DLL", CharSet=CharSet.Ansi)]
static extern bool SQLConfigDataSource(
IntPtr parent,
[MarshalAs(UnmanagedType.U2)
short request,
string driver,
string attributes);

You had request defined as an int, which was shifting the driver and
attributes parmaeters by two bytes.

I've also updated the definition on pinvoke.net, which was incorrect

as

well.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - sh*************@hotmail.com-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver,

string

lpszAttributes);

VB Signature:
"DerekS" <ds********@planview.com> wrote in message
news:uD**************@tk2msftngp13.phx.gbl...

> I've attached the class.
>
> Here's the basic idea.
>
> I have a simple form that gathers:
>
> Server Name
>
> New DSN Name
>
> I know it SQL Server
>
> I know the server is called 'csgsql2000'
>
> and the user/password is 'ip'/'ip'
>
> I know the remote server the DSN is getting created on is called 'CSAPP13' >
> See small attached from MSDN
>
> "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
> wrote
> in
> message news:ub*************@TK2MSFTNGP14.phx.gbl...
>> Hans,
>>
>> I assume that you are calling the ConfigDSN function in ODBC.dll
>> (I
>> think that is the dll)? If so, can you show the declarations, as well as >> the call for the function?
>>
>>
>> --
>> - Nicholas Paldino [.NET/C# MVP]
>> - mv*@spam.guard.caspershouse.com
>>
>> "DerekS" <ds********@planview.com> wrote in message
>> news:eO*************@TK2MSFTNGP14.phx.gbl...
>> > Yes.. It must be created.
>> >
>> > "Hans Kesting" <ne***********@spamgourmet.com> wrote in message
>> > news:OQ**************@TK2MSFTNGP10.phx.gbl...
>> >> DerekS wrote:
>> >> > Hi,
>> >> > I've been pulling my hair out trying to write a simple method to
>> >> > programatically create a system DSN with all parameters on a remote >> >> > machine. I have ensured I have the correct permissions on that
>> >> > remote
>> >> > machine. I have also review many MSDN and codeproject.com articles. >> >> >
>> >> > Any help would be great.
>> >> >
>> >> > Thanks,
>> >> > Derek
>> >>
>> >> It *is* possible to connect without a DSN, or do you have
>> >> specific reasons for using it?
>> >>
>> >> Hans Kesting
>> >>
>> >>
>> >
>> >
>>
>>
>
>
>

[DerekS]

Not Sure...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:%2****************@TK2MSFTNGP09.phx.gbl...

Hans,

I don't think that the ODBC APIs allow you to create DSN names on remote machines. Because of this, you will have to create a service that runs on
the other boxes, which you can connect to through remoting, or COM+, or some other notification mechanism, and run the code on that box.

I am curious, doesn't AD allow for something like this?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DerekS" <ds********@planview.com> wrote in message
news:OI**************@TK2MSFTNGP12.phx.gbl...

Great That is getting me closer.... That you SO much. I only have one
more
question. I an running this on my local machine however the 'SERVER='
parameter is now correctly showing the correct SQL server, HOWEVER it is
creating this System DSN on my local box. I actually need to remotely
create it on another server that is not the SQL server, and do so from
executing this app from my local box.

Make sense?

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:%2****************@TK2MSFTNGP11.phx.gbl...

Hans,

I think I see part of the problem. Your definition for
SQLConfigDataSource should be:

[DllImport("ODBCCP32.DLL", CharSet=CharSet.Ansi)]
static extern bool SQLConfigDataSource(
IntPtr parent,
[MarshalAs(UnmanagedType.U2)
short request,
string driver,
string attributes);

You had request defined as an int, which was shifting the driver and attributes parmaeters by two bytes.

I've also updated the definition on pinvoke.net, which was incorrect

as

well.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
static extern int This function is used to work with the ODBC

Datasources
5/20/2004 2:13:16 PM - sh*************@hotmail.com-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver,

string

lpszAttributes);

VB Signature:
"DerekS" <ds********@planview.com> wrote in message
news:uD**************@tk2msftngp13.phx.gbl...
> I've attached the class.
>
> Here's the basic idea.
>
> I have a simple form that gathers:
>
> Server Name
>
> New DSN Name
>
> I know it SQL Server
>
> I know the server is called 'csgsql2000'
>
> and the user/password is 'ip'/'ip'
>
> I know the remote server the DSN is getting created on is called

'CSAPP13'

>
> See small attached from MSDN
>
> "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
> wrote
> in
> message news:ub*************@TK2MSFTNGP14.phx.gbl...
>> Hans,
>>
>> I assume that you are calling the ConfigDSN function in ODBC.dll
>> (I
>> think that is the dll)? If so, can you show the declarations, as well as

>> the call for the function?
>>
>>
>> --
>> - Nicholas Paldino [.NET/C# MVP]
>> - mv*@spam.guard.caspershouse.com
>>
>> "DerekS" <ds********@planview.com> wrote in message
>> news:eO*************@TK2MSFTNGP14.phx.gbl...
>> > Yes.. It must be created.
>> >
>> > "Hans Kesting" <ne***********@spamgourmet.com> wrote in message
>> > news:OQ**************@TK2MSFTNGP10.phx.gbl...
>> >> DerekS wrote:
>> >> > Hi,
>> >> > I've been pulling my hair out trying to write a simple method

to >> >> > programatically create a system DSN with all parameters on a

remote

>> >> > machine. I have ensured I have the correct permissions on that
>> >> > remote
>> >> > machine. I have also review many MSDN and codeproject.com

articles.

>> >> >
>> >> > Any help would be great.
>> >> >
>> >> > Thanks,
>> >> > Derek
>> >>
>> >> It *is* possible to connect without a DSN, or do you have
>> >> specific reasons for using it?
>> >>
>> >> Hans Kesting
>> >>
>> >>
>> >
>> >
>>
>>
>
>
>

[WillyX]

An other approach to this would be to directly edit the registry on the local
machine. Note: This only works for System DSN's.
Here is an example of how to create a SystemDSN for SQL Server:

#using System;
#using Microsoft.Win32
..
..
public class clsODBC
{
private string sDSNName = "";
private string sDB = "";
private string sServer = "";

public clsODBC(string sDSNName, string sDB,string sServer)
{
this.sDSNName = sDSNName;
this.sDB = sDB;
this.sServer = sServer;
}

public bool AddSystemDSN_SQLServer(string sUID, string sPWD)
{
try
{
//create the ODBC Data Source
string sDriver = System.Environment.GetEnvironmentVariable("SystemR oot")
+ @"\System32\SQLSRV32.dll";
RegistryKey oRegKey = Registry.LocalMachine;
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI",true) .CreateSubKey(sDSNName);
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\" +
sDSNName,true).SetValue("Database",sDB);
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\" +
sDSNName,true).SetValue("Driver",sDriver);
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\" +
sDSNName,true).SetValue("Server",sServer);

if(sUID.Trim() == "*") //if Windows Authentication is used
{
string sLastUser = System.Environment.GetEnvironmentVariable("USERNAM E");
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\" +
sDSNName,true).SetValue("Trusted_Connection","Yes" );
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\" +
sDSNName,true).SetValue("LastUser",sLastUser);
}
else
{
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\" +
sDSNName,true).SetValue("LastUser",sUID);
}

//make the new System DSN visible in the ODBC manager
oRegKey.OpenSubKey(@"SOFTWARE\ODBC\ODBC.INI\ODBC Data
Sources",true).SetValue(sDSNName,"SQL Server");
oRegKey.Close();

return true;
}
catch
{
return false;
}

}
}
"Nicholas Paldino [.NET/C# MVP]" wrote:

Hans,

I think I see part of the problem. Your definition for
SQLConfigDataSource should be:

[DllImport("ODBCCP32.DLL", CharSet=CharSet.Ansi)]
static extern bool SQLConfigDataSource(
IntPtr parent,
[MarshalAs(UnmanagedType.U2)
short request,
string driver,
string attributes);

You had request defined as an int, which was shifting the driver and
attributes parmaeters by two bytes.

I've also updated the definition on pinvoke.net, which was incorrect as
well.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - sh*************@hotmail.com-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
"DerekS" <ds********@planview.com> wrote in message
news:uD**************@tk2msftngp13.phx.gbl...

I've attached the class.

Here's the basic idea.

I have a simple form that gathers:

Server Name

New DSN Name

I know it SQL Server

I know the server is called 'csgsql2000'

and the user/password is 'ip'/'ip'

I know the remote server the DSN is getting created on is called 'CSAPP13'

See small attached from MSDN

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in
message news:ub*************@TK2MSFTNGP14.phx.gbl...

Hans,

I assume that you are calling the ConfigDSN function in ODBC.dll (I
think that is the dll)? If so, can you show the declarations, as well as
the call for the function?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"DerekS" <ds********@planview.com> wrote in message
news:eO*************@TK2MSFTNGP14.phx.gbl...
> Yes.. It must be created.
>
> "Hans Kesting" <ne***********@spamgourmet.com> wrote in message
> news:OQ**************@TK2MSFTNGP10.phx.gbl...
>> DerekS wrote:
>> > Hi,
>> > I've been pulling my hair out trying to write a simple method to
>> > programatically create a system DSN with all parameters on a remote
>> > machine. I have ensured I have the correct permissions on that
>> > remote
>> > machine. I have also review many MSDN and codeproject.com articles.
>> >
>> > Any help would be great.
>> >
>> > Thanks,
>> > Derek
>>
>> It *is* possible to connect without a DSN, or do you have
>> specific reasons for using it?
>>
>> Hans Kesting
>>
>>
>
>