LabWindows/CVI

cancel
Showing results for 
Search instead for 
Did you mean: 

SQL connection string with IP / Port / Instance

Solved!
Go to solution

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:

2eec-4038 EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
HDBC                0x75ACC920
HWND                0x00000000
WCHAR *             0x75C52530 [      -3] "******\ 0"
SWORD                       -3 
WCHAR *             0x75C52530 
SWORD                       -3 
SWORD *             0x00000000
UWORD                        0 <SQL_DRIVER_NOPROMPT>
 
DIAG [08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Timeout occured.
 (258) 
DIAG [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258) 
DIAG [S1T00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) 

 

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?

0 Kudos
Message 1 of 3
(4,529 Views)

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.

Rolf Kalbermatter
My Blog
Message 2 of 3
(4,456 Views)
Solution
Accepted by topic author chris102

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:

 

  1.  Invalid / incompatible install of SQL Runtime Engine (which is separate from CVI Runtime Engine!). When I reinstalled it on the computer, I started to get timeouts instead of instant failures. In my case, I updated it in my application's installer and reinstalled my application (previously built with CVI17, now CVI2020).

  2. Connection strings has indeed changed for newer drivers:
  • Native Client and OLE DB drivers' connection strings required field called 'Provider', which was basically a name of driver's DLL (e.g. SQLNCLI11),
  • ODBC drivers' connection string now require field called 'Driver'. From the Microsoft's documentation, valid 'Drivers' are:

    • ODBC Driver 11 for SQL Server
    • ODBC Driver 13 for SQL Server
    • ODBC Driver 17 for SQL Server
    • ODBC Driver 18 for SQL Server

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
 

0 Kudos
Message 3 of 3
(4,440 Views)