Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 06:20 AM   #1
jsdba
Newly Registered User
 
Join Date: Jun 2014
Location: NY, USA
Posts: 133
Thanks: 34
Thanked 1 Time in 1 Post
jsdba is on a distinguished road
ODBC Connection Error Cannot generate SSPI

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.
Attached Images
File Type: png ODBC Error.PNG (12.3 KB, 10 views)

jsdba is offline   Reply With Quote
Old 06-14-2018, 06:42 AM   #2
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: ODBC Connection Error Cannot generate SSPI

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 06-14-2018, 06:52 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,467
Thanks: 9
Thanked 3,760 Times in 3,703 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: ODBC Connection Error Cannot generate SSPI

I've never had that error but perhaps this will help:

https://support.microsoft.com/en-us/...-error-message

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
jsdba (06-14-2018)
Old 06-14-2018, 07:24 AM   #4
jsdba
Newly Registered User
 
Join Date: Jun 2014
Location: NY, USA
Posts: 133
Thanks: 34
Thanked 1 Time in 1 Post
jsdba is on a distinguished road
Re: ODBC Connection Error Cannot generate SSPI

Quote:
Originally Posted by pbaldy View Post
I've never had that error but perhaps this will help:

https://support.microsoft.com/en-us/...-error-message
In my research i found similar answers pointing in this direction. I downloaded it but now i cant find the .exe file to launch the app.
jsdba is offline   Reply With Quote
Old 06-14-2018, 07:26 AM   #5
jsdba
Newly Registered User
 
Join Date: Jun 2014
Location: NY, USA
Posts: 133
Thanks: 34
Thanked 1 Time in 1 Post
jsdba is on a distinguished road
Re: ODBC Connection Error Cannot generate SSPI

Quote:
Originally Posted by ridders View Post
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.
jsdba is offline   Reply With Quote
Old 06-14-2018, 07:32 AM   #6
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: ODBC Connection Error Cannot generate SSPI

What I meant was that for those users the service gets interrupted.

Anyway, the link Paul provided appears specific to your issue
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 06-14-2018, 07:46 AM   #7
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,301
Thanks: 77
Thanked 1,294 Times in 1,209 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: ODBC Connection Error Cannot generate SSPI

Quote:
Originally Posted by jsdba View Post
In my research i found similar answers pointing in this direction. I downloaded it but now i cant find the .exe file to launch the app.
Try https://www.microsoft.com/en-us/down....aspx?id=39046

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
jsdba (06-14-2018)
Old 06-14-2018, 08:11 AM   #8
jsdba
Newly Registered User
 
Join Date: Jun 2014
Location: NY, USA
Posts: 133
Thanks: 34
Thanked 1 Time in 1 Post
jsdba is on a distinguished road
Re: ODBC Connection Error Cannot generate SSPI

Quote:
Originally Posted by ridders View Post
I got it. I will look into it and update tread on results later
Attached Images
File Type: png Kerberos.PNG (21.8 KB, 6 views)

jsdba is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC Connection Error jsdba Modules & VBA 4 08-20-2015 10:23 AM
Oracle ODBC Connection Error 3151 Insane_ai Modules & VBA 4 01-29-2013 06:03 AM
Secure connection using ODBC connection latex88 General 1 08-19-2012 08:14 AM
ODBC connection to SQL scubadiver007 General 0 11-03-2011 09:02 AM
Run Time Error 3151 ODBC Connection purceld2 General 1 02-11-2009 12:25 AM




All times are GMT -8. The time now is 10:59 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World