Connection to sql server via dsn

[roberto21]

I am having a problem to connect to Sqlserver using a system dsn file.
The server is configured for windows and sql authentication, and I can access it without any problem with Management studio specifyng user ( sa) and pwd.
Now I am trying to execute a stored procedure in the server from excel vba. So I prepared a system dsn specifying all requested values (sql authentication using userid and password), and the connection test at the end of dsn definition works OK. However, if in the vba code I use

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open ("DSN=Driver17Backup")

I get the error



If I use a "normal" connection string like this

connString = "Driver={ODBC Driver 18 for SQL Server};server=desktop-fmbj0fj\sqlexpress;database=RicevuteCPNCompletoBackup;" _
            & "trustedConnection=No;encrypt=optional; uid=sa;pwd=xxxxxx; trustservercertificate=yes;"
 cnn.Open connString

everything works without problems. Does this mean that the dsn file contains parameters other than those in the connection string? This seems strange.
An element that may help someone, but did not help me: If I run the code in the same machine where the server is, cnn.open (dsn=driver18Backup) works fine, so it looks like a network problem. But what is the problem, since management studio and the connection string work from a different machine?

[jojowhite]

just maybe, the problem is with the odbc driver itself (ver 17), but cannot
test if it is so.

[Gustav]

If "ODBC Driver 18 for SQL Server" works, then use that.

Side note: You should create your own admin user and deactivate the SA user.

[roberto21]

Thanks a lot for your suggestions but no, I tested also with driver 18 and, to be on then safe side, also with sql native 11.0 with the same errorI confirm that using dsn in the same machine as the server works fine, at least I tried with driver 18. For the moment, I use the connection string , that works fine, but the problem remains.

[NoellaG]

The error states that the login failed and gives an empty user name. So this happens before the database user ID can be validated. Check how you have created the DSN. If with Windows authentication, be aware that the windows user of your PC differs from a direct login to the SQL server machine.

[roberto21]

I​ am using the very same dsn from a pc in lan and the pc where the server is located. The dsn is created with sql authentication, with uid and password. This (system) dsn works perfectly in the server pc and fails from a pc in lan. I agree that the userid seems to disappear in this second case, but this is exactly the problem. Who or what cancels the userid?
Here is the final screenshot of the dsn creation, and the connection test works fine

[NoellaG]

the userid is probably canceled because the database server login is incorrect. SQL security is 2 layered: first the login is tested on server level, then user ID is checked on database level. So there is something wrong on the server level.
If the track failed logins is activated on this server you can see the error description in the error log of your SQL server ( default location for the error log is <drive>:\Program Files\Microsoft SQL Server\MSSQL.<version nr>\MSSQL\LOG\ERRORLOG)

[roberto21]

All right, I found a bunch of log files, and the last one corresponding to my last attempt shows something that should clarify the issue:

2025-04-10 18:45:24.15 Logon Error: 18456, Severity: 14, State: 58.
2025-04-10 18:45:24.15 Logon Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: 192.168.178.35]


But to me this does not explain anything. I assume "integrated authentication only" means window authentication (but of course I could have misinterpreted). The fact is that the server is configured in mixed mode authentication, as you can see in these snapshots of ssms





(I found the query in some post in the web). In addition, following another suggestion found in the web, I restarted the server service, in the remote possibility that the authentication change needed this restart (but I can access the server with userid and password with ssms and with the connection string, so it allows sql authentication). Anyhow, the error persists: there must be something else, and I am too inexpert to understand what. Thank you very much for your interest and help

[NoellaG]

Have you checked the server property Connections -> Allow remote connections?

[roberto21]

There you go. But the fact that I can connect with ssms and connection string does not already confirm that remote conections are allowed? Sorry for the naive question.

[NoellaG]

SSMS uses a .net protocol and connects to the default port 1433. You probably installed everything as owner, so had no trouble connecting. In this link you can find extra info setting up remote connections: https://learn.microsoft.com/en-us/sq...l-server-ver16 https://www.sqlservercentral.com/art...ote-sql-server

[roberto21]

I tried to follow diligently the suggestions in the links, but nothing works. The connection with connection string works perfectly, the connection with dsn fails with the same error. OK, I can survive with the connection string, but I would like to understand what I am missing in the damn dsn

[NoellaG]

all errors say your DSN passes an empty user name to the server.

[roberto21]

You may be right in assuming that I am making some mistakes in the definition of the dsn file: I would only like to understand what are those errors. Remember that in one of the previous posts I attached the final screenshot of the definition, where there is the connection verification step, and there everything works: connection attempt... connection done.... setting verification...disconnect from server... VERIFICATION COMPLETED. I am not that expert, but it seems strange that the same dsn is "promoted" and then fails when used. In other words, the dsn is OK here and not ok there?
Is it possible that the connection is done in a different way than the one used for the verification test? I know that this seems a guessing game, but what should I do?

[NoellaG]

Did you test the DSN on the remote computer? Is it a system or user DSN?