I’m trying to connect to a MS SQL Server (actually, a serverless SQL service on Azure) from a Linux instance (Ubuntu 24.04). I have installed Microsoft’s ODBC driver (version 18) as per this link, and added the information for the server to /etc/odbc.ini (server name and DB name replaced):
[ODBC Data Sources]
Local Virtuoso = OpenLink Virtuoso
Local Virtuoso (Unicode) = OpenLink Virtuoso
AzureSQL = AzureSQL
[Local Virtuoso]
Driver = OpenLink Virtuoso
Address = localhost:1111
[Local Virtuoso (Unicode)]
Driver = OpenLink Virtuoso
Address = localhost:1111
[AzureSQL]
Driver = ODBC Driver 18 for SQL Server
Server = myservername.database.windows.net
Database = mydatabase
The data source shows up in Virtuoso’s External Data Sources tab, but when I try to connect, I get this error message after a few seconds of waiting:
Invalid data entered
VD052: Remote DSN AzureSQL: ????????????????????????????????????9
UnixODBC’s isql connects instantly and with no problems using the command isql AzureSQL <myusername> <mypassword>, and I’m able to see the data.
All help greatly appreciated! Thanks!
Please try adding the DriverUnicodeType = UTF16 setting in the /etc/odbcinst.ini file for the ODBC Driver 18 for SQL Server ODBC Driver ie something like
[ODBC Driver 18 for SQL Server]
Description = Microsoft ODBC Driver 18 for SQL Server
Driver = /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.10.so.6.1
UsageCount = 1
DriverUnicodeType = UTF16
To indicate to iODBC that his is a UTF16 rather than UCS4 unicode type driver which iODBC defaults to.
Thank you for your quick reply! After I added DriverUnicodeType = UTF16 to /etc/odbcinst.ini, Virtuoso crashes when I try to connect to the data source. Nothing gets written to the log, the process just dies (segmentation fault). UnixODBC’s isql doesn’t seem to care one way or another, continues working as before.
What is the version, gitid and build date of Virtuoso being used ? Please check in the virtuoso.log file which shows these details on startup.
Version 08.03.3332-pthreads for Linux as of Sep 11 2024 (363f8fdcf3)
Thought I’d try out version 17 of the Microsoft Driver, got the same results.
I’ve got the things working somehow, or at least I’m further along.
First, a bit of context: my purpose here has been to build a Docker container that would run Virtuoso and be able to connect to my Azure SQL database.
My first attempt was to start with the openlink/virtuoso-closedsource-8 image, and install the MS ODBC drivers on top of it, but the drivers wouldn’t install: the post-installation script failed.
My second attempt was to start with an Ubuntu image, install the drivers, then Virtuoso. I used the binary download. That was the context of this post.
My third attempt was to start with an Ubuntu image, install the drivers, then install Virtuoso using the commands from the virtuoso-closedsource-8 image (replacing focal with noble in the source definitions.) For whatever reason, that got me a system that does connect, and I can link a table!
I am seeing an odd behavior with the linked table: when I try to get a row count (select count(*) from sample.AzureSQL.Products;), I get an incorrect response: 108233175917541. Other queries seem to be working OK. Should I create a separate post on this oddity? Also, would you guys like me to provide you with more data on the original issue?
The crash you were encountering was due to the use of the Version 08.03.3332-pthreads for Linux as of Sep 11 2024 (363f8fdcf3) Generic glibc2.5 build which statically built withOpenSSL 1.0.2, which is incompatible with the system OpenSSL 3.x used by the MS ODBC Driver 18 for SQL Server driver.
Whereas when you used the docker based binary, which is native for Ubuntu 24.04, it is dynamically linked the same OpenSSL 3.x used by the MS ODBC Driver 18 for SQL Server driver and hence connects.
With regards to this new problem, is the sample.AzureSQL.Products table from the default sample Northwind SQL Server database ? As I have setup a test case and against an Azure SQL database we have setup with the Northwind database and it returns the correct count of 77:
SQL> SELECT count(*) FROM "Northwind"."sql18na"."Products";
aggregate
INTEGER NOT NULL
_______________________________________________________________________________
77
1 Rows. -- 862 msec.
SQL>
Having said that I have seen other issues using the MS ODBC Driver 18 for SQL Server driver, like the querying the Products table itself some of the smallint types are being returned incorrectly:
SQL> SELECT top 5 * FROM "Northwind"."sql18na"."Products";
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
INTEGER NOT NULL VARCHAR NOT NULL INTEGER INTEGER VARCHAR DECIMAL SMALLINT SMALLINT SMALLINT VARCHAR NOT NULL
_______________________________________________________________________________
1 Chai 60129542145 60129542145 10 boxes x 20 bags 18 13563781598674983 13563781598674944 13563781598674954 13563781598674944
2 Chang 140312286593025 140312286593025 24 - 12 oz bottles 19 13563781598674961 13563781598674984 13563781598674969 13563781598674944
3 Aniseed Syrup 13563781598674945 13563781598674946 12 - 550 ml bottles 10 13563781598674957 13563781598675014 13563781598674969 13563781598674944
4 Chef Anton's Cajun Seasoning 13563781598674946 13563781598674946 48 - 6 oz jars 22 13563781598674997 13563781598674944 13563781598674944 13563781598674944
5 Chef Anton's Gumbo Mix 13563781598674946 13563781598674946 36 boxes 21.35 13563803073511424 13563803073511424 13563803073511424 13563803073511425
5 Rows. -- 187 msec.
SQL>
which needs to be looked into …
Note we provided our own SQL Server ODBC Driver and are fully tested against Virtuoso and thus do query the table as expected:
SQL> SELECT top 5 * FROM "Northwind"."sqllu_uda9"."Products";
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
INTEGER NOT NULL NVARCHAR NOT NULL INTEGER INTEGER NVARCHAR DECIMAL SMALLINT SMALLINT SMALLINT SMALLINT NOT NULL
_______________________________________________________________________________
1 Chai 1 1 10 boxes x 20 bags 18 39 0 10 0
2 Chang 1 1 24 - 12 oz bottles 19 17 40 25 0
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10 13 70 25 0
4 Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars 22 53 0 0 0
5 Chef Anton's Gumbo Mix 2 2 36 boxes 21.35 0 0 0 1
5 Rows. -- 1533 msec.
SQL>
Thus I would suggest trying our driver …
Thanks for a detailed and informative explanation! Makes sense.
This is not the Northwind table, it’s a sample data table from our customer, it has around 58k rows. Furthermore, I noticed that the behavior is intermittent, sometimes it will return the correct result, and once it had done it once, it seems that it keeps returning the right result for the lifetime of connection. UnixODBC’s isql always seems to return the right result, though, so it’s not like the MS driver is completely broken.
Is there any kind of logging I can examine to see what calls Virtuoso is making into the driver, and what it’s getting back? Is there any information that I can gather to make debugging this easier?
You can enable Virtuoso tracing with the trace_on() function call to see the VDB call Virtuoso is making that are written to the virtuoso.log file. And also enable iODBC tracing to see the ODBC calls being made to the ODBC Driver.
Given the apparent intermittent issues being experienced with the MS DBC Driver 18 for SQL Server driver, as said previously, I would recommend trying the OpenLink SQL Server ODBC Driver, which has been tried and tested with Virtuoso from day one and would provide a better and more stable experience.
I tried version 17 of the MS driver, and so far I’m not seeing any unexpected behavior, so for now I think I’ll be using that. Again, many thanks for your help! Let me know if you’d like me to send you any data in case you guys want to investigate this on your end.
Hmmm, I tested with the MS DBC Driver 17 for SQL Server driver (/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1) and I am seeing similar behaviour as seen with the MS DBC Driver 18 for SQL Server driver with smallint datatype values:
SELECT top 5 * FROM "Northwind"."sql17na"."Products";
ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued
INTEGER NOT NULL VARCHAR NOT NULL INTEGER INTEGER VARCHAR DECIMAL SMALLINT SMALLINT SMALLINT VARCHAR NOT NULL
_______________________________________________________________________________
1 Chai 60129542145 60129542145 10 boxes x 20 bags 18 13563781598674983 13563781598674944 13563781598674954 13563781598674944
2 Chang 140131897966593 140131897966593 24 - 12 oz bottles 19 13563781598674961 13563781598674984 13563781598674969 13563781598674944
3 Aniseed Syrup 13563781598674945 13563781598674946 12 - 550 ml bottles 10 13563781598674957 13563781598675014 13563781598674969 13563781598674944
4 Chef Anton's Cajun Seasoning 13563781598674946 13563781598674946 48 - 6 oz jars 22 13563781598674997 13563781598674944 13563781598674944 13563781598674944
5 Chef Anton's Gumbo Mix 13563781598674946 13563781598674946 36 boxes 21.35 13563803073511424 13563803073511424 13563803073511424 13563803073511425
5 Rows. -- 797 msec.
SQL>
Thus I would suggest checking the results you are getting carefully …
Darn, spoke too soon: seeing similar behavior to what you’ve shown above with the select top 5 * query. 
So, for the Openlink ODBC drivers for MS SQL Server, is it possible to install them with apt-get, or do I need to download the tar ball + the install script? I’d like to put the installation into my docker build, and apt-get would be more convenient.
Thanks!
Our ODBC Drivers are not available via apt-get on Ubuntu (or other OS’es), they are only available as standalone generic Linux installers, as in the SQL Server ODBC Driver link provided previously.
We have this HowTo Persistently Install and Configure the OpenLink Single-Tier ODBC drivers on Virtuoso Docker Container post, with our SQL Server ODBC driver used as the example in this case.
Thanks, this worked! I was able to simplify the procedure a bit; after installing the driver I just edited odbc.ini to put in the new data source, referring to the driver by its library location.
Good to hear that is working for you now …