1

I've got some desktop experience, but am (brand) new to web programming. I've built a well-received C# WPF desktop app that stores data in a local (on user's desktop) SqlLite DB. I'd like to transition the app to remote data storage, probably with a MS SQL Server DB, hosted by a web-host service provider. One database there would hold all the various users data, access controlled by their own username/password.

In fact I've already done that as an experiment, and it functions. My concern is security: at the moment my in-code connection string just uses my db account/password. I'm not such a newb to know that's not a good idea. There must be a standard way to move that private information out of the code and into a sort of relay between the app code and the db. But I don't know the terminology, or what to ask for, despite a day of googling. So:

(1) User requests data save, say

(2) App sends SQL statement and user credentials to relay.

(3) Relay checks credentials against db records (using my db credentials, but that's ok, they're at least not stored in the apps's source code)

(4) Assuming ok, forward sql statement to db.

Is (something like) this a thing? What's it called? Or is there some other standard way to achieve the goal of keep my connection string completely out of the code? Where do I begin reading about how to implement it? How would I know if my web-host would support such a thing?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
user3486991
  • 451
  • 6
  • 14

1 Answers1

0

From the point of view of web-app operations, your connection string, from your dotnet app to your RDBMS server, is considered a secret. That means it's data you retrieve from a configuration file, and is never checked in to your git or other source control system. That connection string contains your RDBMS username and password, along with stuff like the name of your database and the server where it runs.

(If you did check in an RDBMS connection string to source control for any machine other than localhost, change that password. Do it now. Cybercreeps troll github looking for connection strings to steal and use for nefarious purposes. )

Dotnet web apps have a configuration file. It's an XML file called web.config. Connection strings go into that file in an XML stanza looking like this

  <configuration>  
    <connectionStrings>
      <add name="Name"
       providerName="System.Data.ProviderName" 
       <!-- When deploying to production,
            replace this connection string with one
            to connect the production data base. -->
       connectionString="Valid Connection String;" />  
    </connectionStrings>  
  </configuration>

Here's some info about retrieving that kind of connection string from your dotnet program..

I've had good luck putting a globally useless locally useful localhost connection string in that file, with some xml comments explaining that it needs to be edited when putting the web app on a public server. My example shows such comments.

It's also possible to edit a connection string with the web server's IIS Manager app. This setup -- either web.config or IIS Manager -- has good security.

The scheme you outlined is more complex than you need unless, heaven forbid, every one of your customers has a different connection string.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Ok, but I"m still hazy: If I understand correctly, my desktop app should store the connection string in web.config (app.config?) which presumably is populated when the user installs the app, via some method. Where does the method look for the connection string? With what credentials? (*those* credentials can't be in the code...) Obviously still at sea here, thanks for the help... – user3486991 Aug 08 '22 at 00:39
  • Simple, if a little crude. Manually edit the web.config file or use IIS manager to set the connection string when you deploy. Get the connection string from your password manager or some other secure storage. You won't forget this step: the deployed app will not work without the correct connection string. Dotnet has [secrets management](https://learn.microsoft.com/en-us/aspnet/core/security/app-secrets?view=aspnetcore-6.0) but i'm the wrong guy to explain how to use those features in CI/CD. – O. Jones Aug 08 '22 at 11:13