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!

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)

[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

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.

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.

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.

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

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. 

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.

Keith Chisarik

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.