Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MySQL as linked server


MySQL as linked server

Author
Message
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 20
Hey, here's some progress, following that MS article I found, it seems that the SPID problem occurs only if the driver is setup to run in process.

Changing the setting @ Server Objects > Linked Servers > Providers > MSDASQL > "Allow inprocess" uncheck this checkbox. Now the SPID issue goes away and I get an actual error message returned! Hurray!

And now for the embarassing part Blush looks like I've been keying the database name in the provider string wrong. How silly that was to waste this much of my time!!!!

Thanks all for following this with me
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 20
Ugh, so I'm not out of the woods yet actually... Still facing pretty much the same problem.

When Allow In Process disabled, Testing the connection works now and returns no error.

But if I try to query anything like
SELECT * FROM OPENQUERY(MYLINKEDSERVER, 'SELECT * FROM SomeTable')
Then I receive this error even though permissions on the security account are all setup correctly
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR".


I even went ahead and granted dba for % host on the mysql account. still complaining access denied.

This thread
http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/446e129b-dc86-4f7f-b217-ffc08d874b31
Seems to suggest that I have to have Allow In Process enabled. Is this true??? I tried re-enabling and then I got the same hang errors I was seeing previously.


EDIT: I noticed that everytime I execute the OPENQUERY from the MSSQL server, a connection is getting spawned on the MySQL server. Still, obviously I'm missing something. Not sure why allow in process would have to be enabled...
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 20
Geez, this just is NOT my week. Found another bloody workaround for this issue in this other workaround, but this is getting ridiculous.

So apparently, MSDAINITIALIZE and some other components need some security tweaking in order to run the OLE DB ODBC MySQL driver out of process. I found this great article outlining all the steps in detail.
http://social.technet.microsoft.com/forums/en-US/winservergen/thread/4a94acde-d7dc-496a-9ecf-c1eb04ed83ce/

But low and behold, another hurdle I ran into while walking through this. Just ain't my week. It seems that out-of-the-box, Windows 2008 r2 locks down all the security configuration of the components needed to be modified, which isn't mentioned in the article above resulting in a brick wall. There's a workaround which involves taking Administrator ownership of the reg key back to the Administrators group. This thread outlines the same process while solving a different problem:
http://social.technet.microsoft.com/forums/en-US/winservergen/thread/4a94acde-d7dc-496a-9ecf-c1eb04ed83ce/

I still have to finish the security tweaks, but I wanted to document the 2008 r2 issue before I finish. Once I'm done I'll post the outcome of my efforts.
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 20
Crying Went through the instructions for granting the sql service access to the necessary components, and restarted the sql server. Still not resolved. On a positive note, the error message has now changed:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "SHORETEL_CDR".


Wow, I can't believe how many bloody issues I'm running into to get this linked server to work... Crazy
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 20
Tried this
SELECT * FROM OPENQUERY(SHORETEL_CDR, 'SELECT 1')

and get the same error even still.

Found this
http://connect.microsoft.com/SQLServer/feedback/details/615000/sql-server-2008-r2-linked-server-to-db2-using-microsoft-oledb-provider-for-odbc-fail

and this
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/f9ddbb1e-b276-47e7-a85a-475c1e8c6793/

Both of which suggest to turn off prefetch. Tried that with no luck. I will say however, that based on their instructions it sounds like the ODBC driver they're looking at is different than mine because Mine doesn't have just an "Enable prefetch" checkbox on the "security" tab , but instead has a "prefetch from server by [textbox]" option on the "cursors/results" tab. Either way, I tried turning this feature off, recreating my linked server, and it made no difference.
Scott D. Jacobson
Scott D. Jacobson
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 1000
Would it be totally out of the question to just open a ticket with the MS SQL CSS folks at this point?
ballenger97
ballenger97
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 20
In my experiences, they're just going to tell me that the bug exists in the MySQL ODBC driver shoving the ownership back on Oracle. I guess I could try, but I don't expect much of a response from them on this.

In fact, there's already tickets on this subject sitting out there that I've referenced on this thread which pretty much shows that as the result.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search