4

I've got a beginners question that's hopefully easy, but I can't seem to find a straightforward answer, and I can imagine there's dozens of ways to accomplish this.

I have a c# application that connects to a database using SQL authentication (so I can't use trusted AD connection) and I've got a hard-coded connection string with the password in the source code. Unfortunately, I want to commit this source code to git version control, but I do not want the password in the source code.

What's the standard way to resolve this issue? Should I create a config file that's required to be read at runtime? Is there anyway to have this compiled down into the binary so I can distribute it without end-users needing the database credentials?

Thanks

Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
  • You can encode connection strings in the App.config, I'm sure there's an MSDN article covering it as it's the recommended way. Let me see if I can find it... [This answer](http://stackoverflow.com/questions/11637348/encrypt-connection-string-in-app-config) has some useful info on it. – Equalsk Nov 02 '16 at 16:45
  • 1
    Wouldn't App.config be required to share with the repository? – Jeff Puckett Nov 02 '16 at 16:49
  • Yes, but the connection string has been replaced with an encrypted value. It's also automatically decrypted at runtime so you don't have worry about it. I believe it can technically be decoded by someone smart enough if they have the config file itself so this method involves a little risk vs reward. There are probably nuclear options involving trusted certificates and custom encryption etc, but then the question becomes too broad for SO IMHO. – Equalsk Nov 02 '16 at 16:59
  • Will the database be distributed with the application, or is the application a client that will connect back to your database? – Kevin Nov 02 '16 at 17:20
  • @Kevin it's a stand alone server running ms SQL server, and the distributed client applications will connect back to it. – Jeff Puckett Nov 02 '16 at 17:33
  • The client applications are thick clients (e.g. Windows desktop applications)? – Chris Shain Nov 02 '16 at 18:01
  • @ChrisShain yes – Jeff Puckett Nov 02 '16 at 18:03
  • Have the user input the password. – Dan Field Nov 02 '16 at 18:07
  • 2
    I'd have it connect via a service of some kind. That way the service is all that needs a direct connection to the database. That also give you a good spot to do your user authentication if required. – Kevin Nov 02 '16 at 18:09

5 Answers5

2

you should never store passwords or other sensitive data in source code, and you shouldn't use production secrets in development and test mode.

Best Approach will be storing sensitive info in environment variables.check this link

Other option save it into external file with extension .config as iis will ignore this file also exclude this file from source tree.check this link

Khalil
  • 1,047
  • 4
  • 17
  • 34
  • 2
    I really can't imagine an IT security policy that would allow or encourage someone storing a password in an environment variable.... – Dan Field Nov 03 '16 at 14:33
1

The right way to do this is to use Windows Authentication to authenticate your users to SQL Server. This relies on built-in Windows security, which your app gets "for free" by running under the context of the logged in user. Using Windows Authentication is as simple as enabling it on the SQL server and using Integrated Security=SSPI in your connection string instead of username and password.

That said, it is generally considered a better design to put a service layer between your client and the database and do the authentication there as Kevin suggested in the comments.

Chris Shain
  • 50,833
  • 6
  • 93
  • 125
  • As noted in my question, I am connecting to a SQL Server that does not support AD integration, so I cannot use it. I have no control over this, and was given SQL Authentication credentials. – Jeff Puckett Nov 02 '16 at 18:27
  • 1
    In that case the "right thing to do" is to use a service layer that is secured via Windows authentication or a manual user login (where the user enters credentials into the app itself). There is no way to reliably secure credentials in checked in code. – Chris Shain Nov 02 '16 at 18:28
0

Place the following into your app.config

<connectionStrings> <add name="DB-Conection" connectionString="CONNECTIONSTRINGHERE" providerName="System.Data.SqlClient" /> </connectionStrings>

Place it at the top of your .cs file where you do your database calls

private string connection_string = ConfigurationManager.ConnectionStrings["DB-Connection"].ConnectionString;

Call connection_string where you would have your hardcoded connection string. You could exclude your app.config when you do a git commit but when you compile the app, it will need to be included (obviously)

Captain Squirrel
  • 237
  • 5
  • 15
  • And now you have the credentials stored in the app.config... (that will also be committed) – Matteo Umili Nov 02 '16 at 16:52
  • 1
    I believe the App.config will need to be committed to source control for the rest of the application to function properly when others clone the repository... – Jeff Puckett Nov 02 '16 at 16:52
0

The standard way I've seen to hide the credentials is to use an Encrypted Section in ASP.NET configuration. See https://msdn.microsoft.com/en-us/library/zhhddkxy.aspx

Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
Todd Sprang
  • 2,899
  • 2
  • 23
  • 40
0

You could store an encrypted connection string (in the app/web.config or otherwise), but there's a trick to that: the application (and anyone with admin access to the computer) would be able to decrypt it, and anyone who pulls down your code would be able to get that decrypted password (either attaching a debugger, or modifying your source to print/save the full decrypted connection string, or decrypting the app.config themselves).

If you don't want the password stored, then don't store it. Have the application request that the user input the username and password. If you can't distribute that information, then you really need to rethink your security architecture:

  1. Create accounts for each user of the application that they can know and only has access to the data in the ways they should in SQL Server
  2. Use integrated security and set up that security properly in the database.
  3. Make use of an application role on SQL Server (https://msdn.microsoft.com/en-us/library/bb669062(v=vs.110).aspx) and distribute the password for that application role - but again, requires password distribution.
  4. Move your DAL logic to a gated service that your application connects to. The gated service knows the password, but it's not distributed. Presumably, this could take care of the situation where you can't modify the SQL permissions/roles/etc. but you can control access through your gated service.
Dan Field
  • 20,885
  • 5
  • 55
  • 71