Storing Connection String

[Guest]

I have Users table in my database which stores usernames, passwords and other
information about users. When starting the application the user logs in
providing username and password.

This is why I want the connection string (including database username and
password) to be stored somewhere in code and not in application settings.
Only the program should "know" database username and password.

The database is accessed using both table adapters and command objects.

Which is the best place to store the connection string? How to specify this
connection string in existing table adapters?

 

[Scott M.]

I would still store the connection string in web.config and just inject the
user name and password into that string at run time. Remember, the string
is retrieved as just a string, so you can modify it in your code after
retrieving it.

There is no need for your code to have any information about the database
itself.

 

[Miha Markic [MVP C#]]

Hi John,

There are several solutions, such as:
- use integrated security
- separate data tier (so your app won't need connection string anymore)
- encrypt config file

What kind of application do you have?

 

[Miha Markic [MVP C#]]

Hi John,

It's a desktop application.

I don't want to use the integrated security because I don't know in which
environment the application will be used.

My idea is to have just one database user whose username and password
would
be hard-coded in the application. The connection string would be always
the
same (except server name, which is stored in config file or registry). The
authentication would be application-managed (storing usernames/passwords
in
the database).

I hope you understand that this is very very weak protection. And a very
problematic one. Since you have only one database connection credentials it
means that this credentials can do everything (that application can do) to
the database. Which means that if an user get hold of connection string
he/she can issue sql statements at his/her will to the database - and
getting hold of such connection string is not hard.
Anyway, in the given context I would encrypt username & password and put
them in config file (in case they change). Encryption key might be a
certificate stored in windows certificate store.
As per database I would use only stored procedures (and maybe views) to and
deny access to tables (to mantain integrity and to protect sensitive dasta
if any).

Where in code could I store the connection string in order to be
accessible
from everywhere (table adapters in several datasets and various command
objects)?

In a static property.

Btw, how do I implement separate data tier?

You create a separate assembly which resides on the server and does database
operations. Front end communicates with this assembly remotely (using
remoting, WCF, web services or whatever) and sends datasets (or any other
form of data - but doesn't issue any sql statements) forth and back.

 

[Scott M.]

Amen.

Hi John,



I hope you understand that this is very very weak protection. And a very
problematic one. Since you have only one database connection credentials
it means that this credentials can do everything (that application can do)
to the database. Which means that if an user get hold of connection string
he/she can issue sql statements at his/her will to the database - and
getting hold of such connection string is not hard.
Anyway, in the given context I would encrypt username & password and put
them in config file (in case they change). Encryption key might be a
certificate stored in windows certificate store.
As per database I would use only stored procedures (and maybe views) to
and deny access to tables (to mantain integrity and to protect sensitive
dasta if any).


In a static property.


You create a separate assembly which resides on the server and does
database operations. Front end communicates with this assembly remotely
(using remoting, WCF, web services or whatever) and sends datasets (or any
other form of data - but doesn't issue any sql statements) forth and back.

 

[Scott M.]

Just to follow up, including this information in your code is poor design.
I just don't understand why you would want to do this. All I can think of
are disadvantages, while by separating it out, I can think of several
advantages, security & scalability being among them.

 

[William \(Bill\) Vaughn]

Ah, if the credentials you use for your application can only execute
selected (a few, specific) stored procedures (or views), the security is not
compromised to that great of an extent. SSPI security is more expensive to
manage in that each group (assuming users are members of groups) must also
be maintained independently as the users come and go. The same rights must
also be assigned to the groups. See Chapter 9 for more information.

--
____________________________________
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[William \(Bill\) Vaughn]

If enabled, you can encrypt the traffic to the SQL Server.

--
____________________________________
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)