title: Connect to SQL Server on a productive Client
body: <p>I have looked for multiple Solutions for my Problem.</p>
  
  <p>I have a program, that needs to insert/update/select data from a Database nearly all the time. And the program itself works, but the problem is, it should also work in a production system with our staff and this needs to be secure.</p>
  
  <p>One of the main thing is: 
  How do I manage, that the program can connect to the DataBase from our Staff PC's, without mentioning in the Connection String the Username and Password.
  Some People said, I should use Windows Authentication.</p>
  
  <p>But where do I mention the user/password that will be used to connect to the database? I need it to work in a way, people cant see the Login-Data to manipulate the DB-Server, but the programm itself should still be able to connect to it.</p>
  
  <p>How can I do it?</p>
  
  <p>Here are 2 connection strings my Visual Studio generated for me:</p>
  
  <pre><code>"Data Source=domain,Port;Initial Catalog=Zeiterfassung;Persist Security Info=True;User ID=sa;Password=PW"
  
  "Data Source=domain;Initial Catalog=Zeiterfassung;Integrated Security=True"
  </code></pre>
  
  <p>First one works fine, but the password is visible there. That shouldn't be. Even if I store it somewhere in the program, a decompiler will still make it visible.</p>
  
  <p>Will the second string really work on ANY PC out there, that has internet connection and the program? So no one can see the Information need to Manipulate the program outside of the program or the Admin itself? Right now I can test on authorized PC's, that have a user which is manually added as authorized on the DB-Server, but what is with other users? The should only be able to connect to the DB with the Program, not manually with their Windows Account in MSSMS. Last thing should really be forbidden.</p>
  
  <p>A Simple Code Snippet to clarify how the Connection looks for Example:</p>
  
  <pre><code>class Utility {
  
      private static SqlConnection con;
      static Utility() {
          con = new SqlConnection();
          con.ConnectionString = Properties.Settings.Default.DBPath1;
      }
  
      public static DataTableReader getSelectDataTableReader(string selectCommand, SqlParameter[] parameter) {
          SqlDataAdapter adapter = new SqlDataAdapter(selectCommand, con);
          if (parameter.Length &gt; 0) {
              adapter.SelectCommand.Parameters.AddRange(parameter);
          }
          DataSet ds = new DataSet();
          try {
              adapter.Fill(ds, "Table");
          } catch (Exception ex) {
              writeToLog(ex.Message);
              return null;
          }
  
          return ds.Tables["Table"].CreateDataReader();
      }
  </code></pre>