CODEWITHSUNDEEP
c#.netvisual-studio-2010sql-server-2008linq-to-sql
LCJ
LCJ

Reputation: 22652

Opening SQL Server database from LINQ and SSMS Simultaneously

I have a database in my local machine. I have a C# application that uses LINQ to SQL to insert records into this database. I opened the database (and selected records in a query window) using SSMS ( SQL Server Management Studio). Now, if I try to run the C# application, I am getting the following error :

“Cannot open user default database. Login failed. Login failed for user DFGV\G16570'.”

This error goes away when I restart the system.

Note: I am using Integrated Security=True

Note: The SQL Server is running with "LocalSystem" account

Connection String in LINQ:

 Data Source=.; AttachDbFilename=C:\DevTEST\Databases\LibraryReservationSystem.mdf;
 Integrated Security=True; Connect Timeout=30;User Instance=True

Note: "User Instance" keyword

Following is my LINQ query:

public virtual void InsertOnSubmit(T entity)
    {
        GetTable().InsertOnSubmit(entity);
        Context.SubmitChanges();
    }

What should I do in order to make the LINQ to work when I am connected to the database through SSMS?

EDIT

Based on response, I changed the code as shown below. 

   string connectionstring = "Data Source=.;Initial Catalog=LibraryReservationSystem;Integrated Security=True;Connect Timeout=30";

        using (var context = new RepositoryLayer.LibraryManagementClassesDataContext(connectionstring)) 
        {

            RepositoryLayer.Repository<RepositoryLayer.Account> selectedRepository = new RepositoryLayer.Repository<RepositoryLayer.Account>();
            selectedRepository.Context = context;
            AccountBusiness accountBl = new AccountBusiness(selectedRepository);

            //List<RepositoryLayer.Account> accountList = accountBl.GetAllAccounts();
            accountBl.InsertAccounts();
        }

Note: Made sure that "User Instance" keyword is NOT set as TRUE

Upvotes: 0

Views: 526

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131209

You are opening the database using the AttachDbFilename option which means the database is opened as a user instance, accessible only by the process identity that opened it.

If you want to access the database from multiple processes, attach it to a proper SQL Server instance and then connect to it passing the name of the database in the InitialCatalog property. Don't use AttachDbFileName and UserInstance.

This is described in Using SQL Server Express with ASP.NET

Upvotes: 1

Related Questions