Execution Context for MS Access front end to SQL Server backend

Question

Hello,

we have a business application that is based on MS Access front end and SQL Server backend. Tables are linked via DNS-less connection. The application is using SQL Server Authentication using an ODBC connection, driver is SQL Server (connection string is as per below

ODBC;DRIVER={sql server};SERVER=Sername;DATABASE=dbname;UID=appuser;PWD=apppassword;Trusted_Connection=No;

)

Any interactions with objects etc are loading the connection string, so all code based interactions are executed in the same context.
I have come across, from what seems random occurrences, where an Insert Statement will get executed as the domain user instead of the SQL Server user account and the insert statement will fail.

I ran the Standard Profiler on the Server to get on the bottom of the symptoms.

Below, the in the red-brown color, an insert statement was executed as the domain user and it failed with ODBC Error 3155 the insert permission was denied on object TABLE, database DATABASE, schema dbo.

I got the user to exit out of the current action, do something else (like open a report, which worked) and then trying to do the same action again, which worked this time. The profiler showed that the action was executed as the SQL Server user.

Could someone explain how this can happen and how I can control this better?

Environment is Windows Server 2022, M365, SQL Server 2019 Standard.

Thanks!

Answer 1

Hi, MKinOZ

From your description, it looks like you've encountered an unexpected execution context switch while using ODBC to link to a SQL Server database in your application.

The issue may be related to the design of your application (it is recommended to pay attention to statements that use Windows authentication to link to the database and ensure that the connection is closed after each link).

Of course, there are many reasons that can cause execution context switches (such as SQL Server triggers, functions, etc., but since the command was successfully executed using your alternative method, it doesn't seem to be this point).

In addition, you can also monitor the process of context switching through the SQL Profiler you mentioned. Here are the suggested options:

• Audit Login
• Audit Logout
• Audit Login Failed
• ExistingConnection
• Audit Database Scope GDR Event
• Audit Schema Object GDR Event
• Audit Addlogin Event
• Audit Login GDR Event
• Audit Login Change Property Event

Best regards

Mikey Qiao