Question Using a App.Config connection string and display Login/Password error

[farook]

Hello,

Given bwlow is the code I use to connect to my SQL 2008 R2 Server. The connection is excellent. My problem is that I cannot display a messagebox on logon failure. I do not wish to use Dim sqlCnn As New SqlConnection, Dim connString As String = My.Settings.Item("CustomerConnectionString").ToString, etc. Here is the code:

Thank you.

Private Sub LoginBtn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoginBtn1.Click
My.Settings.Item("CustomerConnectionString") = "Data Source=FAROOK-PC\SQLEXPRESS;Initial Catalog= '" & Me.ComboBox1.Text & "'; uid = '" & Me.Login1.Text & "'; pwd = '" & Me.Password1.Text & "'"
Form1.Show()
Me.Hide()
End Sub

 

[Menthos]

Are you attempting to control access to your database (or application) via the connection string????

Unless you have the highly unlikely situation of separate schemas for the users of your application, using the connection string as your database authentication is a pretty poor way of going about things, and if it isn't causing problems at the moment (which it is) it most certainly will in the future.

Normal best practice is to have your connection string defined to give the application access to the relevant schema. User authentication is, usually, best handled by your application - store the user details in a table, quiz that table for user / pass combos and go from there.

Best advice - store your user details within your database.

 

[farook]

Are you attempting to control access to your database (or application) via the connection string????

Unless you have the highly unlikely situation of separate schemas for the users of your application, using the connection string as your database authentication is a pretty poor way of going about things, and if it isn't causing problems at the moment (which it is) it most certainly will in the future.

Normal best practice is to have your connection string defined to give the application access to the relevant schema. User authentication is, usually, best handled by your application - store the user details in a table, quiz that table for user / pass combos and go from there.

Best advice - store your user details within your database.

Thank you for your reply post. I had been using authentication method via tables way back 10 years ago for a medical laboratory report system using MS-Access. I was not happy using Access since I was already using Oracle 8i and MS-Sql Server DBMS. It has been a long way I have come to while using Connection String authentication for users. I am used to it. Schemas can be setup in groups, so there is less Server side work involved, besides I am talking about under 50 users. The Table auth. method is good, you can enable and disable Menu items as well for users. I am doing a System which includes SQL R2 user setup, backup Db, copy Db, etc. I have posted the above code since it's superb. This one line authenticates the user to the Databases and Tables. Just one line (My.Settings.Item("CustomerConnectionString")). All I need is a msgbox displayed on Logon failure. Sorry for replying so late. Thank you menthos.

 

[Menthos]

Well I'd argue that the code you posted is "superb" to be honest, and regardless of the number of users you have, a bad coding practice is still a bad coding practice.

The only way I can think of flagging the details entered being wrong would be a Try...Catch block and trap the exception raised when your db connection fails. Messy, but it will likely do the job.

 

[farook]

Well I'd argue that the code you posted is "superb" to be honest, and regardless of the number of users you have, a bad coding practice is still a bad coding practice.

The only way I can think of flagging the details entered being wrong would be a Try...Catch block and trap the exception raised when your db connection fails. Messy, but it will likely do the job.

I'm using Try Catch. I want to shorten my code. We have to bear in mind .NET 4 has things even easier for programmers. Long codes require more time. I wish to remind you that there are no problems logging in, but a wron uid/pwd should throw an error. I also wish to remind you that this single line of code connects the user to the Server. What I really need is that the My.Settings... results have to be returned to some variable enabling an error throw. I'm posting another thread for a different problem. Could you pls have a look if you could. Thanks.

My.Settings.Item("CustomerConnectionString") = "Data Source=FAROOK-PC\SQLEXPRESS;Initial Catalog= '" & Me.Database1.Text & "'; uid = '" & Me.Login1.Text & "'; pwd = '" & Me.Password1.Text & "'"

Dim sqlCnn As New SqlConnection
Dim connString As String = My.Settings.Item("CustomerConnectionString").ToString

Try
sqlCnn = New SqlConnection(connString)
sqlCnn.Open()
globalConnStr = connString
Catch ex As SqlException
MsgBox(Err.Description)
sqlCnn.Close()
Exit Sub
End Try

 

[Menthos]

What you should say is that you are not getting any problems logging in - you'll be hard pushed to find anyone who'll advocate your current method of user authentication as being remotely best practice. It works for you and you're happy with it, so I guess we'll leave that there.

What I really need is that the My.Settings... results have to be returned to some variable enabling an error throw.

There simply isn't any sort of validation available using application settings - you are in essence just storing a string. The only way you could possibly parse whether that string is going to perform as a valid connection to your database, is to try and connect with it (which you're already doing). I guess you could create a shell function that checked your connection string then updated the settings object, but since you don't want to add any code, that wouldn't be an option.

Barring any other change, you'll have to stick with the Try...Catch

 

[farook]

What you should say is that you are not getting any problems logging in - you'll be hard pushed to find anyone who'll advocate your current method of user authentication as being remotely best practice. It works for you and you're happy with it, so I guess we'll leave that there.



There simply isn't any sort of validation available using application settings - you are in essence just storing a string. The only way you could possibly parse whether that string is going to perform as a valid connection to your database, is to try and connect with it (which you're already doing). I guess you could create a shell function that checked your connection string then updated the settings object, but since you don't want to add any code, that wouldn't be an option.

Barring any other change, you'll have to stick with the Try...Catch

Message noted with thanks.