I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
The drives are secured so that only local administrators have rights to them.
In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.
My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.
I figured it out. Here is what I found out:
Using the FileMon.exe tool from sysinternals.com I noticed that the file referred to in the error called "SYBINIT.ERR" could not be written since regular users did not have write access to the directory that it was trying to be written to. This was C:\WINNT\System32. So, I temporarily gave "Everyone" rights to modify the files within this directory. After doing so I ended up getting a "SYBINIT.ERR" file created which in turn told me that it "Cannot access file c:\sybase\ini\objectid.dat". I then gave Everyone read rights to the C:\sybase\ini folder. I then attempted again and got the following error: "Cannot access file c:\sybase\locales\locales.dat". Again, I gave read rights to this folder and tried again... this time getting the following errors: "Cannot access c:\sybase\charsets\iso_1\binary.srt file
Cannot access c:\sybase\charsets\iso_1\charset.loc file
Cannot access c:\sybase\charsets\iso_1\iso_1.cfg file"
Not wanting to keep this up, I then gave read rights to the C:\sybase folder and tried again. Everything worked. Then, I removed modify rights from "C:\WINNT\System32" and tried again. EVERYTHING WORKED!!! Yeah, this means that I just had to give read rights without opening up the SQL Server too much.