[Mono-list] FW: Named Instances in SqlClient

Daniel Morgan danmorg@sc.rr.com
Sun, 16 Feb 2003 11:03:13 -0500
Previous message: [Mono-list] IL Assembler
Next message: [Mono-list] RE: Named Instances in SqlClient
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
This is a multi-part message in MIME format.

------=_NextPart_000_0013_01C2D5AB.00258CE0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

I committed to cvs the changes neccessary for Named Instances in SqlClient.

I used Phillip Jerkin's code for discovery of the Microsoft SQL Server TCP
port; however, I did not use any threads nor any of the error handling code.
This is because Thread.Abort does not work on Windows due to an incomplete
pthreads implementation for Windows.  I used Socket.Poll() instead to
determine if it timeouts.

I also committed changes to allow the user to use a different port.

So, the user has three ways to connect to the SQL Server:

1. hostname

   Example: "Server=myhost;Database=pubs;User
ID=myuserid;Password=mypassword"

2. hostname,port

   Example: "Server=myhost,1433;Database=pubs;User
ID=myuserid;Password=mypassword"

3. hostname\\instance

   Example: "Server=myhost\\NETSDK;Database=GrocerToGo;User
ID=myuserid;Password=mypassword"

I have tested that you can now connect to your MSDE database, such as,
NETSDK via SqlClient.

For options 1 and 2 above, I committed to cvs the changes to allow a
different port for
SybaseClient and TdsClient too.

Some notes:
- Trusted_connection nor Integrated security works
  due to Windows security is proprietary and there is
  no knowlege of how to get it to work.  Unless, someone is
  willing to help with this.  So, do not use these in
  your connection string.
- Make sure you SQL Server authenticates using SQL Server.
  By default, many MS SQL Server databases only authenticate
  using Windows.  (If you have Enterprise Manager or Visual Studio.NET,
  this can be done via Properties)
- if using MSDE, you might need to create a new user with password.  Give
  this user access to various databases in this MSDE instance.  Also, for
each
  database, give this new user at least SELECT access to the various tables
you want
  to retrieve data from. (If you have Enterprise Manager or Visual
Studio.NET,
  this can be done via Properties)

If you don't know what port your MSDE instance listens, you can find out via
the SqlPing utility that Phillip wrote.
If you MSDE instance is NETSDK and your hostname is MYHOST, you would run
SqlPing like:

mono SqlPing MYHOST\\NETSDK

You might have to search for the string "sqlmonitor" and replace with
"localhost" or something.

Many thanks go to Phillip Jerkins for the original discovery code.

-----Original Message-----
From: Jerkins, Phillip [mailto:Phillip.Jerkins@morgankeegan.com]
Sent: Wednesday, February 12, 2003 11:06 AM
To: Daniel Morgan
Subject: RE: Named Instances in SqlClient


Sounds great to me.  I just wanted the support to be in there.  I
haven't yet had time to look at your code, but I do want to see it.  I
played briefly with a different version that didn't require
Thread.Abort(), but never got anywhere.

I'm attaching the most recent version of my SqlPing.cs file.  While it
still uses threads the way my original sample did, it does a much better
job of error-handling.  You might want to test for the cases I found in
this file.  I've got exceptions defined for both.

Tim Coleman posted a few minutes ago, saying that he's posting changes
to SqlConnection concurrently with you.  Sounds like the two of you
might need to get together on that.  Just do me a favor and let me know
when the two of you have committed those changes.

Thanks!

Phil Jerkins
Phillip.Jerkins@MorganKeegan.com
x3473


-----Original Message-----
From: Daniel Morgan [mailto:danmorg@sc.rr.com]
Sent: Tuesday, February 11, 2003 5:02 PM
To: Jerkins, Phillip
Cc: Tim Coleman
Subject: RE: Named Instances in SqlClient

Hello Phillip,

The System.Environment class has OSVersion.

I know I was suppose to wait, but I got an itch and I scratched it. :-)
Thanks for the code you gave.  It would be up to Tim Coleman whether it
gets
committed to cvs or not since SqlClient is his baby.

I have attached SqlConnection.cs with the named instance discovery code
you
made.  It does not use threads, but I was itching just to try it.  It
worked, I was able to connect to MSDE.  See the test program
TestSqlConnection.cs

I have both MSDE and MS SQL Server 2000 Developer installed on my
computer,
so I used Enterprise Manager to change the properties about MSDE.  I
changed
it from Windows authentication to
Sql Server and Windows. For NETSDK, I created a user with a certain
password
and gave this new user access to various databases and SELECT access to
various tables in those databases.
Apparently, my regular SQL Server connects on 1433 while my MSDE NETSDK
connects on 1335.

So, there are three ways to connect to the server:
1. "hostname" ie. "DANPC"
2. "hostname\\instance" ie. "DANPC\\NETSDK"
3. "hostname,port"  ie. "DANPC,1335"

Anyways, this is just what I have been playing with.

Cheers,
Daniel

-----Original Message-----
From: Jerkins, Phillip [mailto:Phillip.Jerkins@morgankeegan.com]
Sent: Tuesday, February 11, 2003 4:38 PM
To: Daniel Morgan
Subject: RE: Named Instances in SqlClient


I'm perhaps halfway there.

I'm expecting to have some time this week to work on it.  I have refined
my Windows sample code considerably so that it can be dropped into place
almost without change.  I just have to get a couple hours at home to
integrate and test it.

I realize it's not that big a project (one of the reasons I decided to
do it), but time constraints have still kept me from more progress.  I
have not forgotten it, however, and will keep you up-to-date.  At the
latest it should be finished by one week from today (I get Monday off).

One question, though:  Is there any way to determine at run-time which
platform the code is executing on (Win32 vs Linux)?  Since it's
binary-portable, the compiling platform won't help.

Thanks!

Phil Jerkins


-----Original Message-----
From: Daniel Morgan [mailto:danmorg@sc.rr.com]
Sent: Tuesday, February 11, 2003 3:24 PM
To: Jerkins, Phillip
Subject: Named Instances in SqlClient

Hello Phillip,

How is that named instance stuff for SqlClient coming along?

Even if it does not work on Windows because of the Windows pthreads,
those
using Linux maybe interested.

Just curious,
Daniel


************************************************************************
****
**********************
Morgan Keegan & Co., Inc. DOES NOT ACCEPT ORDERS AND/OR
INSTRUCTIONS REGARDING YOUR ACCOUNT BY E-MAIL.  Transactional details
do not supersede normal trade confirmations or statements.  The
information
contained in this transmission is privileged and confidential. It is
intended for the use of
the individual or entity named above. The information contained herein
is
based on
sources we believe reliable but is not considered all-inclusive.
Opinions
are our current
opinions only and are subject to change without notice.  Offerings are
subject to prior
sale and/or change in price.  Prices, quotes, rates and yields are
subject
to change
without notice.  Morgan Keegan & Co., Inc., member NYSE, NASD and SIPC,
is a
registered broker-dealer subsidiary of Regions Financial Corporation.
Investments are
NOT FDIC INSURED, NOT BANK GUARANTEED and MAY LOSE VALUE.  Morgan
Keegan & Co., Inc. reserves the right to monitor all electronic
correspondence.


http://www.morgankeegan.com
************************************************************************
****
**********************

*** eSafe scanned this email for malicious content ***
*** IMPORTANT: Do not open attachments from unrecognized senders  ***


****************************************************************************
**********************
Morgan Keegan & Co., Inc. DOES NOT ACCEPT ORDERS AND/OR
INSTRUCTIONS REGARDING YOUR ACCOUNT BY E-MAIL.  Transactional details
do not supersede normal trade confirmations or statements.  The information
contained in this transmission is privileged and confidential. It is
intended for the use of
the individual or entity named above. The information contained herein is
based on
sources we believe reliable but is not considered all-inclusive. Opinions
are our current
opinions only and are subject to change without notice.  Offerings are
subject to prior
sale and/or change in price.  Prices, quotes, rates and yields are subject
to change
without notice.  Morgan Keegan & Co., Inc., member NYSE, NASD and SIPC, is a
registered broker-dealer subsidiary of Regions Financial Corporation.
Investments are
NOT FDIC INSURED, NOT BANK GUARANTEED and MAY LOSE VALUE.  Morgan
Keegan & Co., Inc. reserves the right to monitor all electronic
correspondence.


http://www.morgankeegan.com
****************************************************************************
**********************

------=_NextPart_000_0013_01C2D5AB.00258CE0
Content-Type: application/octet-stream;
	name="SqlPing.cs"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="SqlPing.cs"


using System;
using System.Collections;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;

public class HiddenOrOldServerException : Exception {
	public override string Message { get { return "The server is either =
hidden or is older than Microsoft SQL Server 2000"; } }
}
public class TcpNotSupportedException : Exception {
	public override string Message { get { return "The server is not =
configured to support TCP access."; } }
}

public class Tester {
	public static void Main() {
		Tester tester =3D new Tester();
		tester.Test();
	}
	protected int Port =3D -1;
	protected string DataSource;
	protected ManualResetEvent GetPortThreadHandle =3D new =
ManualResetEvent(false);
	protected ManualResetEvent TimeoutThreadHandle =3D new =
ManualResetEvent(false);
	public void Test() {
		string[] argv =3D Environment.GetCommandLineArgs();
		if(argv.Length < 2) {
			Console.WriteLine();
			Console.WriteLine("Usage: SqlPing <server>");
			Console.WriteLine();
		}
		DataSource =3D argv[1];
		// DataSource =3D "SQLMONITOR\\DBATOOLS";
		// DataSource =3D @"SQLMONITOR\ABC";
		string ServerName;
		int idx =3D DataSource.IndexOf("\\");
		if(idx > -1) {
			ServerName =3D DataSource.Substring(0, idx);

			Thread GetPortThread =3D new Thread(new ThreadStart(GetPort));
			Thread TimeoutThread =3D new Thread(new ThreadStart(Timeout));
			GetPortThread.Start();
			TimeoutThread.Start();
			WaitHandle.WaitAny(new WaitHandle[2] {GetPortThreadHandle, =
TimeoutThreadHandle});

			if(Port =3D=3D -1) {
				GetPortThread.Abort();
				throw new HiddenOrOldServerException();
			}
			TimeoutThread.Abort();
			if(Port =3D=3D -2) {
				throw new TcpNotSupportedException();
			}

			Console.WriteLine(Port.ToString());
		}
		Console.WriteLine();
		Console.WriteLine("Done!");
	}
	public void GetPort() {
		UdpClient sock =3D null;
		Thread.Sleep(100);
		try {
			int idx =3D DataSource.IndexOf("\\");
			string ServerName =3D DataSource.Substring(0, idx);
			string InstanceName =3D DataSource.Substring(idx+1);
			sock =3D new UdpClient(ServerName, 1434);
			ASCIIEncoding enc =3D new ASCIIEncoding();
			Byte[] rawrq =3D new Byte[InstanceName.Length+1];
			rawrq[0] =3D 4;
			enc.GetBytes(InstanceName, 0, InstanceName.Length, rawrq, 1);
			sock.Send(rawrq, rawrq.Length);

			// IPEndPoint endpoint =3D new IPEndPoint(IPAddress.Any, 0);
			IPEndPoint endpoint =3D new =
IPEndPoint(Dns.GetHostByName("sqlmonitor").AddressList[0], 0);
			Byte[] rawrs;

			rawrs =3D sock.Receive(ref endpoint);
			string rs =3D Encoding.ASCII.GetString(rawrs);

			string[] rawtokens =3D rs.Split(';');
			Hashtable data =3D new Hashtable();
			for(int i=3D0; i<rawtokens.Length/2; i++) {
				data[rawtokens[i*2]] =3D rawtokens[i*2+1];
			}
			if(!data.ContainsKey("tcp")) {
				Port =3D -2;
			} else {
				Port =3D int.Parse((string)data["tcp"]);
			}

		} catch(ThreadAbortException e) {
			sock.Close();
			throw e;
		}
		sock.Close();
		GetPortThreadHandle.Set();
	}
	public void Timeout() {
		Thread.Sleep(1000);
		TimeoutThreadHandle.Set();
	}
}


------=_NextPart_000_0013_01C2D5AB.00258CE0--
Previous message: [Mono-list] IL Assembler
Next message: [Mono-list] RE: Named Instances in SqlClient
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]