Okay, so let me start out by saying that I've searched these forums and Google for the last few days and while I find many similar situations, I still haven't been able to find a solution to my problem. I have setup linked servers in SQL and they have always worked fine. The problem I'm facing now is that I'm trying to run them through an SQL Agent Job and I am getting hit with a long error that says "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MAS_Link". [SQLSTATE 42000] (Error 7303) OLE DB provider "MSDASQL" for linked server "MAS_Link" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". [SQLSTATE 01000] (Error 7412) OLE DB provider "MSDASQL" for linked server "MAS_Link" returned message "[ProvideX][ODBC Driver][PVKIO]Logon failed". [SQLSTATE 01000] (Error 7412). The step failed." The exact same query run in the SSMS window works fine, it's only when run as a job that it fails. I've tried both 32 and 64 bit versions of SQL (on different servers) and still no luck. This is on Sage 100 Advanced 2014 PU 7. What's interesting is we still have our old Mas 4.4 Server and I had linked servers set up to that as well and they work in the jobs. The only difference is the path to the mas installation. Does anyone know anything I can try to get this working? Any insight is appreciated. Thanks
That's interesting. When I login with a local SQL login, I can't even run a query using SSMS, I get that exact same message...what I find the most interesting is that I can still hit the 4.4 linked server's no problem. I don't know...I'm open to any suggestions any one may have...
EDIT: Scratch that, I recreated the linked server while logged in under the sql account and the job seems to run. That's great!! Thanks Kevin M!!! Now I have to recreate all the linked server's which is a pain because we have 58 of them, but hopefully after that this will work!!! Thanks again!