Is there a standard way to handle a data source that requires a password?

I have a client that does not want to embed a password in their data source. Instead, they'd like me to popup an input box asking for the password at run-time. My VB.Net based software works with a variety of data sources so I was wondering if there's a standard way of asking the odbc /oledb system to prompt the user for the password if a password is not specified in the configured data source.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Pawan KumarDatabase ExpertCommented:
I think you should go with the forms authentication.
ou81aswellAuthor Commented:
My VB.NET app is windows forms based and runs on a Windows desktop.
You could prompt for credentials and then establish the odbc connection.

Not sure what you might be running into..
Pawan KumarDatabase ExpertCommented:
>>My VB.NET app is windows forms based and runs on a Windows desktop.
In this case you can store the user Name and password in the DB using encrypted format and authenticate the user using login page.
@Pawan he doesn't want to store the password for the actual data sources in the project.  So he wouldn't be able to connect to the database to authenticate the user ;)

And to your question, no there isn't really a standard way.  Different connection strings depending on the source may or may not require a password.  A text file connection doesn't need a password.
Even if you filter by the provider, you still aren't guaranteed.  SQL for example WOULD need one if using SQL auth, would not need one if using windows auth.

My best suggestion would be to include a false / token password in the connection string wherever one is required.  ${UserPassword} for example.

    <add name="Sql1" connectionString="data source=myServer;initial catalog=myDataBase;user id=myUserName;password=${UserPassword};MultipleActiveResultSets=True;" />
    <add name="Sql2" connectionString="Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=${UserPassword};" />
    <add name="Txt" connectionString="Provider=Microsoft.Jet.OleDb.4.0; Data Source=eee;Extended Properties=&quot;Text;HDR=YES;FMT=Delimited&quot;" />

Open in new window

Whenever you're going to connect (or on startup), load the connection from Configuration as a string, and check for that token.
If it exists:
1) Then prompt the user for the password
2) Replace the token in the connection string
3) Pass that NEW string to the oledb / ocdb connection constructor

An ole connection builder does have a "Keys" property you could use, but even connection strings without passwords will still have that key in place, so above suggestion is probably the easiest to implement.

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.