10-04-2023 10:14 AM
I have an issue with database connection (located on another computer) by creating my own connection string.
Previously, I used driver SQL Server Native Client 11.0 and connection string in form of:
"Provider=SQLNCLI11; Server=xxx:xxx:xxx:xxx,yyyyy\instName; Database=dbName; User Id=aaa; Password=bbb"
worked well.
However, now I had to switch to SQL ODBC and now connection fails with error -10 from DBConnect.
Here: https://www.connectionstrings.com/microsoft-odbc-driver-17-for-sql-server/
I have read that there may be a difference in connection string composition, so I tried numerous combinations with no success.
I can set up proper DSN for connection to use like: DBConnect("DSN=TestDsn") and it works fine.
To find out the proper way out of DNS connection, I tried to trace ODBC connections and analyze it, but it did not have any connection string inside - the closest information I got is:
It is unlucky that ODBC administrative tool cannot trace proper test connection made from itself to view connection string.
Do you know how to properly construct connection string for SQL Server 2022 (ODBC Driver 17 for SQL Server) ?
Alternatively, is there any other ODBC tracing library than odbctrac.dll to trace the connection string used in correct connection?
Solved! Go to Solution.
10-10-2023 10:54 AM - edited 10-10-2023 10:56 AM
The DSN settings are stored in the registry somewhere. You can look through the registry to see the actual data of the DSN, which is more or less list of settings as combined into a connection string.
System DSNs are stored under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
User DSNs are stored under HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI
There is an additional complication about 32-bit System DSNs being stored under the WOW6432Node subkey.
10-11-2023 08:40 AM
Thank you for your suggestion rolfk. These settings are indeed stored in registry and it will be very useful for me to look up for it to check my setup in the future.
I recently found out that there were two reasons for connection failures. In case anyone falls into the same issues:
They must be input in curly braces, e.g.: Driver={ODBC Driver 17 for SQL Server}.
Sample, valid connection strings for ODBC 17:
Driver={ODBC Driver 17 for SQL Server};Server=xxx.xxx.xxx.xxx,port;Database=databaseName;Uid=login;Pwd=password;
Driver={ODBC Driver 17 for SQL Server};Server=compName\servName,port;Database=databaseName;Uid=login;Pwd=password;
I also found very useful link to find out which drivers are compatible with specific versions of SQL Server:
Drivers compatibility