SQL server Login from Windows Forms app

[jerrykeel]

hello!

The configuration is as follows
Remote SQLserver 2005 back-end
Windows Forms Front end (Visual studio 2008)

Is there a way to provide the username and password for the data connection from a Login form and not have it stored in a Connection string?

Even better, is there a way to invoke the SQL login window each time the app starts????

I need each user to be able to use his own (sqlserver) login everytime the application is started.

just like it happened with my access front end.

any information will be greatly appreciated!!

thank You!

[Siddharth Rout]

Welcome to the forums

Why not create a Login form which takes the relevant inputs and concatenates it with the Connection string?

[jmcilhinney]

If it's possible to use the inbuilt SQL Server then I'd say that you'd have to use SQL Server Management Objects (SMO), which means adding a reference in your project. If you do want to create your own dialogue, which is simple enough, then you can use a SqlConnectionStringBuilder to build a connection string from the components.

[Satal Keto]

I'm not certain if there is anyway of getting the SQL login window to show but you should be able to have a login form that the user supplies their username and password which is then used to check that they can login.

This is just a bit of example code that should hopefully show you how I would go about doing this

    Private Const CON_STR As String = "server=servername;uid=[username];pwd=[password];Initial catalog=defaultDB"

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Dim constr As String = ""
        If txtUser.Text.Trim.Length > 0 And txtPass.Text.Trim.Length > 0 Then
            constr = CON_STR.Replace("[username]", txtUser.Text.Trim)
            constr = constr.Replace("[password]", txtPass.Text.Trim)
            If checkConStr(constr) Then
                'The user can connect using that username and password
            Else
                'The user cannot connect using that username and password
            End If
        Else
            MsgBox("You must supply both a username and password in order to continue")
        End If
    End Sub

    Private Function checkConStr(ByVal constr As String) As Boolean
        Dim conn As New SqlConnection(constr)
        Dim rtn As Boolean = True

        Try
            conn.Open()
            conn.Close()
        Catch ex As Exception
            rtn = False
        End Try

        Return rtn
    End Function

As mentioned above this is just an example piece of code, you should have a better method of storing the base connection string rather than a constant but for this quick example that should be ample.

Anyway I hope that this helps you

Satal

[jerrykeel]

Thank you all for the quick replies!

omg the brackets in the connection string never crossed my mind!!
[password]


I will try this and if it works I will simply kill myself!!

i will look into the smo solution (sounds complicated though..).

Again thank you all, I will post the outcomes...

[jerrykeel]

Unfortunately No cookie. The steps are:
1. added the SQLserver datasource with the wizard
2. did not include the password in the connection string
3. created 2 forms, a login form and a form with datagrid view of a table from my dbase.
4. included the appropriate(?) code on the click event of the login button.
5. Hit F5
6. Insert right user and pass and click login (which also opens the form2) I get no errors, no bugs... and no data connection.!

Also when I input a wrong user/password I get no error code.

The code is as follows


Imports System.Data.SqlClient

Public Class Form1
Public Function SelectRows(ByVal dataSet As DataSet, ByVal connectionString As String, ByVal queryString As String) As DataSet

Using connection As New SqlConnection(connectionString)

Dim adapter As New SqlDataAdapter()

adapter.SelectCommand = New SqlCommand(queryString, connection)

adapter.Fill(dataSet)

Return dataSet

End Using

End Function
Private Const masterConnectionString As String = "server=GEROLYMATOS\STUDIO;uid=[user];pwd=[pword];Initial catalog=master"

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

masterConnectionString.Replace("[user]", TextBox1.Text.Trim)
masterConnectionString.Replace("[pword]", TextBox2.Text.Trim)

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Form2.Show()

End Sub

Any Ideas?