Hi guys
I'm working on a desktop app that needs to access a local database, and also the same database on networked and remote machines. As the database user credentials are held in tables within the database, I dont want networked/remote users to be able to access the database with 3rd party apps using windows authentication and read all the data, namely,my app controls their access to the database, and tables/rows within this, based on certain coded criteria. (Reason I didnt just use the SQL Server users/roles is due to the way the database needs to be structured and the permissions system needed, and not wanting to implement too many views).
So, I'm thinking if the databaser server is closed to remote access, and the app communicates to the database (wherever it is) through a managed windows service (local to the database) ,which IS accessible remotely through TCP, then this would allow for my app to completely control user access, and protect the data, seeing as the database is only accessible through sa login, or from 'windows authentication' from my client app.
I'm thinking a WCF service embedded in a managed windows service which accepts queries and returns datatables to the app's local dataset.
I guess what I'm asking is for people's advice on this process, and if this sounds like the best option.
Thanks