|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Storing Connection StringI 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? 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. Show quote "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message news:3DAAA142-0207-4FD3-9BFA-CB4BA62118F5@microsoft.com... >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? 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? -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message news:3DAAA142-0207-4FD3-9BFA-CB4BA62118F5@microsoft.com... >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? 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). 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)? Btw, how do I implement separate data tier? TIA Show quote "Miha Markic [MVP C#]" wrote: > 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? Hi John,
Show quote "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message I hope you understand that this is very very weak protection. And a very news:911A0572-76CC-4C3E-ADAA-85DC0E6215A2@microsoft.com... > 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). > 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 In a static property.> accessible > from everywhere (table adapters in several datasets and various command > objects)? > > 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. -- Miha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ Amen.
Show quote "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:uWdSo6wLHHA.4712@TK2MSFTNGP04.phx.gbl... > Hi John, > > "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in > message news:911A0572-76CC-4C3E-ADAA-85DC0E6215A2@microsoft.com... >> 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. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ 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. Show quote "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:uWdSo6wLHHA.4712@TK2MSFTNGP04.phx.gbl... > Hi John, > > "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in > message news:911A0572-76CC-4C3E-ADAA-85DC0E6215A2@microsoft.com... >> 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. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ 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. -- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:uWdSo6wLHHA.4712@TK2MSFTNGP04.phx.gbl... > Hi John, > > "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in > message news:911A0572-76CC-4C3E-ADAA-85DC0E6215A2@microsoft.com... >> 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. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ I would like to be able to manage users (e.g. add new users) from my
application. I could store encrypted username and password in code (instead storing them in plain text) and decrypt them on the fly, but I suppose that they are transferred unencrypted over the network and this is the weakest point. How can the malicious user otherwise find out the password in this scenario? Show quote "Miha Markic [MVP C#]" wrote: > "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message > news:911A0572-76CC-4C3E-ADAA-85DC0E6215A2@microsoft.com... > > 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). If enabled, you can encrypt the traffic to the SQL Server.
-- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in message news:DA2D4CCB-2574-490A-A55F-BC70B443AA01@microsoft.com... >I would like to be able to manage users (e.g. add new users) from my > application. > > I could store encrypted username and password in code (instead storing > them > in plain text) and decrypt them on the fly, but I suppose that they are > transferred unencrypted over the network and this is the weakest point. > How > can the malicious user otherwise find out the password in this scenario? > > "Miha Markic [MVP C#]" wrote: > >> "John Stivenson" <JohnStiven***@discussions.microsoft.com> wrote in >> message >> news:911A0572-76CC-4C3E-ADAA-85DC0E6215A2@microsoft.com... >> > 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). >  |
|||||||||||||||||||||||
Other interesting topics