SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict darkwater23's Avatar
    Join Date
    Nov 2005
    Location
    Omaha, NE
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Windows Service vs UNC file access

    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. #2
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    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....
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  3. #3
    SitePoint Addict darkwater23's Avatar
    Join Date
    Nov 2005
    Location
    Omaha, NE
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    I guess I just don't understand why you are using Access to begin with.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  5. #5
    SitePoint Addict darkwater23's Avatar
    Join Date
    Nov 2005
    Location
    Omaha, NE
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Addict darkwater23's Avatar
    Join Date
    Nov 2005
    Location
    Omaha, NE
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •