ODBC Connection Error Cannot generate SSPI (1 Viewer)

jsdba

Registered User.
Local time
Today, 17:54
Joined
Jun 25, 2014
Messages
165
Here is my setup.

Access 2010, SQL Server 2016 Std Edition

On my server i have Express, Developer and Standard edition installed

Users are connected though ODBC File.DSN (on a shared network drive) using Server = ServerName(IP Address)\InstanceName,PortNumber

I have 50 users connected at any given time with no problems. However i have 2 user who constantly get this error throughout the day. If they restart their computer they can reconnect for about an hour then it happens again. I'm out of ideas at this point.

See attachment for image of error.

Any help is appreciated.
 

Attachments

  • ODBC Error.PNG
    ODBC Error.PNG
    12.3 KB · Views: 52

isladogs

MVP / VIP
Local time
Today, 21:54
Joined
Jan 14, 2017
Messages
18,186
It sounds very much like the SQL Server service stops for some reason.
Either that or you are hitting a concurrent user limit ...but that seems unlikely as it wouldn't always be the same users.

You may find the following useful to check if the SQL Server service is running

Code:
Public Function CheckSQLServerConnection() As Boolean 

'returns true if SQL Server is running and the listed database is available
'Otherwise false

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection

CheckSQLServerConnection = False

    Set cnn = New ADODB.Connection
   'modify to suit your SQL database settings
    cnn.Open "Provider=SQLOLEDB;Data Source=" & GetSQLServerName() & ";Initial Catalog=SDABE;User ID=SDAuser;Password=SDApassword"
    
    If cnn.State = adStateOpen Then
        CheckSQLServerConnection = True
        cnn.Close
    End If

    'Debug.Print CheckSQLServerConnection
    
Exit_Handler:
    Exit Function
    
Err_Handler:
    'err = -2147467259 'can't open SQL database - server or database name incorrect or SQLServer not running
    'err = -2147217843 'incorrect UserID / password
   
    If err = -2147467259 Or err = -2147217843 Then
        Exit Function
    Else
        MsgBox "Error " & err.Number & " in CheckSQLServerConnection procedure : " & vbNewLine & _
            "  " & err.Description & "   ", vbCritical, "SQL Server error"
    End If
    Resume Exit_Handler

End Function
 

jsdba

Registered User.
Local time
Today, 17:54
Joined
Jun 25, 2014
Messages
165
It sounds very much like the SQL Server service stops for some reason.
Either that or you are hitting a concurrent user limit ...but that seems unlikely as it wouldn't always be the same users.

You may find the following useful to check if the SQL Server service is running

I know for sure that service is running. Again i have 50 users connected at the same time.
 

isladogs

MVP / VIP
Local time
Today, 21:54
Joined
Jan 14, 2017
Messages
18,186
What I meant was that for those users the service gets interrupted.

Anyway, the link Paul provided appears specific to your issue
 

Users who are viewing this thread

Top Bottom