Connection to sql server via dsn

[roberto21]

Assuming that by "remote computer" you mean the one from which I am attempting the connection, of course I tested it. It is a system dsn: as I said, the connection verification works, the connection in the code doesn't. The same dsn in the server machine works fine.

[NoellaG]

The only other thing I can think about is something is missing/wrong in the VBA code. As SQL dba I am not an expert in VBA coding, so maybe another programmer can help here.

[roberto21]

By remote computer I assume you mean the one from which I atempt th connection. Of course, here the DSN is tested at the end of the cration: it work ok here, and fail in the code.
Naive solution to the problem: since it consistently refuses to pass the userid, let's force it:

cnn.open ("DSN=Driver18;UID=sa;PWD=xxxxx")

this works.... I do not consider this a solution, only a bypass. I'd like an explanation of the reason why the dsn drops the userid. Or is it a bug?

[NoellaG]

Could be VBA doesn't work well with DSN files

[roberto21]

OK, I think we can close this discussion. It was my fault, I did not search enough. Simply stated, the password you supply in the dsn definition is only used to check the connection at the end, but is not saved in the file for security reasons. The explanation is found here

https://learn.microsoft.com/en-us/sq...l-server-ver16

The key sentence is

This field only applies to the connection made to determine the server default settings; it doesn't apply to subsequent connections made using the new data source.

Of course, the error in the log is completely misleading, but this is another matter.
Thank you all for your interest and help.

[NoellaG]

thx for sharing the insight and solution