1

I have the following problem, I have a service that runs at low level and connects to the mdf database of the sql server localdb. But this service is using a different account 'AUTHORITY NT \ SYSTEM' when that account connects to the database, it does not leave any other program with high level to connect to the database.

The opposite also happens, if you have any high-level program connected to the database, the service does not connect.

The connection string:

 way = $"Data Source = (LocalDB)\\v11.0; AttachDbFilename = { MDF}; Integrated Security = True; Connect Timeout = 30 ";

Is it possible for the two accounts to connect at the same time?

mba
  • 319
  • 2
  • 11
  • 1
    Yes. First DO NOT use the MDF filename in the connection string. The database is attached to the server and the server knows the location of the file. Then use Windows credentials (which is Integrated Security = true) for the database (not SQL). Setup a Window user Group Account which will be used by database. Add users to Group Account. When you remote connect make sure the Group account is on both Server PC and Client PC. – jdweng Jan 29 '19 at 12:38
  • 1
    @jdweng I do not understand very well, how come I do not use the `mdf` name in the connection string? In my case, the **MDF** name is a string that takes the path where the bank is. Do I have to create the user **'AUTHORITY NT \ SYSTEM'** in windows user chant? – mba Jan 29 '19 at 13:11
  • You probably want to run the service using a dedicated user account rather than the built-in one. Also, which account owns the database? _"An instance of LocalDB owned by the built-in accounts such as NT AUTHORITY\SYSTEM can have manageability issues due to windows file system redirection; Instead use a normal windows account as the owner."_ [docs page](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-2016-express-localdb?view=sql-server-2017) – stuartd Jan 29 '19 at 13:18
  • Also see [Using SQL LocalDB in a Windows Service](https://stackoverflow.com/questions/26597498/using-sql-localdb-in-a-windows-service) for lots more info (and as a possible duplicate) – stuartd Jan 29 '19 at 13:19
  • 1
    @stuartd But how can I pass the windows user account on the connection? – mba Jan 29 '19 at 13:48
  • 1
    You don't. You run the service as the user account, and use windows authentication to connect to the server. – stuartd Jan 29 '19 at 13:58
  • 1
    If it working already with Integrated Security = True you do not have to do anything except remove the AttachDbFilename from connection string. The mdf file is a database in SQL. So in the connection string you can include the default database, or in the SQL query add "Use DatabaseName" to select the correct database. The in the User Account in Windows add the users to the same group as the database is using. You do not need AUTHORITY NT \ SYSTEM which is a higher level Admin Account. Just create a normal group account and put the database credentials and users in same group. – jdweng Jan 29 '19 at 14:08
  • 1
    @jdweng The problem is that it will be installed on several machines and I do not know which users are registered in windows, but it is possible to register a user at the time of installation? Another issue is that Integrated Security = True is clear on the connection, but why is it not working? – mba Jan 29 '19 at 16:46
  • 1
    You can modify a windows user account any time if you have admin privileges. In large companies they use Group Policy and giving the SQL Database credentials the Group Policy will work. If it is not working first type with SQL Server Management Studio. The make sure the connection string you are using include the Server name and instance of the database that is in the SSMS login window. – jdweng Jan 29 '19 at 16:55

1 Answers1

0

From what I understand you're trying to access the mdf file through two simultaneous LocalDB instances(two different apps), which won't work, since the file is locked by the first instance which attaches it.

If you need multiple apps to connect to your DB(mdf), you need to install an instance of SQL Server(Express) and attach the mdf file as a database to it.

Then you can update your connection string to use the database from the SQL Server instance.

jbud
  • 694
  • 5
  • 7