Here is the problem I am having. We have a VB and SQL Server application. Every user that logs into the domain can use the application just fine. However, we recently had need to allow access to an individual outside of the domain and this is where the problem is.

I receive the following error:

Login failed for user (null) Reason: Not associated with trusted SQL Server connection.

In order to test this scenario I logged into my machine with the same username and password as the domain login. I set SQL Server Authentication to Windows Only. If I login locally I receive the same error that our remote user receives. Our remote user is a part of the domain as I am but the remote user is also part of other domains as well and therefore cannot log into one specified domain.

It was my understanding that by changing the SQL Authentication to Windows only the login would be authentication using NT\Windows authentication.

I received the same error if SQL Authentication is set to mixed mode. I expected to receive the error in mixed mode.

Here is the connection string I am using.

    NewConnection.ConnectionString = "Provider=SQLOLEDB.1;" _
    & "Integrated Security=SSPI;" _
    & "Persist Security Info=False;" _
    & "User ID=" & Trim(UserID) & ";" _
    & "Initial Catalog=" & Trim(Catalog) & ";" _
    & "Data Source=" & Trim(DataSource)
    ' Open the connection
    NewConnection.Open

Any help would be greatly appreciated.






Larry Asher

__________________
Larry Asher

Larry, your connection string determines whether it uses trusted connection (i.e. win logon) or not. At the moment the conn str you're using is a bit of a mix-up. Basically, if you want to use Windows login, you include Integrated Security=SSPI; and leave out User ID and Password in the conn str,

but if you want SQL login you leave out the Integrated Security=SSPI and instead supply User ID and Password.

If users are in different domains it probably depends on the trusts set-up between the domains in question - I'll do some digging around on that aspect.

hth
Phil

You are absolutely correct. I did notice that after I had made the post. In addition I set the SQL Authentication back to mixed mode and changed my connection logic, submitted below. I attempt to connect to the server using integrated security and this works if the user is logged into the domain. If that fails I then prompt the user to authenticate. However, the problem I am having is nothing works if the user is not logged into the domain. I even went to the ODBC tools and tried to setup a DSN but ran into the same problem. Since, there are only two modes to choose from as methods of authentication and neither of them work. I am leaning in the same direction as you statement about a trust between the servers/domains. I am going to talk with the networking guys next and see what can be done.

Thanks for you help.

Here is the code I am using to establish the connection. If you see any problems feel free to comment.

Public Sub ConnectToDB(ByVal UserID As String, ByVal Catalog As String, _
ByVal DataSource As String, ByRef NewConnection As ADODB.Connection)

    On Error Resume Next

    ' Create a new connection
    Set NewConnection = New ADODB.Connection
    ' Set the cursor to the client side
    NewConnection.CursorLocation = adUseClient
    ' Build the connection string
    ' Use this if the user is logged into the domain
    NewConnection.ConnectionString = "Provider=SQLOLEDB.1;" _
    & "Integrated Security=SSPI;" _
    & "Persist Security Info=True;" _
    & "Initial Catalog=" & Trim(Catalog) & ";" _
    & "Data Source=" & Trim(DataSource)
    ' Open the connection
    NewConnection.Open
    ' Test for any errors
    If NewConnection.State = adStateOpen Then
        ' Prompt the user for username and password
        NewConnection.Provider = "SQLOLEDB"
        NewConnection.Properties("Prompt") = adPromptAlways
        ' Open the connection
        NewConnection.Open "Data Source=" & Trim(DataSource) & ";" _
        & "Initial Catalog=" & Trim(Catalog) & ";"
    Else
        ' Return the error
        Err.Raise Err.Number, Err.Source, Err.Description
    End If

    Exit Sub

ErrorHandler:

    ' Any errors will be returned to the client
    Err.Raise Err.Number, "clsSQLDBAccess\ConnectToDB", Err.Description

End Sub

Larry Asher

Larry, I'm not sure about this test in your code:
If NewConnection.State = adStateOpen Then
        ' Prompt the user for username and password

shouldn't that be testing the state <> adStateOpen? i.e. if the trusted conn didn't work then try again prompting the user.

Does this remote user login to a different domain and then try and connect via your ASP page? What have you set-up in SQL Server to allow this user access to the db? (I'm talking here about granting them access to the server and relevant databases using things like sp_grantlogin - specifically which domain\user combination was set-up, or is it all done via Win Group membership?)

Finally are we talking about SQL2000/Kerberos, or SQL7/NTLM?

rgds
Phil

Larry, one other thing, when trying to connect this user with a SQL user/password check out which protocol the user is connecting with- TCP/IP or Named Pipes. If it's Named Pipes then you're going to have problems because that's an authenticated protocol so it's likely the connection will be rejected before the user/pass has a chance to be submitted to the server. TCP/IP isn't authenticated so switch the user to that method.