title: How to open a connection to a SQL Server database that is already in use in c#?
body: <p>I connect to the database in asp.net mvc 3 project. Also I have a windows service project in my solution which on some event writes the data into this database.  </p>
  
  <p>The difficulty is that when the code from windows service project needs to connect to the database I get <code>SqlException</code>:   </p>
  
  <blockquote>
    <p><em>Cannot open database "WebStore" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.</em></p>
  </blockquote>
  
  <p>Here is the code from windows service:  </p>
  
  <pre><code>    public static string GetConnectionString()
      {
          return @"Data Source=(LocalDB)\v11.0; DataBase=WebStore; 
                  AttachDbFilename=myDbFullPath;
                  Integrated Security=True;Connect Timeout=30";
      }
  </code></pre>
  
  <p>And in the event handler that writes to the database:  </p>
  
  <pre><code>using (var conn = new SqlConnection(GetConnectionString()))
  {
      conn.Open();
      var productId = Convert.ToInt32(Regex.Match(e.Name, @"\d+").Value);
      const string cmd1 = "UPDATE Products SET ImageModifiedDate=@date WHERE ProductId=@productId";
  
      using (var command = new SqlCommand(cmd1, conn))
      {
          command.Parameters.AddWithValue("@productId", productId);
          command.Parameters.AddWithValue("@date", DateTime.Now);
          command.ExecuteNonQuery();
      }
   }
  </code></pre>
  
  <p>The exception occurs in <code>conn.Open();</code> line. If I comment this line new exception will occur in <code>command.ExecuteNonQuery();</code>:   </p>
  
  <blockquote>
    <p><em>InvalidOperationException: Opened and available connection is needed for ExecuteNonQuery. Connection is closed.</em></p>
  </blockquote>
  
  <p><strong>Question:</strong> How to open a connection to the SQL Server database that is already attached in another project? </p>
  
  <p><strong>Edit 1:</strong> I use the following code to create new login and new user in my DB:  </p>
  
  <pre><code>CREATE LOGIN user1
  WITH PASSWORD = 'password1';
  GO
  CREATE USER user1 FOR LOGIN user1;
  GO
  </code></pre>
  
  <p>Than I'm trying to attach my DB in Server Explorer: I choose "Use Sql Server Autherntication" and enter user name: user1, password: password1.  </p>
  
  <p>And I get:  </p>
  
  <blockquote>
    <p><em>The attempt to attach to the database failed with the following information:<br>
    Login failed for user 'user1'.</em></p>
  </blockquote>
  
  <p><strong>Edit 2:</strong> I set this properties:  </p>
  
  <pre><code>processInstaller.Username = "user1";
  processInstaller.Password = "password1";
  </code></pre>
  
  <p>and than run in cmd:  </p>
  
  <pre><code>installutil /name=user1 /password=password1 MyService.exe
  </code></pre>
  
  <p>and I get <em><code>System.ComponentModel.Win32.Exception:</code></em><br>
  <em><code>the comparison between user names and security identifiers haven't been executed.</code></em>  </p>
  
  <p>What do I do wrong?</p>
  
  <p><strong>Edit 3:</strong>  I set my username and password in services.msc and processInstaller "Account" property I left with "Local System". It runs but when I change the image (and the event handler trying to access my database) I get:  </p>
  
  <p><em><code>Cannot attach file 'C:\Users\Aleksey\repos\working-copy\WebStore\WebStore\App_Data\WebStore.mdf' as database 'WebStore' because this file is already in use for database 'C:\USERS\ALEKSEY\REPOS\WORKING-COPY\WEBSTORE\WEBSTORE\APP_DATA\WEBSTORE.MDF',</code></em> </p>
  
  <p>In the connection string I added credentials of user which I've created in the database:  </p>
  
  <p><em><code>User Id=user1;PASSWORD=password1;</code></em> </p>
  
  <p>Besides, when I'm trying to add connection in server explorer and use <code>Sql Server Authentication</code> and I enter <code>user1</code> and <code>password1</code> I get the message from <strong><em>Edit 1</em></strong>.  </p>
  
  <p>Should I use in server explorer <code>*Windows Authentication*</code> and in windows service in the connection string: <em><code>User Id=user1;PASSWORD=password1;</code></em> ?</p>