By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,448 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Create System DSN for SQL 2000 DB on remote box

P: n/a
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
Nov 16 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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
Nov 16 '05 #2

P: n/a
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

Nov 16 '05 #3

P: n/a
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


Nov 16 '05 #4

P: n/a
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
>>
>>
>
>



Nov 16 '05 #5

P: n/a
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
>>
>>
>
>



Nov 16 '05 #6

P: n/a
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
>> >>
>> >>
>> >
>> >
>>
>>
>
>
>



Nov 16 '05 #7

P: n/a
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
>> >>
>> >>
>> >
>> >
>>
>>
>
>
>



Nov 16 '05 #8

P: n/a
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
>>
>>
>
>



Nov 16 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.