Howto obtain the credentials from a SqlConnection object

[Friso Wiskerke]

Hi all,

In the ConnectionString property you can set the UserName and Password to be
used for the SqlConnection as well as the DataSource and Initial Catalog
(and some more possible values). There are properties on the SqlConnection
object which return the DataSource and the Database provided in the
ConnectionString but there are no such properties for the UserName and
Password.

I'm creating a class which is using DTS object to generate an Excel file and
I would only like to provide a SqlConnection object, a CommandText and a
CommandType to the class. The DTS Connection2 object has separate properties
for Datasource, Catalog, UserID and password. Now I have to provide the
UserName and Password as separate parameters to the class because I can't
derive them from the SqlConnection object.

Parsing the ConnectionString seems a bit tricky because you can provide the
username with different keywords like: "uid" or "User Id', the same goes for
password. And I don't know if there are more possible keywords.

Does anybody know a way to to achieve what I want?

TIA,
Friso Wiskerke

 

[Norman Yuan]

If the access to SQL Server uses Windows Security, the is not User Name (ID)
and Password required in ConnectionString. Also, in most case (in
ConnectionString), you may see "Persist Security Info=False;", that means,
even the username/password exist in ConnectionString, they do not het stored
in any object, they are only used to get into SQL Server when the Connection
being opened.

Probably, you'd better re-think your logic about getting user credentials.

 

[Friso Wiskerke]

I'm not using a Windows authentication but SQL authentication so I *must*
provide a username and a password in the connectionstring of my connection
object. I also have to provide them to the DTS.Connection2 object that's
generating the Excel file. The Persist Security Info is set to True.

any other suggestions?

TIA,
Friso Wiskerke

 

[Mark Ashton]

SqlClient & OracleClient have 'user id', 'user', 'uid', 'password', 'pwd'.
In ADO.Net V2.0, you can use the DbConnectionStringBuilder class to parse
the connection strings.

If you use the SqlConnectionStringBuilder/OracleConnectionStringBuilder,
they will correctly deal with all the possible synonms. For Odbc & OleDb,
all synonms are OLE DB provider specific and no attempt is used to deal with
them in managed code.

A suggestion would be to deal with all the keywords you know about and fail
if you encounter something outside of that range. That way you would be at
less risk for missing a particular keyword that causes you to make wrong
authentication assumptions.