I have an issue connecting to Azure CosmosDB using the Microsoft Azure CosmosDB ODBC driver, when setting up a table connection in PI AF, and wanted to know if anyone has seen this error or scenario before.
I start with installing the 64 bit Microsoft Azure CosmosDB ODBC driver (Azure DocumentDB driver) and set up a 64 bit System DSN by providing the host URI and the API Key. This combo is all it takes to connect - other means of authentication are not available atm.
The System DSN is set up correctly, and when I test the connection it's a success!
- I can use this connection through Excel, or in SQL Server without issue.
In SQL Server I can either use it directly with the OPENROWSET command (specifying the MSDASQL provider) or by adding it as a linked server and querying through OPENQUERY.
- In Excel I get data from an ODBC source, select the newly created System DSN, provide a query and then - when faced with the authentication dialog, go to the option where you provide a connection string and leave it blank.
Then I try adding this connection as a table connection in AF. I build the connection string through the Microsoft Data Link dialog, select "Microsoft OLE DB Provider for ODBC Drivers", then select my SystemDSN in the "Use data source name" dropdown box, then select my document collection in the "Enter the initial catalog to use" by browsing the list of collections returned from Azure. When I click "Test Connection" it's a success.
I then select "No additional security context" for this AF table connection, and create a linked table using this connection! But I'm met with this error message when trying to execute a query:
Unable to link to external table: Unexpected Server Error: 'ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'.
Going back to the table connection, I see the connection string says: "DSN=CosmosDB;DBQ=db_collection" and immediately noticed that the driver information was missing. It should have looked like "Provider=MSDASQL;DSN=dsnName;UID=MyUserID;PWD=MyPassword;" but upon further reading it seems the MSDASQL is the default driver, and should have been used anyway. I even tried adding it to my connection string and checking in the changes, but the "Provider=MSDASQL;" part was removed automatically.
Anyone seen this before? Either with the Azure CosmosDB Driver or any other ODBC driver used in AF through MSDASQL?