CODEWITHSUNDEEP
c#asp.netsqldatabase-connectionsql-server-2012
Aleksei Chepovoi
Aleksei Chepovoi

Reputation: 3955

How to open a connection to a SQL Server database that is already in use in c#?

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.

The difficulty is that when the code from windows service project needs to connect to the database I get SqlException:

Cannot open database "WebStore" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.

Here is the code from windows service: 

    public static string GetConnectionString()
    {
        return @"Data Source=(LocalDB)\v11.0; DataBase=WebStore; 
                AttachDbFilename=myDbFullPath;
                Integrated Security=True;Connect Timeout=30";
    }

And in the event handler that writes to the database: 

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();
    }
 }

The exception occurs in conn.Open(); line. If I comment this line new exception will occur in command.ExecuteNonQuery();:

InvalidOperationException: Opened and available connection is needed for ExecuteNonQuery. Connection is closed.

Question: How to open a connection to the SQL Server database that is already attached in another project?

Edit 1: I use the following code to create new login and new user in my DB: 

CREATE LOGIN user1
WITH PASSWORD = 'password1';
GO
CREATE USER user1 FOR LOGIN user1;
GO

Than I'm trying to attach my DB in Server Explorer: I choose "Use Sql Server Autherntication" and enter user name: user1, password: password1.

And I get:

The attempt to attach to the database failed with the following information:
Login failed for user 'user1'.

Edit 2: I set this properties: 

processInstaller.Username = "user1";
processInstaller.Password = "password1";

and than run in cmd: 

installutil /name=user1 /password=password1 MyService.exe

and I get System.ComponentModel.Win32.Exception:
the comparison between user names and security identifiers haven't been executed.

What do I do wrong?

Edit 3: 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:

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',

In the connection string I added credentials of user which I've created in the database:

User Id=user1;PASSWORD=password1;

Besides, when I'm trying to add connection in server explorer and use Sql Server Authentication and I enter user1 and password1 I get the message from Edit 1.

Should I use in server explorer *Windows Authentication* and in windows service in the connection string: User Id=user1;PASSWORD=password1; ?

Upvotes: 0

Views: 3564

Answers (1)

Carlos Grappa
Carlos Grappa

Reputation: 2381

Cannot open database "WebStore" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.

This happens because your service is running with the LocalSystem account, and you're using integrated security to connect to SQL server, and SYSTEM shouldn't have access there. So threechoices:

  1. Switch to SQL Server authentication. That includes creating a login in the database and modifying the connection string
  2. Run the service with a particular user with the only the privileges it really needs (this is actually a good idea) and give that user access to the database so it can connect with integrated security. This is the easiest because it doesn't require any changes to the code
  3. If you really need to run as SYSTEM, then can use impersonation to switch to another user (like the user from option 2) with LogonUser. That way you end up in option 2 just for the database access and retain SYSTEM priviliveges for the rest of the service code.

The second error

InvalidOperationException: Opened and available connection is needed for ExecuteNonQuery. Connection is closed.

Is caused by the first error, failure to open the connection brings this up. You can't execute anything without an open connection, ADO.Net makes sure of that.

So the first error is the only one that matters.

I recomend that you go with option two as is the best in terms of security practices, and since you are running in integrated security you don't have to store any password, and thats a plus when you have your application in several environments with different databases, users, passwords and what have you. Just a user per enviroment, permissions and you're set.

Upvotes: 1

Related Questions