VB.NET secure database login

Dear Gurus,

Would appreciate some guidance how best to go around creating a secure login form for SQL server authentication (non windows based authentication for now). I have gotten the form working fine (Application is winforms based) but would like to store the credentials supplied by the user (Username & password) dynamically and securely in memory to dynamically build the connection strings as needed without having to re ask the end user to supply the credentials.

Till now I have the following possible scenarios that may work:

Pass the values of the two text boxes to the constructor of the main application screen and store the values in memory ( have some concerns re run time attacks )
After some research the Securestring function seemed very interesting to store the password value in a encrypted format but I got a bit confused on how to actually use it ( decrypt the string and deploy that value in the connection string)

Any assistance with the above is greatly appriciated
Nicholas XerriSolutions ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
@Nicholas Xerri -  Ideally you should use Windows Authentication.

However, if you can't use Windows Authentication only then I'm assuming your SQL Server is configured to use Mixed Mode Authentication.

Is your application using a dedicated account to connect to SQL Server and your App users multiple user IDs stored in a SQL table?

If so, then you could perhaps create some host lookup table that SQL Server checks after a user logins for the first time:
1. If host is not found force login using authorize user lookup table.
2. Store host info after successful login.
3. Future visits would validate user against host table.
4. If authorized then allow access and skip password requirement.

You can obtain connecting client information using something like the following:
SELECT  hostname,
        net_library,
        net_address,
        client_net_address
FROM    sys.sysprocesses AS S
INNER JOIN    sys.dm_exec_connections AS decc ON S.spid = decc.session_id
WHERE   spid = @@SPID

Open in new window

Nicholas XerriSolutions ArchitectAuthor Commented:
The Database is in Azure hence the SQL accounts since im still working on getting Azzure AD working. No the application does not use a static account to talk to the database rather the user supplied credentials actually form part of the connection string.

There is no user table, rather I am using SQL Login commands to create the users. My issue is how to store the user supplied username and password in memory securely once the login screen has done its job (connection was successfully established to the database and closed hence validating the supplied credentials). The idea is to be able to dynamically build connection strings based on the user supplied credentials, but I don't want to prompt them for credentials every so often.
LajuanTaylorCommented:
@Nicholas Xerri - Microsoft suggests that a password handling best practice when using Windows Forms with Azure is:
The login and password to SQL Azure should never be stored in the code running on the desktop.
The login and password shouldn’t be stored on the the user’s hard drive.
Every desktop user should have their own login and password to SQL Azure.

As a proof of concept one option would be to create a Microsoft Account for each user, and then have your app leverage the saved credentials from the MS account for authentication. Your application needs to be configured as a Mobile or Desktop Client application on the API settings page for this library to work - https://github.com/rgregg/microsoft-account-winforms

If that's not an option take look at Chapter 15: Internet - Windows Forms Client to Remote WCF Using Message Security (Original Caller, HTTP) - https://msdn.microsoft.com/en-us/library/ff648163.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.