Linked Server Fails with SQL Login. Works as Server Admin

Question

Linked Server Fails with SQL Login. Works as Server Admin

homebrew01

SSC Guru

Points: 55548
More actions
November 6, 2013 at 3:30 pm

#284569

I have a linked server to MySQL on a Windows server 2003 32 bit server. I can run selects & updates against the MySQL database as a Windows admin account, but not as a local SQL login, which is what the application will be using. I get the error:
OLE DB provider "MSDASQL" for linked server "mysql_cms" returned message "[MySQL][ODBC 3.51 Driver]Access denied for user 'MySQL_Login'@'216.1.1.1' (using password: YES)".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "mysql_cms".
I am using an old ver 3.51 MySQL driver because the newer ones fail when trying to install.
Ideas ??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Linked servers use OLEDB providers, which are ultimetely DCOM objects. DCOM configuration is really tricky and out of the box everything works with Administrator only.

See if some of the steps here can help:

http://www.sqlservercentral.com/articles/Linked+Servers/73794/

-- Gianluca Sartori

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Before I make lots of changes on a production server, do you know which settings might apply to my situation ? That I can access the MySQL data via Linked Server if I use Windows Authentication, but not with a SQL login.

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Probably the SQL Server service account can't instantiate the OLEDB provider.

Follow the steps in the "DCOM configuration" section of the article.

However, I don't recommend that you troubleshoot in production (some changes actually need rebooting the machine!).

Find a test server and fiddle with permissions there instead.

Once you get it to work, you can repeat the same steps in production.

-- Gianluca Sartori

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

Another puzzle with this:

When I run an INSERT statement from my MSSQL query window against the live MySQL database, but a TEST table it works fine. But if I try to run the INSERT in the same database, but different table, I get the error:

[font="Courier New"]The OLE DB provider "MSDASQL" for linked server "MYSQL_CMS" reported an error. The provider did not give any information about the error.

Msg 7306, Level 16, State 2, Line 9

Cannot open the table "exwp_users" from OLE DB provider "MSDASQL" for linked server "MYSQL_CMS". Unknown provider error.[/font]

I can select from live MySQL DB, Live MySQL Table "exwp_users", but not insert to "exwp_users"

I had just created the test table, so don't see why it should be easier to access than the live table.

EDIT: Got a workaround by using a staging table.