Need help making an ODBC MySQL Connection
- by Andy Moore
Short Version:
How do I connect from PowerShell to an ODBC 5.1 MySQL Driver? I can't seem to find any connection strings that accurately have a "Provider" field for this particular instance. (See bottom of this question for examples/errors)
=====
Long Version:
I'm not a server guy, and I've been handed the task of setting up PowerGadgets on our network.
I have a MySQL server running on a Linux box, that is configured for remote access and has a user defined for remote access as well.
On my windows desktop PC, I have PowerGadgets installed. I installed the MySQL ODBC 5.1 connector, and went to Control Panel Data Sources and set up a User DSN connection to the database.
The connection, user, and pass seem to be correct because it lists the tables of the database in my windows control panel.
Where I'm running into trouble is in 3 places in PowerGadgets:
When selecting a data source, I can select "SQL Server". Inputting the servers IP address does not work and I can't get this option to work at all.
When selecting a data source, I can select "OleDB". This screen has a wizard on it, that appears to populate all the correct information (including database table names!) for me. "Test Connection" runs great. But if I try to complete the wizard, I get the error "The .NET Framework data provider for OLEDB does not support the MS Ole DB provider for ODBC Drivers."
When selecting a data source, I can select "ODBC". This screen does not have a wizard and I cannot figure out a "connection string" that works. Typically it will respond with the error "The field 'Provider' is missing". Googling ODBC connection strings doesn't reveal any examples with a "provider" field and have no idea what to put in here. The connection string (for #2) above contains "SQLOLEDB" as a provider, and upon inputting that value into this connection string I get the same connection error that #2 gets.
I believe I can solve my problems by figuring out a connection string for #3 but don't know where to get started.
(PowerGadgets also allows for PowerShell support but I believe I will run into the same problem there)
==
Here's my current PowerShell connection that doesn't work:
invoke-sql -connection "Driver={MySQL ODBC 5.1 Driver};Initial Catalog=hq_live;Data Source=HQDB" -sql "Select * FROM accounts"
Spits back the error:
"Invoke-Sql : An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.
==
Another string that doesn't work:
invoke-sql -connection "Provider=MSDASQL.1;Persist Security Info=False;Data Source=HQDB;Initial Catalog=hq_live" -sql "select * from accounts"
And the error:
The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc).
