Job fails when connecting to MySQL Linked Server from SQL Server 2008

• Tim Howard

  Valued Member

  Points: 60

  More actions

  August 5, 2009 at 8:40 am

  #207810

  This is strange! We recently upgraded from MSSQL 2000 to MSSQL 2008. We have a linked server that is actually a MySQL DB on a client's server. The connection string used for this linked server is:

  Driver={MySQL ODBC 5.1 driver};Server=1.2.3.4;Port=3306;Option=131072;Stmt=;Database=RemoteDB;Uid=myUserID;Pwd=myPassword (obviously I changed the Server, Uid, and Pwd values for security).

  We defined users and assigned the logal logins to use the UID and password that the client gave us. In Mgmt Studio, I can run a query (SELECT * FROM OPENQUERY(...)) and I have no problems. But we have a job scheduled to run a stored procedure every night, and ever since we upgraded to 2008 the job has failed. The owner of the job and the "executed as user" are both defined in the login list for the linked server. The error message we get is:

  Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "myLinkedServer". [SQLSTATE 42000] (Error 7303) OLE DB provider "MSDASQL" for linked server "myLinkedServer" returned message "[MySQL][ODBC 5.1 Driver]Access denied for user 'myUserID'@'localSqlServer' (using password: NO)". [SQLSTATE 01000] (Error 7412). The step failed.

  What bothers me is the "(using password: NO)" phrase. As far as I can tell, it SHOULD be using the UID and password in the connection string. Any ideas why I can directly run queries myself but SQL Server Agent job fails? Thanks!!!

• alphabytes

  Newbie

  Points: 3

  More actions

  October 21, 2009 at 12:15 am

  #1068676

  hello can you please help me on how to execute a

  mysql procedure from mssql server 2000 ?

  a simple example would do.

  sorry but i am a novice.

  Regards

  Nitesh