• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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.

1 Solution
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now