Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

MySQL as linked server Expand / Collapse
Author
Message
Posted Wednesday, August 8, 2012 1:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
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 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
Post #1342192
Posted Wednesday, August 8, 2012 2:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
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...
Post #1342205
Posted Wednesday, August 8, 2012 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
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.
Post #1342256
Posted Wednesday, August 8, 2012 3:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
Points: 14, Visits: 20
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...
Post #1342268
Posted Wednesday, August 8, 2012 4:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
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.
Post #1342296
Posted Thursday, August 9, 2012 6:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 5:41 PM
Points: 356, Visits: 918
Would it be totally out of the question to just open a ticket with the MS SQL CSS folks at this point?
Post #1342606
Posted Thursday, August 9, 2012 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 30, 2012 10:16 AM
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.
Post #1342837
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse