Recently, I spend quite some time to trouble shooting the linked server. I like to keep note about this.
alter any linked server
“alter any linked server” is the permission for modify the link server setting. The scenario is even when I grant the permission to the user, user still get the permission denied when try to modify the link server from SSMS.
The error message is somehow strange : ad hoc update system catalog are not allow.
However, if we do use sp_serveroption to change the linked server property, it works great.
Link Server error 7399 and 7330
When user execute the query against the linked server, they receive the below error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ASEOLEDB" for linked server "XXX" reported an error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ASEOLEDB" for linked server "XXXX"
This is actually a difficult one to track down. I am end up with using the procmon to monitor the sql server process while executing the query. It turns out the sybase client is looking for charsets\unicode\cp1252.uct. This file is part of Sybase open client. For some reason the SQL server is looking at C:\windows\System32 instead of Sybase client directory. I have copy the file and query works fine.