Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

  1. 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.

  2. 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."

  3. 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).
share|improve this question
    
invoke-sql isn’t part of PowerShell, it’s an extension provided by PowerGadgets. It might be worth front-loading the information that you’re using PowerGadgets in your question to increase the chances of the right people noticing it. Also, since PowerGadgets is a commercial product, perhaps they offer some sort of support? –  Daniel Cassidy Jun 21 '11 at 22:16
add comment

1 Answer

I hardly know where to begin with this, so I’ll just dive right in the middle.

“SQL Server” means “Microsoft SQL Server”, which is why that isn’t working. For some reason Microsoft like to name their products after generic terms.

invoke-sql is part of PowerGadgets, and is documented here: http://support.softwarefx.com/SupportDocTree.aspx?Prod=PG10&Type=A

Judging by the sparse documentation, and by the error messages you’re getting, invoke-sql expects to connect to a database via ADO.NET. From the documentation I don’t see any way to tell it what ADO.NET driver to use, but it sounds like maybe you can set that up in some sort of GUI — bit weird, but OK.

ADO.NET provides a compatibility layer to connect to databases that only provide OLE DB or ODBC drivers. If you choose “OLE DB” or “ODBC” then it sounds like PowerGadgets is talking through that compatibility layer (again, judging from the error messages).

The reason OLE DB isn’t working for you is that the connection string you’re providing is telling OLE DB to connect through yet another compatibility layer in order to talk to a database via its ODBC drivers. It sounds like ADO.NET specifically disallows this, presumably because it would cause all kinds of pain to talk to a database through two compatibility layers and an ODBC driver.

MySQL doesn’t provide an OLE DB driver, but you might be able to get it working by installing a third party MySQL OLE DB driver (unfortunately a paid product, but you can try it for free for a few days).

ODBC should work but it sounds like something is trying to validate your connection string as if it were an OLE DB connection string. OLE DB connection strings include a Provider parameter, but I’ve never seen that parameter in an ODBC connection string. I’m a bit stumped by this one but I suspect a bug/misfeature in PowerGadgets.

MySQL provides an ADO.NET driver called Connector/NET. If you install that, then if you’re lucky maybe PowerGadgets will offer you the option of using that driver instead. I kind of doubt that it will, but if it does then this is probably your best option. A MySQL Connector/NET connection string looks like:

server=localhost;user=dan;database=stuff;port=3306;password=bla;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.