SQLServer Validate Login

[nievesj]

Hello,

I have been looking around for this information, but did not found anything.

I want to validate the login of an user from Visual Basic to SQL Server, the user enters the login information in the application, then I send that information over to the database to verify it exist and is valid. Is there a way to do that?

Thanks in advance!

[vb_dba]

You can try to create a connection to the database using the user's login credentials:

Public Function VerifyLogin(sUID As String, sPWD As String) As Boolean
On Error Goto errHandler
    Dim oCon  As ADODB.Connection
    Dim sConStr As String
 
    sConStr = "Provider=SQLOLEDB.1;" & _
              "Server=[b]YourSQLServer[/b];" & _
              "Initial Catalog=[b]YourDBName[/b];" & _
              "UID=" & sUID & ";" & _
              "PWD=" & sPWD & ";"
 
 
    Set oCon = New ADODB.Connection
    With oCon
        .Open sConStr
        VerifyLogin = True
        .Close
    End With
    Set oCon = Nothing
    Exit Function
errHandler:
    Set oCon = Nothing
    VerifyLogin = False
End Function

[nievesj]

Nice!!

Now I have another problem, the domain accounts are not validating, only the accounts created on SQL Server works, the autehtication on the server is SQL and Windows......

[szlamany]

Domain accounts do not use the same connection strings as SQL Authentication accounts. Something more like this...

sConStr = "Provider=SQLOLEDB.1;" & _
              "Integrated Security=SSPI;" & _
              "Server=YourSQLServer;" & _
              "Initial Catalog=YourDBName;"

BTW - Mixing authentication types - why?? MS recommends Win2K authentication - not SQL authentication. Why have you made your choice??

[nievesj]

Originally posted by szlamany
Domain accounts do not use the same connection strings as SQL Authentication accounts. Something more like this...

sConStr = "Provider=SQLOLEDB.1;" & _
              "Integrated Security=SSPI;" & _
              "Server=YourSQLServer;" & _
              "Initial Catalog=YourDBName;"

BTW - Mixing authentication types - why?? MS recommends Win2K authentication - not SQL authentication. Why have you made your choice??

But there's no username and pass for that connection string?, it authenticates with the logged account?, thats not what I want.

BTW - Mixing authentication types - why?? MS recommends Win2K authentication - not SQL authentication. Why have you made your choice??

Dont know, this is the first time I wirk with SQL Server.

[szlamany]

The whole point of Win2K is integrated security.

A person logs into the PC once - and everything authenticates from that moment.

When you get into OUTLOOK on your PC, it doesn't ask for a username/password.

Having a person type a username and password at application launch time, and sending that in "clear text" around a network is a security hole.

So, when using integrated security, the domain/username of the person at logon time is used at SQL connection time - all automatically. Very nice and neat.

I'm sure we could start a debate here about the merits of both types of authentication.

[techgnome]

What we did on our login screen is to have a check box "Use Windows Login" If they user checks it, we disable the UserName and Password fields. It also then allows us to build the right kind of connection string to authenticate with.

TG

[szlamany]

Here's some code from our EstablishConnection FUNCTION...

If strDriver = "" Then
        strDriver = "SQLOLEDB"
    End If
    
    'Create a connection to the database
    Set gCn = New ADODB.Connection
    
    gCn.Provider = strDriver
    gCn.Properties("Data Source").Value = strServer
    gCn.Properties("Initial Catalog").Value = strDatabase
    If gstrUser <> "" Then
        gCn.Properties("User Id").Value = gstrUser
        gCn.Properties("Password").Value = gstrPassWord
    Else
        gCn.Properties("Integrated Security").Value = "SSPI"
    End If
    
    gCn.CommandTimeout = 300
    gCn.Open
    
    'Check the connection state
    If gCn.State = adStateOpen Then
        EstablishConnection = True
    Else
        EstablishConnection = False
    End If

[nievesj]

Originally posted by szlamany
The whole point of Win2K is integrated security.

A person logs into the PC once - and everything authenticates from that moment.

When you get into OUTLOOK on your PC, it doesn't ask for a username/password.

Having a person type a username and password at application launch time, and sending that in "clear text" around a network is a security hole.

So, when using integrated security, the domain/username of the person at logon time is used at SQL connection time - all automatically. Very nice and neat.

I'm sure we could start a debate here about the merits of both types of authentication.

That would work in an environment that all computers are in the Domain, that is not the case here. Oh well, I guell I have to enter all logins manually...