I'm trying to create a linked server to an instance of MySQL. I've created a System DSN to the instance and that passes the connection test, but when I try to create a linked server using that data source, I get an error (specifically Cannot initialize data source object of OLE DB provider MSDASQL for linked server "MyServerName") The system error is 182, MS Sql Server error 7303. Screen shot below:
Below is a screen shot of the general settings (TEST_MYSQL is the name of the data source I've previously and successfully configured and tested):
I'm using the "use this security context" with a username and password that work in the data source name. Below is a screen shot of the server options.
I'm not sure what I'm missing or doing wrong, but this isn't working. I would assume that something is configured wrong, but the data source connects successfully using the same creds. Is there something missing that would make the data source connect fine but prevent the linked server using that data source from doing so? Please advise, thanks!