Connection string information

[Francois Malgreve]

Hi,

I would like to know how I can do to retrieve the user and password from a
connection object.
All i see from SQLConnection is that i can access the connectionString but I
need to be able to access the specific user and password for that
connection.
If I parse the connection string myself i may not be able to catch the
values properly every times as there are many valid syntax to build a
connection string and I am pretty sure that I do not know all of them.
For example
"Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
is valid but
"Data Source=Aron1;Initial Catalog=pubs;User Id = sa;Password = asdasd;"
"Data Source=Aron1;Initial Catalog=pubs;uid=sa;Password =asdasd;"
"Data Source=Aron1;Initial Catalog=pubs;uid =sa;pwd=asdasd;"
and probably other syntaxes are valid.

I assume that internally the SqlConnection object parses the connection
string and store the user and password in separate fields. Is there any
public method or attribuate that can do that? Or any third party library?

Best regards,

Francois Malgreve.

 

[Scott M.]

The connection object does not parse the connection string, nor does it even
look at it. It just stores it and passes it to its connection source (the
database). It is up to the database to parse the string.

All you are really talking about here is a string that needs parsing on
different values at different times, but standard string parsing applies.
Also, most connection strings wouldn't even have the user name and password
included in the string anyway, so trying to pull that info. out may prove to
be pointless.

-Scott

 

[Francois Malgreve]

Hi Scott,

I am sorry but if the connection object does not parse the connection
string, how does it know to which server to connect to as the server
instance name is specified inside the connection string? It must parse the
connection string to at least now to what datasource (DB) to connect to!

Or maybe we have different understanding of data source. When you speak
about datasource, do you mean an SQL server instance? or then some kind of
connector within the .net framework? For me the data source is the SQL
Server.

Francois

 

[William \(Bill\) Vaughn]

Unless the ConnectionString contains "Persist Security Info=true"
referencing the ConnectionString assigned to a Connection won't return the
credentials. If it is enabled, you'll have to do a string parse on the
ConnectionString for the values. When ADO.NET assigns the ConnectionString
to a Connection object, it parses the string and picks off key elements
which are assigned to the Connection properties behind the scenes. Not all
of the key/value pairs are used to set properties--the user id and password
are not assigned to properties.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Scott M.]

As Bill states, only the most pertinent data is taken from the string (ie.
what data source to attempt to connect to), the rest is not looked at by the
connection object, it is just passed to the data source for it to use.

 

[Francois Malgreve]

Well, I just decompiled the SQLConnection class.
I saw a lot of properties definned such as:

private string _userID;
private string _password;

internal string UserID
{
get
{
return _userID;
}
}

internal string Password
{
get
{
return _password;
}
}

Also I saw those values being assigned in some places:

I also saw that there was a parser in the class SQLConnectionString
inherited from DBConnectionString but my decompiler was not good enough to
decompile it completely. I can more or less read it but I cannot copy paste
it in my own class to be able to re-use it

It seemed that the parser stored EVERY value found from the conection string
into an Hahstable, as long as the value exists, otherwise the value will be
kept blank. Also I did not see anything that makes the parser skip some
elements. If an element exists within the connection string it parses it,
except if that element is not a proper keyword then an exception is thrown
(such as u put in passeword instead of password)
At the end of the constructor of DBConnectionString, a method
ValidateParse() is called and that method is overridden in
SQLConnectionString to set values for all properties that are in the
connection string (including passwords, user name and anything u can define
into a connection string). Of course if a property is not defined in the
connection string then the equivalent property in SQLConnectionString will
remain empty.

Well, do you have a properly decompiled version of the System.Data.dll that
you can actually recompile? I believe MS still does not publish the source
code of the Framework API, do they?
And if I could run it i am pretty sure i could retrieve the user name
password.

Last, sorry in advance to be so stubborn.

Francois.

 

[William \(Bill\) Vaughn]

Right. And they are private--not exposed by ADO.NET

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Francois Malgreve]

Well I managed to reproduce the parser from SqlConnectionString and it
works. And it seems that MS saw that these kind of features were missing as
in ADO.NET 2 these properties are exposed.

Well to conclude, the believing that those values were not parsed was wrong.
Well about how the connection is actually made from .Net to SQL Server, if
you have some links mentioning about that, I would be glad to read about it
to know how it actually works. In the past I really did not believe that the
connection string was sent to the SQL Server but since your post, I am not
sure about it anymore. And I would tend to believe you as you seem to be
know more than me about it but I would like to read about it more
extensively. Then I could finally fully understand what's going on in there.

Thank you for your help.
Best regards,

Francois.

 

[William \(Bill\) Vaughn]

I don't expect that the connection string is sent to the server. There's no
need. However, the elements (key/value pairs) ARE used to choose the
provider (as in OleDb or Odbc), point to a specific server (which sets up a
named pipe or TCP connection search), provide authentication credentials
(user id and password or Windows credentials) which are sent to the server
for validation, specify a default database which is also sent to the server
to set the initial catalog. We've been connecting to SQL Server for many
years and the mechanisms are about the same as always (at least at the
lowest levels). The connection pooling mechanism is not that new (it was
implemented in ODBC many years ago and carried into OLE DB). Yes, the highly
evolved ADO.NET version is more sophisticated and more easily controlled.

My question is, what problem are you trying to solve? Wanting to know how
stuff works is an interesting intellectual exercise that can help understand
how to use a mechanism but if there is a specific issue that needs fixing,
what is it?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Francois Malgreve]

Well I just wanted to understand how things work to prove to myself that
indeed all the connection string needs to be parsed. I just could not
believe the first posts about the connection string being sent to a
DataSource.

For the original question of this post, I just need to retrieve user and
password from a connection string that I pass as global variables to a DTS
that I run from a VB.NET application.

Also of course I do not need to know all the internals of everything just
for the sake of it and I sure do not have the time neither the desire for
it. It's just that sometimes people need to do stuff that is not exposed by
any API and in that case you may have to dig in, decompile the framework,
and provide those features by yourself. Many years ago in the early days of
Java it was a common practice when you needed to do something that was not
provided in java 1.0 / 1.1. The first versions of the API was not that big
neither bug free. But at least Sun gave the source code. I wish it would be
the same with .Net, it would be much easier. Because decompilers do not
always work well and all the documentation is missing. That is slowing down
my productivity as well. I am just a programmer and I have a deadline to
meet. Anyway that's an other topic...

Thanks again for your help,

Best regards,

Francois