We help IT Professionals succeed at work.

SQL connections with either Windows or SQL Authentication in VB.net

rko9911 asked
Several years ago, I had developed a VB.NET application that attaches to SQL Server with Windows Authentication that we use at our company.  Recently, I was asked to add a user who is in a non-trusted domain.  I have changed the SQL Server authentication to mixed mode and added the person in Security->Logins and Database->Security->Users.  But honestly, I have no idea what to do next.  I am by no means a professional programmer, but I do have a decent understanding of VB and SQL.  

I really don't want to add a uid and password in an additional connection string in the app.config.  

What I would like to do is have the application check for authentication on load.  If the user has Windows authentication, proceed normally.  If the user has SQL authentication, go to a login screen.  

But I will be happy to entertain other suggestions.  

Any help would be appreciated. Any code snippets would be helpful too.
Watch Question

Top Expert 2015
Here is a trick I used once. There might be more elegant methods, but mine worked.

The first time the application is launched, it tries to connect with Windows Authentication. If it works, it saves a flag in the user settings, that tells it to always start with that method.

If it fails, then one assume that a username and password is required. It again saves a flag to that effect, that tells it to, always launch the application with the login form.

You might want to had an option somewhere to reset the flag in case the status of the user changes or his computer is given to somebody else.

At first sight, that seems to require 2 connection strings. But you do not have to store the password and username in the configuration file. Simply request it from the user himself everytime, which is the best way to go anyway, with the following method.

In the configuration file, define the username and password section the following way:


Fill it in code by pasting the information typed by the user in the login form, by using String.Format:

String.Format(My.Settings.ConnectionString, txtUserName.Text, txtPassword.Text)

You could also have only a partial connection string in the settings, that holds what is common to both ways of connecting, and fill in the remaining part in your code depending on whether you go for Windows Authentication or User/Password.

A third way would be to save only the server name and possibly the database name in the settings, and build the connection string with a ConnectionStringBuilder object.
When you say login screen to pop up for screen authentication, do you mean in your windows application?  If so, try the attached example.
Marten RuneSQL Expert/Infrastructure Architect

A further devolopment of Jacques Bourgeois (James Burger) method would be to:
Check if the domain that validated the user launching the app, has the right domain-name, if so launch as usual. If not use the alternate connectionstring and let the user fill in name and password.

I would not let this program store the pwd, in for example the registry. But I would let it "remember" the user last entered in the user fileld, and let the second launch of this program fill in the user, and set focus on the password field. All to make it comfortable for the user, but still safe!

Note though, as a security remark. There is code out there in the wild that can take a network capture, find sql logon name, extract the hash, and then bruteforce it. This means someone evil can sniff the pwd (or bruteforce it to be correct). If the password needs to be entered manually, it cant be brutaly long, and not to complicated. Wich means it can be bruteforced!

There are solutions to this, I'm not going to suggest them in this thread, but rather inform about the cavity of such a solution.

Regards Marten


Thank you very much.  I used a combination of Jacques' and srikanthreddy143's solutions.  I started the application with a Login screen, but on load tried the trusted connection, and if OK, then immediately opened the main menu.  If this check failed, it would stay on the login screen, where the submit button would check credentials.  I had the different forms reference which connection method was used through a flag to determine which connection string to use when running the queries.  I used both answers to help me with this.