Let user read and write to sql database without password using vb.net desktop app

Hi I want to create a desktop app that can read and write to our sql server,  I what to have a shared table that please can connect and only read and write to their own rows etc. But I don't want to use a password anywhere just in case it gets decompiled, If I encrypt the password that can be decrypted.

Which way is best to go?
Maybe like the eBay API and give a token. But have now idea how to do this...
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Is this application deployed onto the intenral network or internet?

Internal network: If your users log onto a domain, you can use impersonation and windows authentication. In this case, your connection string will not have a username and password. It will have a Trusted_Connection=True; OR Integrated Security=SSPI; settings.
Persist Security Info=False;Integrated Security=true;Initial Catalog=Northwind;server=(local)

Open in new window

Internet: In this case, Windows Authentication will not work. And it is not a good idea to expose the SQL Server directly over the internet. In this case, it is best to use an API. From a .NET desktop app, a WCF service might work best (one, two, three). Alternatively, you can use ASP.NET Web API (http://www.asp.net/web-api/overview/getting-started-with-aspnet-web-api/tutorial-your-first-web-api - good idea if you also want to support platforms such as mobile, web apps, etc).

For security and tokens, you need to look into OAuth2 (web API: one, two WCF: google

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
taz8020Author Commented:
Hi yes it is to be used over the internet, I have created a desktop app where customers can see our stock levels, send orders to us.
All works great but its using our remote sql server. Had a look at what you send and is very new to me. What would say is the best way for us to go. I have used other peoples apis and a lot seem to use a wsdl file.

Ideally want it using a token they can see only their data, have you seen a simple example in vb.net
In .NET, typically, WSDL was used with the older ASMX Web Services, for which the preferred model is to now use WCF (though I must say, ASMX Web Services had a simpler programming model, especially for beginning web services). Did you look at the hyperlinks I gave (for examples)

The token is OAuth 1.0 or OAuth 2.0 functionality. Even without using tokens, you could still achieve complete isolation of one customers data from another. This would depend on your database structure, where for example, you might have a TenantID in each table to identify the company to which a user belongs AND you would always filter the data for a user by this TenantID in all your SQL.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

taz8020Author Commented:
Great, gone with the old web service as looks a bit easier to start with but just one last question

Why can I not return a typed datatable or dataset
eg in the two below, the untyped version works on the web service but the other comes back blank, if I try in same form both work?

    <WebMethod> _
    Public Function GetOrdersUnTyped() As DataSet
        Dim DS As New OrdersDataSet
        Dim TA As New OrdersDataSetTableAdapters.AmazonOrdersTableAdapter
        Return (DS)
    End Function

    <WebMethod()> _
    Public Function GetOrders() As OrdersDataSet.OrdersDataTable
        Dim DS As New OrdersDataSet
        Dim TA As New OrdersDataSetTableAdapters.AmazonOrdersTableAdapter
        Return DS.Orders
    End Function

Open in new window

Plus would like to put data in a typed dataset send it back to save it but this does not work either
I haven't used typed datasets much. I tend to load my data into objects and then transmit the list of objects as opposed to the actual dataset.
taz8020Author Commented:
Perfect and just what I was after, and gave good examples
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.