Transmission of connection string in ADO.Net

  • Thread starter Thread starter GCeaser
  • Start date Start date
G

GCeaser

Can anyone tell me if the actual connection string of the ADO.Net
connection objects are sent across the network to the database in clear
text?

Thanks
George
 
The connection string exists as a definition for the client to give it the
information necessary to connect to the server. The connection string is not
transmitted anywhere (unless a bad programmer sets it up to be...). Plain
text is not used. (MS isn't THAT stupid.)
 
Well,

It does seem logical that in some manner the authentication
credentials must make it to the database in order for the database to
authenticate a user. When not using windows integrated authentication,
is this information transmitted in a secure manner to the database and
is there documentation from Microsoft that anyone knows of supporting
this?

Thanks
George
 
Microsoft does not disclose this information, other than basic info about
what ports are used, nor should they.
 
Hi George,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know how credentials are
passed through network to SQL Server. If there is any misunderstanding,
please feel free to let me know.

Based on the document that cecil has provided, we can clearly see that
credentials are passed over the wire so they must be secured.

So it is recommended to use Windows Authentication instead of SQL
Authentication, because of the following reasons:

1. Credentials are managed for you and the credentials are not transmitted
over the network.
2. You avoid embedding user names and passwords in connection strings.
3. Logon security improves through password expiration periods, minimum
lengths, and account lockout after multiple invalid logon requests. This
mitigates the threat from dictionary attacks.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
All,


This has all been very good information. Let me ask a couple more
questions and these revolve around using Windows Authentication into
SQL Server.

1. In WinForms application, if all the connections are being made from
a middle tier component that is a .Net Remoting service on the server,
how do I get the client's credentials onto the connection so SQL Server
will authenticate for the actual client user? If this is even
possible, will I get the benefit of connection pooling since each
individual user will be authenticated to the database?

2. If this is not possible / reasonable, then I would assume another
approach would be to have the service running under a valid Windows
Domain account on the server. The challenge with this is - especially
after 9-11 - organizations are implementing very strict rules on the
frequency with which passwords must change and are becoming less open
to the having 'service' type domain ids whose passwords to not expire.
What is the best way to handle this?
Thanks again for you all your help.
George
 
Hi George,

1. If you're using a .NET remoting service, this component will be run
under a certain account. If using Windows Authentication, I think you will
benefit from connection pooling, because ADO.NET creates pool according to
the connection string. Since the connection string is the same, the
connections will be put in a single pool.

2. I think it will be better to use a domain account for authentication,
because it will prevent problem from happening. We can try to create a
domain account, and set the account's password to "never expire".

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
George,
You can configure ASP.Net to use Windows Authentication to
authenticate the remote user and control Authorization. You could then
use impersonation to carry that identity to SQL server, then yes you
would loose the benefit of connection pooling, but you can limit
impersonation by programmatically controlling it. Have only two or
three identities that you impersonate that correspond to different
security roles in the db, you can then use the incoming ASP.Net
authenticated identity to decide which of the limited number of
accounts you will impersonate. See the link bellow for more info
especially check out the section labeled "Avoid impersonation in the
middle tier":
http://msdn.microsoft.com/library/e...p?frame=true#daag_managingdatabaseconnections
Cecil Howell MCSD, MCAD.Net, MCT
 
All,

OK - So let me get this straight -

1. The only way to get connection pooling is to have identical
connection strings.
2. That would typically mean connecting to the database using a small
number of either SQL Server Accounts or domain accounts. Non-Domain
IDs and password will be sent in open text across the network.
3. This then prevents SQL server from knowing the exact ID of the
person who ran the transaction thus making auditing difficult.
4. Even if this approach were taken, most may organizations will NOT
allow domain IDs whose password does not expire.
5. I each individual user's ID is used through something like Window
authentication, you loose connection pooling on the server but SQL
Server would be able to determine the exact user that ran the
transaction for Auditing purposes.
Does this sound accurate?
Thanks
George
 
Hi George,

Yes, I think that's correct.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Sounds about right, but the remote component in ASP.Net would know who
the original caller was and could pass this information into SQL as a
parameter to each sp. Not perfect but with a little work you get
auditing back. As for needing the password to change periodically
maybe you could have the component itself generate a new password
periodically or convince the organization to accept that if a pwd is
long (128 bytes) and random enough there is no need to change it
periodically. Just two ideas.

Cecil Howell MCSD, MCAD.Net, MCT
 
Back
Top