I am working on a C # application that requests an instance of SQL Server 2008 Express on a remote server (LAN).
When I am on my computer with the / dev workstation, I can open SQL Server Management Studio and connect to the SQL instance using Windows authentication, and for all purposes the experience is identical to working in the SSMS "on" server. As far as I can tell, I can do anything from my workstation, which I could do on the server.
The same server contains the Access 2003 .mdb file, which is configured as the linked server in the SQL instance.
The requested queries return a set of results compiled by joining tables from SQL db and Access db.
When I execute the server "on" query, which should say that I am running SQL Server Management Studio on the server machine, everything works as expected. However, when I try to run the same requests from my development machine, whether in SSMS or when I try to run integration tests or debug the application, I can do nothing with the connected server. I see this in SSMS, but any attempt to execute something results in:
Unable to initialize OLE DB provider data source object "Microsoft.Jet.OLEDB.4.0" for linked server " <DatabaseName >".
I looked at a lot of threads associated with this error, and tried many permutations of the provider settings, all to no avail.
- Access database does not have a password (I tried to set security to use the name "Admin" without a password)
- the workstation user account has full permissions in the server directory where the access database is located
- The same behavior was associated with a server as Win XP Pro SP3 and now with Win Server 2003
- The same behavior was associated with the client as Win XP Pro SP3, and now with Win 7 Pro x32
Works great on a server, never remotely.
Help evaluate.
source share