Larry Wilson

I am using VS2005 and have created a login form using the VS template. The below comments were added to the form class, however, the link to LinkID 35339 seems to be no longer valid.

' TODO: Insert code to perform custom authentication using the provided username and password

' (See http://go.microsoft.com/fwlink/ LinkId=35339).

' The custom principal can then be attached to the current thread's principal as follows:

' My.User.CurrentPrincipal = CustomPrincipal

' where CustomPrincipal is the IPrincipal implementation used to perform authentication.

' Subsequently, My.User will return identity information encapsulated in the CustomPrincipal object

' such as the username, display name, etc.

Currently, for testing purposes, my ConnectionString is storing the ID and password, but I would like to change that and, instead use the ID and password entered on this form. Can anyone point me to the current location of the above document or at least tell me how I need to use this Login form to change/specify my ConnectionString

Thanks,

Larry



Re: Visual Basic Language Logging in to SQL Server database using ID and password from the Login form

Derek Smyth

Hi Larry,

That information is not what your looking for mate. The link is pointing to information on Principles and code access security, rather than anything to do with database access, well maybe IntegratedSecurity but not in supplying an ID and password to a connection string.

What I recommed you looking at the SqlConnectionStringBuilder class, this will let you take your current connection string and change certain properties of it, like the ID and password (which you get from your login form). The class will then give you a connection string with those new changed credentials.

Post again if you have any problems in using it.






Re: Visual Basic Language Logging in to SQL Server database using ID and password from the Login form

Larry Wilson

Derek,

Thanks for the reply. The SQLConnectionStringBuilder seems to be much more what I am looking for. However, there is still a gap in my level of understanding on this. Here is a little more detail on what my need is.

In my app.config file I have a ConnectionString. I do not want to keep, for obvious reasons, the UserID and Password in the app.config file. Since this a data-centric application, I have several data sets, each containing one or more tableadapters. These tableadapters all use the ConnectionString from the app.config file.

When the user logs in, I need to be able to use the UserID and Password along with the Server and Initial Catalog from the ConnectionString to create a new ConnectionString that all of my tableadapters will use.

I can probably glean from the SQLConnectionStringBuilder class documentation what I need to be able to build the new ConnectionString. However, I need to know how to persist that new ConnectionString and how/when/where to change the tableadapters to use it.

Thanks a ton,

Larry





Re: Visual Basic Language Logging in to SQL Server database using ID and password from the Login form

Derek Smyth

Hi Larry,

What you could do is store the connection string in the configuration file but without user credentials, leave them blank but have everything else filled in. You can use this as the initial state of the SqlConnectionStringBuilder. You can load this connection string using ConfigurationManager.ConnectionStrings[0].ConnectionString (I think thats the call, not sure), but you go through the ConfigurationManager and you need to add a reference to System.Configuration.dll.

I would keep the connection string persisted in memory, there is no reason to store it to file or back to the configuration file. You could create a class with a Shared method that returns the connection string, here's an example..

Public Class ConnectionString

Public Shared UserID as String

Public Shared Password as String

Public Shared Function Generate() as SqlConnection

'use the configuration manager to load the connection string in app.config (without credentials)

'load connection string into SqlConnectionStringBuilder

'set user id and password properties

'return string, or connection object

End Function

End Class

Unfortunately your storing the password in memory for the duration of the application which is not ideal. When you create your TableAdapters you need to set then to use your SqlConnection, they should have a connection property, truth be told I am not that familiar with table adapters but I'm assuming there is a property or method available that should allow you to change which connection they use.

Dim datAdapt as New DataSetAdapters.EmployeeAdapter()

'datAdapt.Connection = maybe or datAdapt.InsertCommand.Connection






Re: Visual Basic Language Logging in to SQL Server database using ID and password from the Login form

Larry Wilson

Derek,

Thank-you very much for your help. This did the trick. I created the ConnectionString class as above. In the Generate function, I load the connection string from my app.config file into the SqlConnectionStringBuilder, add the user id and password from the login form and put that in a new SqlConnection object. In my data form load event, I replace the connection object of the with my new connection object and it works great.

For those could benefit from this, my data form code is below:

Private Sub frmAssessmentTypeMaint_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Me.AssessmentTableAdapter.Connection = ConnectionString.Generate()

Me.AssessmentTableAdapter.Fill(Me.dsTableName.Assessment)

End Sub

My class is as follows:

Imports system.Data.SqlClient

Public Class ConnectionString

Public Shared UserID As String

Public Shared Password As String

Public Shared Function Generate() As SqlConnection

Dim Builder As New SqlConnectionStringBuilder

Dim NewConnection As New SqlConnection

Builder.ConnectionString = MyApp.My.Settings.MyAppConnectionString

Builder.UserID = UserID

Builder.Password = Password

NewConnection.ConnectionString = Builder.ConnectionString

Return NewConnection

End Function

End Class

Thanks again for your help.