3

I know that there are a lot of questions regarding this topic, however after four days of reading and googling, I am kind of stuck and need somebody to clarify some things for me please.

My situation:

I have developed a WPF application in C#. This application needs to be connected to a database, as it stores useraccount data and others. Up to now, I have worked with a local database in the Visual Studio Environment by setting up a datasource and using Fluent NHibernate for mapping and session. Now I would like to distribute the application to users via ClickOnce, so I need to switch from local to online database.

I have a Webserver (Debian 8 Jessie 64Bit, Kernel Version 3.16.0-4-amd64 (SMP)), SSH access and access to the i-MSCP 1.3.16 platform for administrating the server. I created a database in one of the server's domains and a sql-user and I can access phpmyadmin. I have followed to steps to grant remote access for any IP, as described for example here by SANDHYALAL KUMAR. First question: How can I check whether remote access is now successfully granted. Because when executing mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION; I get 0 rows affected. by the Putty console?

Second and more important question: How can I connect my WPF now with my server. I have read, that I should not directly connect to the server, because of password security and database security, like this:

            ISessionFactory sessionFactory = Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2012
              .ConnectionString(c => c
                  .Server("SSS")
                  .Database("DDD")
                  .Username("UUU")
                  .Password("PPP")
                  ).ShowSql()
            )
            .Mappings(m =>
                      m.FluentMappings
                          .AddFromAssemblyOf<User>())
            .ExposeConfiguration(cfg => new SchemaExport(cfg)
             .Create(false, false))
            .BuildSessionFactory();
        return sessionFactory.OpenSession();

However, I do not think that above solution would work for me, as I do not have an online MSSQL Server, right? Or do I need to configure my existing server?

Is there a specific layer or API which solves my problem. Is either WCF or REST or ADO.Net a solution for me? I'm really stuck with all these different names, so I would be truly thankful for some explanations and advice regarding my two questions.

Community
  • 1
  • 1
Niklas S.
  • 329
  • 4
  • 16
  • Just to be clear... you want to distribute a WPF desktop application... which connects to an online database... and at the same time protecting the connection string? – hyankov Feb 07 '17 at 13:04
  • Yes, is this uncommon ? I mean every application is somehow connected to a database, aren't they ? Regarding security I thought a layer between the WPF and database would solve the issue. – Niklas S. Feb 07 '17 at 13:08
  • 1
    Indeed, if that's your scenario, you need to expose an API (easiest is to use WebApi project) to which your clients will connect. The API will securely communicate with a database. So: client --> API --> DB – hyankov Feb 07 '17 at 14:18
  • Thanks for clarifying, can you recommend a specific webAPI or framework to set up such an api ? – Niklas S. Feb 07 '17 at 14:31

1 Answers1

1

Your case is almost same with my case. My app is also WPF (clickonce) and connects to Microsoft Azure Cloud SQL Server. I also had struggled for several days because as you know, security is always of most importance.

Putting password in desktop app and distribution means providing password to hackers. Like you commented, Set a layer with WCF and make WCF to hold the asymmetrically (like RSA) encoded password. Then, the Server will only authentify users with asymmetric key for decryption. This private key exists only in the Server.

Answer to first question) You can write a SELECT query to confirm which Logins, Users have these roles, permissions to enable access or not. I'm sure helpful examples are manywhere on internet.

Hope this helps your understanding..

Kay Lee
  • 922
  • 1
  • 12
  • 40
  • Do you know whether WCF works fine with my described server? I do not have a SQL Server, I am running a mysql database on a debian server. And I heard that WCF is difficult to set up. I don't have experience in web-services. So, is WCF suitable for "just" setting up the connection? – Niklas S. Feb 07 '17 at 16:22
  • 1
    It's now morning in East-Asia here. I've found the Debian server + MySQL cases are much common also in our country. Your Server will have IP address (or domain) and your WPF app will connect through communication broker like WCF. I don't know about In-premise Server because I'm using Cloud Server but you can find many WCF+ MySQL articles, questions on internet. As the name WCF (Window Communication Foundation), it's optimized for communication and not difficult to be set up when compared to the level of your knowledge for sure. – Kay Lee Feb 07 '17 at 23:38
  • Making WCF is easy and can be small but the more SQL commands you want to communicate with Server, the more Contracts, Interfaces codes are needed in the WCF. The all terms like Contracts, Intercfaces might not be familiar with you but once learned, they'll be nothing. However, to me, the most difficult hell was understanding the Security. It took couple of months to be finished. In the begining, I even was not sure this kind of architecture is available or not. WCF itself is really not difficult. Security is a door you must pass as you know and it might take time. – Kay Lee Feb 07 '17 at 23:46
  • Thank you so much for the details. I will step into WCF then, it's just good to know that's the right way before working days or weeks in the wrong direction and then having any compatibility issues or something like that. BTW my server is a cloud server as well. Security is always a hurdle but important. – Niklas S. Feb 07 '17 at 23:52