By Greg McGuffey - 11/1/2006
I'm attempting to figure out how to use a single well known login user/password for the db, then use the security system to authenticate, do roles/permissions, etc. I.e. I'm using a more 'web' like system within this winform app. I'm at a loss to determine a secure way to do this. I don't want the user's knowing much of anything about the database they are connecting to. Unfortunately the Connection Manager displays the database name and the user name when a shared settings file is used. The database name is OK (not great, just ok), but the user name is definitely not OK.
I could set the DataSources manually, but then I'm left with a bunch of other security holes, chiefly that most .net approved places to store app settings are done so in clear text or there are significant challenges to encrypt the app.config file.
Also, I will likely want to build the connection based on how they are connecting to the database, either locally or via some sort of VPN (some VPNs I've used required the IP, others the server name). I also need to use the TCP/IP library to connect (to SQL Server 2000), since this is how the VPNs work (at least the ones I'm working with). This all adds up to a quandary about how to connect to the database(s). The app needs to know the user name and passwords for each database it is connecting to, but these need to be secure. It is too easy to decompile a .net app, so placing it in the compiled code is not good. The app.config is clear text and encrypting it causes problems since I can't encrypt it easily in a way that allows any user of the app to decrypt it (as far as I understand it).
Is there any way to store these sorts of settings in a secure file, like the shared settings file for connections? Preferably, it would be just like the shared connections file, in that it could be on a UNC share (or local) or even better a secure web site. Any help would be greatly appreciated!
|
By Trent L. Taylor - 11/2/2006
Honestly I don't know exactly what you want or how to answer this...I got more of what you don't want than what you do...so here are a few ideas that may help.  First of all, you can set the datasources manually rather than using a shared settings file or going through the Connection Manager. That is why we allow you to control the DataSources collection: MicroFour.StrataFrame.Data.DataBasics.DataSources.Add(New SqlDataSourceItem("","MyConnectionString")) As for connecting through a VPN and using IP addresses, this already works....it .NET or StrataFrame. You just provide the IP in place of the server name. You do not have to specify a server name. You can even use IPs and names instances in conjunction: 10.0.0.x\SQLEXPRESS. Last, you can reverse engineer any SQL connection to retrieve the password from it. You don't have to store it in clear text. To get to the connection string it would look like this: MyConnectionString = MicroFour.StrataFrame.Data.Databasics.DataSources(0).ConnectionString You can then parse out anything you want using RegEx or whatever else. You can also use an SqlConnection class to pull information out (I don't think you can get the username though): Dim loSQL As System.Data.SqlClient.SqlConnection loSQL = New SqlConnection(MicroFour.StrataFrame.Data.DataBasics.DataSources(0).ConnectionString) MsgBox(loSQL.DataSource)
|
By Greg McGuffey - 11/2/2006
[quote]Honestly I don't know exactly what you want[\quote]
The requirements are:
1. Use a single login to database (data source more generally) for app
2. The credentials must be secure (user name and password), i.e. user won't know and can't know these
3. The app can change databases it is using (i.e. different environments like dev, test, prod etc., all dbs connected to use the same schema), thus the server and database within the connection string need to be easily changable.
Try 1:
Use connection manager with required data sources.
Problem:
User has to know user name and password to database.
Try 2:
Use connection manager with required data sources and shared settings files.
Problem:
The user name (user name used to access db) is displayed (and less of an issue, the name of the database).
Try 3:
Directly set datasources in code.
Problem:
While this can be used to handle changing databases, the credentials are not secure. It is a trivial matter to decompile the exe (or dll) and view the user name and password as it is set in code. (see Reflector for .NET at http://www.aisto.com/roeder/dotnet/)
Does this help your understanding? So, I need a try 4, 5, etc. that will meet these requirements 
|
By Trent L. Taylor - 11/2/2006
Your number 3 is only insecure if you use .NETs clear text config file. What is keeping you from having your own external encrypted text file that has the information? Or even internal for that matter. This has more to do with the design of your application than anything. You can set connect strings through StrataFrame about 5 different ways...so the issue is how and where do you want to get your connection information from? Once you have this you can set the connection any way that you would like.For example, StrataFrame has encryption classes that allow you to encrypt and decrypt whole files using 3DES. Dim lo3DES As New MicroFour.StrataFrame.Security.Encryption.TripleDESWrapper() lo3DES.EncryptFile("c:\temp\MyConnectionFile.xml") lo3DES.DecryptFile("c:\temp\MyConnectionFile.xml") You could even decrypt the file into memory so that it never exists on disk in a decrypted format: Dim loStream As New MemoryStream()
Dim loReader As New StreamReader(loStream)
Dim lcDecryptedText As String '-- Decrypt the file into a memory stream
lo3DES.DecryptFileToStream("c:\temp\MyConnectionFile.xml", loStream) '-- Convert the stream into a text string
loStream.Seek(0, SeekOrigin.Begin)
lcDecryptedText = loReader.ReadToEnd() '-- Do whatever you want with the decrypted data '-- Clean Up
loReader.Close() This really has more to do with how you want to distribute your application and connect your end-user to the server. You know the requirements that you app has...there are hundreds of ways to take a stored connection string and load it up. For example, in our medical application, we have a server that authenticates the concurrent users and much more. The only thing that gets entered on a client install is the name of the server (and we have detection classes for that as well). When the end-user goes into our medical software, it talks to the server, which in turn gives the client the connection information. This way is it all controlled from a single location...the server. Once we get the connection information we manually set the data sources. This requires no intervention on the part of the end-user going into the application. So this is more of a design issue related to your application.
|
By Greg McGuffey - 11/2/2006
I'm sorry if the question seemed like a silly one. I'm not only new to StrataFrame, but also to .Net. I'm a business user that has taught myself to program, starting in Lotus 1a, then Excel, then Access, were I've been stuck for a while. Along the way I've done some ASP/PHP web work and some proprietary scripting in an online game. So, while the architecture may have seemed obvious to you, it was eluding me 
Thanks for the response. I'll looking into encrypting the connection info into a file and then using a stream to read it in. That will be the foundation of what I need.
|
By Trent L. Taylor - 11/2/2006
So, while the architecture may have seemed obvious to you, it was eluding me I am sorry if you took my response the wrong way. I completely understand where you are coming from. We never had anyone where we could ask these types of questions. So please don't let me discourage you...that was NEVER my intent. I do have have one suggestion that could supercharge your development and dramatically move time to the left. We have a training class coming up here December 1-3 that covers StrataFrame and developing applications in .NET using StrataFrame. We have had people come to the class with basically very little .NET experience and go home and be productive immediately. If you could work this out I think that it would be well worth your while! As for my previous posts....I was just trying to give you some ideas as to how you may want your application to work. First, look at the problem from a 10,000 foot view and get a concept in mind. Then you can worry about how to code it.
|
By Greg McGuffey - 11/2/2006
Thanks Trent. I really do appreciate this forum and your and Ben's quick and helpful replies. When I read your reply I was simply reminded of how little I know and of how little time I have to learn it. I'm also under some serious constraints to figure out if StrataFrame is the correct choice for our needs. At this point I'm feeling much better about it, both as a technology and considering the help I'm getting. What's cool is that not only are you telling me what can be done, but I'm actually doing it too, which is amazing
So, I'm sorry I was a bit snippy. I'll look into the class and I promise I'll have more architecture questions too 
|
By Trent L. Taylor - 11/2/2006
No worries whatsoever! We look forward to working with you and I hope to see you here in December. We like getting to know our users and one good way of doing that, aside from email and the forum, is in person at the classes.
|
By Keith Chisarik - 11/2/2006
I attended the class after evaluating SF for only a few days due to the timing of the class, I had until that point been 100% self taught in .NET, I didn't come back a pro, but I was definitely on the right track and was able to be productive with SF as soon as the plane hit the ground. Highly recommended. I continue to learn and as you have seen here already the guys at SF are incredibly helpful.
|
By Ivan George Borges - 11/3/2006
Right, here goes my testimony ... put a soft song in the background.I attended the course as well, and I had 0% of knowledge in .NET Only great things came out of it. I was introduced to the product not only by the developers, but by the questions the other attendees made. So, I can't say I know .NET yet, nor VB.NET, which I decided to use with a little push, but within a month I had an extremely professional application developed, all set with security, layers, integrated reports, in a way I wouldn't dream to have in years of developing myself. It's a great jump start, that I can tell you. Cheers.
|
By Greg McGuffey - 11/3/2006
I woke up this morning with a horrible thought. So, in the InitApplication, the admin name and password, the security user name and password and the key for security are all set. All very easily accessible by decompiling the assembly. So, I must be missing something. How is this information secured in a Winform app? I'm guessing that .NET can't actually secure this and an obfuscater must be used.
|
By Greg McGuffey - 11/3/2006
Highly recommended. I continue to learn and as you have seen here already the guys at SF are incredibly helpful.
Right, here goes my testimony ... put a soft song in the background.
Thanks for the comments and encouragement from both Keith and Ivan. I'm seriously considering it. I contacted sales and got the details. I'm assuming that Ben, Trent and Steve actually teach this class right?
|
By Trent L. Taylor - 11/3/2006
You can use obfuscator, but what we did for our medical system is create a dynamic password that goes through a series of prefixes (which are encrypted in the code) and then we just decrypt it when the app is loading. This way it is not stored in the assembly as clear text. For example, see a password example below:Dim lo3DES As New MicroFour.StrataFrame.Secuity.Encryption.TripleDESWrapper()
SecurityBasics.AdministratorPassword = lo3DES.Decrypt("jCgs2890obI=") To get the encrypted text we just wrote a simple little program that turns the text that we type in into the encrypted text. We then copy that text and do whatever we want with it.
|
By Greg McGuffey - 11/3/2006
OK, but don't you need to have a known key to do the encryption/decryption and isn't that key set in code as clear text? (the setsecuritykeyandvector...property) I know just enough to be dangerous when it come to cryptography, but no enough to actually know anything :/
|
By Ivan George Borges - 11/3/2006
I'm assuming that Ben, Trent and Steve actually teach this class right? That's it Greg, these are the ones.
|
By Trent L. Taylor - 11/3/2006
No...a key and vector are byte arrays, so you can store this anywhere in your app, and even have a method that obfuscates that array before it is passed over. The sample I gave you uses the defaults, so they will not be visible, but there are definite ways to do this. In our medical application we also tie in some additional hashes that are appended to the password....so it would take an extremely dedicated genius to crack the code
|
By Greg McGuffey - 11/3/2006
OK, to restate what's been said so far (mostly so I have it straight to update my boss, who will decide to actually buy StrataFrame):
1. Ton of way to handle setting up data sources.
2. Sensitive strings can be encrypted within .net assembly, i.e. externally, encrypt them, then paste encrypted string with a call to decrypt it.
3. The keys are byte arrays, thus are not as easily read, they can be encrypted and salted (extra hashs) (I sort of know what that means).
4. For now, I can just use the default key provided by SF to start to figure this out.
5. I will continue to be able to get help here to figure this out
6. The upcoming class would really help me much less clueless
|
By Trent L. Taylor - 11/3/2006
You got it
|
|