-=B3N=-

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



Re: Visual C# General Windows Service as middleman between client desktop app and Database

Muhammad Adel

I guess it is easier to encrypt sensitive data in the database using a key that is only know by your application.
Sql server 2005 has built in encryption features where you can encrypt data using a keyword or a certificate.





Re: Visual C# General Windows Service as middleman between client desktop app and Database

frederikm

Hi

what you propose is an option however, i have an issue with the

I'm thinking a WCF service embedded in a managed windows service which accepts queries and returns datatables to the app's local dataset

does this mean you are sending sql over the network to your tcp service

If so, remember that any user could send something like "drop database" etc

Also, how is the security on your wcf service implemented

Can you get away with just setting the sql authentication credentials (maybe encrypted) in the app.config file

this would allow you to work with sql server without needing to pass through a service.

Third party programs would at least need those credentials to connect to the server.

Alternatively you can put the credentials in the registry.

Hope this helps you out.






Re: Visual C# General Windows Service as middleman between client desktop app and Database

bmcneill0

Hi

Thanks for your response. The sort of queries send to the service would be determined by the application, not the user, and I'd seek to protect from injection attacks through code. WCF service will need to receive a key in order to accept commands, and the key will be given by the application.

As for setting authentication credentials, as I need each user to be only able to access 'certain rows' in 'certain tables' depending on what their 'team leader' has set in the app, creating one set of credentials would still allow that user to use say, 'Management Studio Express' or similar to login with those credentials and access the same data as everyone else on those credentials. What I seek to do with code in the app is restrict those credentials further depending on the in-app permissions set by this 'team leader', which effectively limits their access (eg, only the budgets for a particular project). And do to this, I believe I'd need to block direct access to the database for this user credential and let the service access it so the app can control what is sent between the service.

The only other ways I could think of which would be creating different authentications for different users (too many), or having different sets of views (also too many). Hope this makes sense





Re: Visual C# General Windows Service as middleman between client desktop app and Database

frederikm

hi

yes, that does make sense

out of 1000 rows your user only has access to ten.

Programmatically restricting the access would be the way to go here

However, I'm not sure you really need the service.

Encrypting the credentials would give you the same behaviour, with less code involved

but your solution would work so i guess it's a matter of taste Smile

Hope this helps you out






Re: Visual C# General Windows Service as middleman between client desktop app and Database

bmcneill0

Hmmm...are you saying encrypt the credentials for 'Windows Authentication' I thought WA meant anybody could login (cos I need to allow for remote access) if you knew the server name/port Are you able to encrypt access on the server-side (in SQL Server) and provide the key in code for the app to give I appreciate your insight, thanks.



Re: Visual C# General Windows Service as middleman between client desktop app and Database

frederikm

hi

you could both use windows and sql login if you want.

(using windows authentication, you can limit who can access the database ,ie only your choosen useraccount)

either by spoofing the credentials of the windows user or by using a connection string.

say you want to use windows authentication & the app.config

you add two values in the app.config

dbuser = domain\username (of the account you choose to give access to the db)

dppass = encryptedpassword

using impersonation & decryption you can access the db.

your user can try and connect via sql management studio and the likes, but seeing as the password is encrypted, it won't work.

using sql authentication & the app.config

add a value with the encrypted connectionstring,

in you code decrypt the connectionstring and connect to sql server.

the issue with this is that you can't change the password easily,

which is not an issue when you use a service.

Hope this helps you out






Re: Visual C# General Windows Service as middleman between client desktop app and Database

bmcneill0

Thanks

I could't app.config the domain/username, as I want any domain/username to have access, as long as the app user has access (determined by the user table within the database).

I could just use sql authent, encrypt the string in the app,and have the service decrypt and login to sql server. shame sql cant deactivate windows authent in this case and just allow sql authent though.





Re: Visual C# General Windows Service as middleman between client desktop app and Database

frederikm

keep in mind that the credentials in the app.config are those used by the system to connect to the db.

so anyone logging in to the application has access, via the application.






Re: Visual C# General Windows Service as middleman between client desktop app and Database

bmcneill0

That would be okay, because the application would also control what data the service will retrieve for them (by checking in the database's 'user' table what project access they have if they try to obtain project data, right



Re: Visual C# General Windows Service as middleman between client desktop app and Database

frederikm

that's correct Smile

I hope this helped you out, please close the thread if it did Smile

Cheers






Re: Visual C# General Windows Service as middleman between client desktop app and Database

DivyaDamodaran

Hi ,

I'm for the first time trying out accessing Database using WCF service.Got some serialization problems could u o=plz help me out.





Re: Visual C# General Windows Service as middleman between client desktop app and Database

bmcneill0

What exactly are you after




Re: Visual C# General Windows Service as middleman between client desktop app and Database

DivyaDamodaran

Hi,

Me want to return a SqlConnection object from service.Is it enough to mark it with [DataMember] attribute in Datacontract for serializing it I tried the code in this way

[ServiceContract]

public interface IDBAccess

{

[OperationContract]

object GetConnection();

}

[DataContract]

public class Connect

{

[DataMember]

SqlConnection con = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Student;Data Source=C513USS");

[DataMember]

public object Con

{

get{return con;}

}

}

public class DBAccessService : IDBAccess

{

public object GetConnection()

{

Connect c = new Connect();

// object co = null;

return (object) (c.Con);

}

}

But execption is generated related to missing endpoints.But i could access any other method in contract(no probs in endpoints in those cases).i boxed it to object otherwise it is showing serialization error. Is this the right way to do it