Create System DSN for SQL 2000 DB on remote box

  • Thread starter Thread starter DerekS
  • Start date Start date
D

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
 
DerekS said:
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
Click to expand...

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

Hans Kesting
 
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?
 
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]
- (e-mail address removed)
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - (e-mail address removed)-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
DerekS said:
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

in
message news:[email protected]...
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]
- (e-mail address removed)

DerekS said:
Yes.. It must be created.

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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]
- (e-mail address removed)
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - (e-mail address removed)-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
DerekS said:
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

in
message news:[email protected]...
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]
- (e-mail address removed)

Yes.. It must be created.

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
Click to expand...
Click to expand...
Click to expand...
 
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]
- (e-mail address removed)

DerekS said:
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?

in
message news:%[email protected]...
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]
- (e-mail address removed)
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - (e-mail address removed)-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
DerekS said:
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]" <[email protected]>
wrote
in
message 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]
- (e-mail address removed)

Yes.. It must be created.

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
Click to expand...
Click to expand...
Click to expand...
 
Not Sure...

Nicholas Paldino said:
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]
- (e-mail address removed)

DerekS said:
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?

in
message news:%[email protected]...
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
Click to expand...
incorrect
as
well.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - (e-mail address removed)-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
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]" <[email protected]>
wrote
in
message 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
Click to expand...
well
as
the call for the function?


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Yes.. It must be created.

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
Click to expand...
Click to expand...
Click to expand...
 
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("SystemRoot")
+ @"\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("USERNAME");
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 said:
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]
- (e-mail address removed)
static extern int This function is used to work with the ODBC Datasources

5/20/2004 2:13:16 PM - (e-mail address removed)-131.107.3.92
SQLConfigDataSource (int hwndParent, int fRequest, string lpszDriver, string
lpszAttributes);

VB Signature:
DerekS said:
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

in
message news:[email protected]...
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]
- (e-mail address removed)

Yes.. It must be created.

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top