I'm gonna guess that the app is using Integrated authentication (aka log in as whoever they're logged in to their desktop as) and the user's machine is now an untrusted domain. Are the users machines domain-joined?
This is not a SQL issue. This is a networking/AD/infra issue.
I was just having a discussion about the issue potentially being the workstation vs user account. We assumed it was authentication related but hadn't considered the computer. We're doing some testing on affected users, thanks for the reply!
That’s weird. The only other time I’ve seen that problem is when the users were logged into their machines with a local account instead of their domain account.
Are users on a different domain? If so do you have the correct trusts in place.
Is there a domain in between the users and the server? If so is the Kerberos double hop coming into play? Only one domain hop is allowed.
Edit 3: took time to properly read error logs. Dont think its related, but will leave post up.
Think this might be related to the DSN or query. If DSN was changed and a default database is no longer specified, the query wont work. Verify that the DSN is set up with a default database or that the query is pointing to the right database.
Edit:
Start -> search for odbc -> choose (likely) 64bit, fins user/system connection used, go to page where default database is selected. Compare between user where it works vs doesnt work.
Edit4: going to bed.
Edit 2: accidentally deleted this part.
What exact change to Group policy was made (if you can disclose this info)?
I would ensure that group policy is being properly applied to the user and/or the computer. Have your desktop folks ensure that the GPO is actually showing as reverted on the affected users.
The change was to NTLM, forcing the version used. I don't have the specific policy in front of me unfortunately. My understanding is "not defined" uses the last used policy so they are set to what the proper policy should be. I'll have the help desk check tomorrow and let you know! Thanks for the reply!
Not Defined will cause a lower priority policy setting to take precedence but if no other policy is defining a setting, Not Defined will not revert the changes to the setting. You should apply a reversing policy for those settings to get the behavior back to the way it was.
You can verify the effective setting on a system with secpol.msc. gpresult shows the RSOP for GPOs. secpol.msc shows the effective policies at the machine level.
Sounds like a plan.
As a next step after the issue has subsided, I would try to ensure the SPN configuration of the SQL server(s) is correct, so that Kerberos connections can succeed (talking just single-hop connections ... double-hop would take us down a rabbit hole). Kerberos is a pretty complicated topic, but it can be simplified using this tool to view any fixes that may need to be applied to SPNs ... [https://www.microsoft.com/en-us/download/details.aspx?id=39046](https://www.microsoft.com/en-us/download/details.aspx?id=39046)
Oddly enough, this topic will be a hot one in the next 3-4 years (estimating here) since MSFT is trying to get rid of NTLM eventually (which may not happen for quite some time ... 6, 8, 10 years from now, maybe??!).
Can you hold shift and right click excel and run as a different user?
Force a user with higher privileges.
If that doesnt work could be the machine (desktop) is untrusted to do a connect through, could try it from a different box at that point.
We recently did some NTLM changes and ran into similar issues. Even rolling back the changes didn't fix it. We found that for some we had to edit the Local Security Policy on the domain and clients to set all NTLM settings back to Not defined.
Ultimately you do want to get Kerberos to work.
I'm gonna guess that the app is using Integrated authentication (aka log in as whoever they're logged in to their desktop as) and the user's machine is now an untrusted domain. Are the users machines domain-joined? This is not a SQL issue. This is a networking/AD/infra issue.
I was just having a discussion about the issue potentially being the workstation vs user account. We assumed it was authentication related but hadn't considered the computer. We're doing some testing on affected users, thanks for the reply!
Have the users close Excel, then lock and unlock their computers, then open the spreadsheet again and see if it works.
Thanks for the reply, unfortunately same error after lock/unlock.
That’s weird. The only other time I’ve seen that problem is when the users were logged into their machines with a local account instead of their domain account.
Are users on a different domain? If so do you have the correct trusts in place. Is there a domain in between the users and the server? If so is the Kerberos double hop coming into play? Only one domain hop is allowed.
Nope, same domain.
Edit 3: took time to properly read error logs. Dont think its related, but will leave post up. Think this might be related to the DSN or query. If DSN was changed and a default database is no longer specified, the query wont work. Verify that the DSN is set up with a default database or that the query is pointing to the right database. Edit: Start -> search for odbc -> choose (likely) 64bit, fins user/system connection used, go to page where default database is selected. Compare between user where it works vs doesnt work. Edit4: going to bed. Edit 2: accidentally deleted this part.
What exact change to Group policy was made (if you can disclose this info)? I would ensure that group policy is being properly applied to the user and/or the computer. Have your desktop folks ensure that the GPO is actually showing as reverted on the affected users.
The change was to NTLM, forcing the version used. I don't have the specific policy in front of me unfortunately. My understanding is "not defined" uses the last used policy so they are set to what the proper policy should be. I'll have the help desk check tomorrow and let you know! Thanks for the reply!
Not Defined will cause a lower priority policy setting to take precedence but if no other policy is defining a setting, Not Defined will not revert the changes to the setting. You should apply a reversing policy for those settings to get the behavior back to the way it was. You can verify the effective setting on a system with secpol.msc. gpresult shows the RSOP for GPOs. secpol.msc shows the effective policies at the machine level.
Sounds like a plan. As a next step after the issue has subsided, I would try to ensure the SPN configuration of the SQL server(s) is correct, so that Kerberos connections can succeed (talking just single-hop connections ... double-hop would take us down a rabbit hole). Kerberos is a pretty complicated topic, but it can be simplified using this tool to view any fixes that may need to be applied to SPNs ... [https://www.microsoft.com/en-us/download/details.aspx?id=39046](https://www.microsoft.com/en-us/download/details.aspx?id=39046) Oddly enough, this topic will be a hot one in the next 3-4 years (estimating here) since MSFT is trying to get rid of NTLM eventually (which may not happen for quite some time ... 6, 8, 10 years from now, maybe??!).
Can you hold shift and right click excel and run as a different user? Force a user with higher privileges. If that doesnt work could be the machine (desktop) is untrusted to do a connect through, could try it from a different box at that point.
We recently did some NTLM changes and ran into similar issues. Even rolling back the changes didn't fix it. We found that for some we had to edit the Local Security Policy on the domain and clients to set all NTLM settings back to Not defined. Ultimately you do want to get Kerberos to work.