pop up sql server login box after updating access 365 to version 2604 (1 Viewer)

[ywin]

using dns-less link to azure sql database, pop up sql servre login box.
open pass-through query also pop up.

no such error in version 2603.

 

[isladogs]

I've heard one other report today for SQL Server ODBC connections in version 2604 build 19929.20032 (Current Channel (Preview) which may possibly be related to your issue. However it is not at all clear what issue you are having.

Please provide more details of your issue including screenshots.
Also give details of the ODBC connection method used together with the exact build & update channel info

 

[ywin]

when login:
Set qdf = CurrentDb.CreateQueryDef("")
With qdf
.Sql = "SELECT 1 AS cache"
.Connect = "ODBC;" & GetOdbcCSt("sfshio4723jc")
.ReturnsRecords = False
.Execute
End With

 

[isladogs]

Sorry but that doesn't help much.

Screenshot?
Can you show the full connection string. You are using a function which masks the actual connection string.
Version / build / update channel?

 

[ywin]

after login, pop up sql server login box
open linked table, open pt query,open form pop up

update odbc driver to laster version: 18.6.2.1,pop up sql login box.
roll back to version 2603 20168,everything is fine
using dns-less connect to azure sql databse,connection string just include driver,server,database,no uid and pwd
update channel is current channel
i also post on microsoft access community

 

[ywin]

current channel
it does not matter.
i think the most important thing is that odbc cache is key for access link sql.
if MS change the rule.is there any way to link sql securely?

 

[isladogs]

Thank you. That indicates you have the same issue as with two other ODBC reports sent to me yesterday.
Those had provided the detail I needed to pass on to the Access team and I got a response from them in just a few hours.

Very odd that you state this is Current channel version 2604. Current Channel is still on version 2603 19822.20182.
However Current Channel (Preview) is on 2604 19929.20062.

Anyway, the A-team stated that the problems were due to a recent change that 'improved' how Access handles credential caching for ODBC connections. Unfortunately, when a linked table's connection string omits UID/PWD (because it relies on credentials cached by a prior pass-through query), the new code was interpreting the missing credentials as a "change" and so flushed the cached connection. This forced the login prompt.

The A-team is in the process of disabling the recent change to restore the previous behaviour. It may be in place by the time you read this and should take effect without requiring a build update or rollback. You may need to restart Access for this to take effect. Please test & let me know the outcome.

They will next fix the underlying code, so it only flushes the cache when supplied credentials differ from the cache, rather than when they're simply absent from the connection string (as in your situations).

To assist the A-team with this, please could you share the full connection string being used for the pass-through query and the linked table DSN?
If possible please translate that info into English. Any sensitive data such as passwords should be replaced with dummy values - they just need to see the structure and which keys are present (UID, PWD, DRIVER, SERVER, DATABASE, Trusted_Connection etc). This info will be used to validate their fix against the exact scenario.

Feel free to send this info via a direct message if you don’t want that info to be circulated to the entire forum.

 

[ywin]

ODBC;DRIVER={ODBC driver 18 for sql server};Server=tcp:xxxxxxxx,1433;Database=xxxxxxx;Uid=xxxxxxx;Pwd=xxxxxxxxxx;Encryption=yes;TrustServerCertificate=no;Connection Timeout=30;Pooling=True;Min Pool Size=5;Max Pool Size=100;

 

[ywin]

Set tdf = db.CreateTableDef(strTableName)
tdf.Connect = "ODBC;" & GetOdbcCStForDAOWith3Parameters("sfshio4723jc")
tdf.SourceTableName = strTableName
db.TableDefs.Append tdf

If IsNull(Trim(objRst.Fields("Primary_Key"))) = False And Trim(objRst.Fields("Type")) = "VIEW" Then
strSQL = "Create Index IX_" & strTableName & " On " & strTableName & " (" & objRst.Fields("Primary_Key") & ") WITH PRIMARY"
CurrentDb.Execute strSQL
End If

GetOdbcCStForDAOWith3Parameters("sfshio4723jc")
DRIVER={ODBC driver 18 for sql server};Server=tcp:xxxxxxxxxxxxxxxxxxxxx;Database=xxxxxxxxxx;

 

[isladogs]

Thanks. I’ll pass that on. Can you check whether it’s now working again in v2604

 

[ywin]

sound there is no need keysooling,min pool size,max pool size.
for speed login action, link table just ones, next time open database, there is no need recreate link table after odbc cached.

 

[isladogs]

Sorry but I don’t understand your last post. Possibly a language issue in translation.

 

[ywin]

enable update, update now, but system return This is the latest status.

 

[isladogs]

OK so you’re back in CC v2603 which was working anyway.

 

[ywin]

for security reason,MS must consider that disable share credentials cached within same access process.

 

[isladogs]

For info, the security change in 2604 has now been disabled in all channels with builds >=19929.20000. It may take a few hours for that change to propagate to all users.

I do have more details about the background for this security change but I have shared all that I am allowed to put in a public forum.

I have also passed on the connection string details for the three people who reported this bug. The Access team has asked me to pass on their thanks as that info will help in testing the amended code that will be released in the future. That will follow the approach that I outlined in post #8.