Windows Service vs UNC file access

Get Started
1

Hello!

Ok, I got a toughie. I’m normally a web guy, so services for Windows is not my thing, but I’m on the hook for this.

I have a client that runs some old software for managing their production lines. The software writes to an Access database. They have a SQL server to host the data, but it’s a 64-bit installation, so it can’t query the MDB directly. The client had one of the IT guys setup another Access database with some timer code in it to query the Access database and push it to the SQL Server. This works fine, but I’ve been tasked with converting this to a service instead.

That’s all well and good. I have the queries being called. So, I wrote a service in C#. However, the service can’t seem to access the UNC path to the Access database. I had the service running as local service. I’ve tried Network Service, as well, but no luck. I found a solution involving giving permission to a user account in AD, but the box where the file lives is not on a domain.

How can I configure the environment to allow my service to open and query the Access database?

Thanks!

2

We struggled with this for a while. Apparently, 64 bit SQL doesn’t have a client that can connect to an Access database. We tried setting up a 32 bit ODBC connection, but SQL Server couldn’t find it when we tried to create a linked server.

I was able to connect to the Access database with my service, though. I just had to create a local account on my laptop that matched a local account on the target machine and then have the service use that account to connect to the Access database over the UNC path.

Now, I should be able to pull data from that database and update SQL server. The queries are pretty janky, but I think I can clean it up.

3

I guess I just don’t understand why you are using Access to begin with.

4

I’d probably hit this from a “write app that sits on OLD system and pushes Access DB data to client” if I had the shot.

Anyhow, insofar as the windows security angle, what you should be able to do is:

  1. create an account with the same credentials (user/pass combo) on each machine
  2. run service in that context
  3. give appropriate permissions to that account on OLD system
  4. ??
  5. [censored] a duck
  6. PROFIT!!!

NTLM should handle the authentication transparently, at least for one hop scenarios. Matching credentials are a poor or lazy man’s domain.

5

That tells me you didn’t actually read my first post. It’s not my choice. My client has OLD hardware on a production line that uses OLD software. The OLD software writes to an Access database, but they want to move the data periodically to a real SQL Server. They can’t get rid of the Access database without completely changing their automation software, which they can’t afford to do right now. It sucks. I hate working on it, but this is what I have to deal with.

6

Could you not send queries directly to the SQL server? 64-bit or not would not matter. I was under the impression that an SQL server accepts network connections…

7

wwb_99,

Yep! That’s pretty much what I did except that they wanted the service on the SQL Server. I created a matching account on my laptop and was able to execute a simple query from the service I created against that old database and log the result to the event log (just for testing).

I used a timer to have it re-run my method every 10 minutes, per the requirements. The only thing left that bugs me is that the original script deletes all the data from the SQL Server side and then inserts all the records from the Access database over and over. I’m going to see if they’ll let me code it so it just pulls new records and not dump everything, but they didn’t give me a lot of hours for this.