sql server - Using SQL LocalDB in a Windows Service

Question

I have a very small test application in which I'm trying to install a Windows Service and create a LocalDB database during the install process, then connect to that LocalDB database when the Windows Service runs.

I am running into huge problems connecting to a LocalDB instance from my Windows Service.

My installation process is exactly like this:

1. Execute an installer .msi file which runs the msiexec process as the NT AUTHORITYSYSTEM account.

2. Run a custom action to execute SqlLocalDB.exe with the following commands:

   • sqllocaldb.exe create MYINSTANCE
   • sqllocaldb.exe share MYINSTANCE MYINSTANCESHARE
   • sqllocaldb.exe start MYINSTANCE

3. Run a custom C# action using ADO.NET (System.Data.SqlConnection) to perform the following actions:

   • Connect to the following connection string, Data Source=(localdb)MYINSTANCE; Integrated Security=true
   • CREATE DATABASE TestDB
   • USE TestDB
   • CREATE TABLE ...

4. Start the Windows Service before the installer finishes.

5. The Windows Service is installed to the LocalSystem account and so also runs as the NT AUTHORITYSYSTEM user account.

6. The service attempts to connect using the same connection string used above.

I am consistently getting the following error when trying to open the connection to the above connection string from within the Windows Service:

System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. The specified LocalDB instance does not exist.

This is frustrating because both the msi installer custom action and the Windows Service are running under the same Windows user account (I checked, they're both NT AUTHORITYSystem). So why the first works and the second does not is beyond me.

I have tried changing the connection strings used in the custom action and the Windows Service to use the share name (localdb).MYINSTANCESHARE and I get the exact same error from the Windows Service.

I have tried changing the user account that the Windows Service logs on as to my Windows user account, which does work as long as I first run a command to add it to the SQL server logins for that instance.

I've also tried running a console application and connecting to the share name connection string and that works as well.

I've also tried connecting to the share name from SQL Server Management Studio and that works as well.

However none of these methods really solve my problem. I need a Windows Service because it starts up as soon as the computer starts up (even if no user logs on) and starts up no matter which user account is logged in.

How does a Windows Service connect to a LocalDB private instance?

I am using SQL Server 2014 Express LocalDB.

Answer 1

Picking up from the comments on the question, here are some areas to look at. Some of these have already been answered in those comments, but I am documenting here for others in case the info might be helpful.

• Check here for a great source of info on SQL Server Express LocalDB:

  • SQL Server 2014 Express LocalDB
  • SqlClient Support for LocalDB
  • SqlLocalDB Utlity
  • Introducing LocalDB, an improved SQL Express (also look at the Q&A section at the end of the main post, just before the comments, as someone asked if LocalDB can be launched from a service, and the answer is:

    LocalDB can be launched from a service, as long as the profile is loaded for the service account.

• What version of .Net is being used? Here it is 4.5.1 (good) but earlier versions could not handle the preferred connection string (i.e. @"(localdb)InstanceName"). The following quote is taken from the link noted above:

  If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB.

  And according to the MSDN page for SqlConnection.ConnectionString:

  Beginning in .NET Framework 4.5, you can also connect to a LocalDB database as follows:

  server=(localdb)\myInstance

• Paths:

  • Instances: C:Users{Windows Login}AppDataLocalMicrosoftMicrosoft SQL Server Local DBInstances

  • Databases:

    • Created via SSMS or direct connection: C:Users{Windows Login}Documents or C:Users{Windows Login}
    • Created via Visual Studio: C:Users{Windows Login}AppDataLocalMicrosoftVisualStudioSSDT

• Initial Problem
  • Symptoms:
    • Database files (.mdf and .ldf) created in the expected location:
      C:WindowsSystem32configsystemprofile
    • Instance files created in an unexpected location:
      C:Users{current user}AppDataLocalMicrosoftMicrosoft SQL Server Local DBInstances
  • Cause (note taken from "SqlLocalDB Utility" MSDN page that is linked above; emphasis mine):

    Operations other than start can only be performed on an instance belonging to currently logged in user.

• Things to try:

  • Connection string that specifies the database (though maybe a long-shot if the error is regarding not being able to connect to the instance):
  • "Server=(LocalDB)MYINSTANCE; Integrated Security=true ;AttachDbFileName=C:WindowsSystem32configsystemprofileTestDB.mdf"

  • "Server=(LocalDB).MYINSTANCESHARE; Integrated Security=true ;AttachDbFileName=C:WindowsSystem32configsystemprofileTestDB.mdf"

  • Is the service running? Run the following from a Command Prompt:
    TASKLIST /FI "IMAGENAME eq sqlservr.exe"
    It should probably be listed under "Console" for the "Session Name" column

  • Run the following from a Command Prompt:
    sqllocaldb.exe info MYINSTANCE
    And verify that the value for "Owner" is correct. Is the value for "Shared name" what it should be? If not, the documentation states:

    Only an administrator on the computer can create a shared instance of LocalDB

  • As part of the setup, add the NT AUTHORITYSystem account as a Login to the system, which is required if this account is not showing as the "Owner" of the instance:
    CREATE LOGIN [NT AUTHORITYSystem] FROM WINDOWS; ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITYSystem];

  • Check the following file for clues / details:
    C:Users{Windows Login}AppDataLocalMicrosoftMicrosoft SQL Server Local DBInstancesMYINSTANCEerror.log

  • In the end you might need to create an actual account to create and own the Instance and Database, as well as run your service. LocalDB really is meant to be user-mode, and is there any downside to having your service have its own login? And you probably wouldn't need to share the instance at that point.

    And in fact, as noted by Microsoft on the SQL Server YYYY Express LocalDB MSDN page:

    An instance of LocalDB owned by the built-in accounts such as NT AUTHORITYSYSTEM can have manageability issues due to windows file system redirection; Instead use a normal windows account as the owner.

UPDATE (2015-08-21)

Based on feedback from the O.P. that using a regular User account can be problematic in certain environments, AND keeping in mind the original issue of the LocalDB instance being created in the %LOCALAPPDATA% folder for the user running the installer (and not the %LOCALAPPDATA% folder for NT AUTHORITYSystem ), I found a solution that seems to keep with the intent of easy installation (no user to create) and should not require needing extra code to load the SYSTEM profile.

Try using one of the two built-in accounts that is not the LocalSystem account (which does not maintain its own registry info. Use either:

• NT AUTHORITYLocalService
• NT AUTHORITYNetworkService

Both have their profile folders in: C:WindowsServiceProfiles

While I have not been able to test via an installer, I did test a service logging on as NT AUTHORITYNetworkService by setting my SQL Server Express 2014 instance to log on as this account, and restarted the SQL Server service. I then ran the following:

EXEC xp_cmdshell 'sqllocaldb c MyTestInstance -s';

and it created the instance in: C:WindowsServiceProfilesNetworkServiceAppDataLocalMicrosoftMicrosoft SQL Server Local DBInstances

I then ran the following:

EXEC xp_cmdshell N'SQLCMD -S (localdb)MyTestInstance -E -Q "CREATE DATABASE [MyTestDB];"';

and it had created the database in: C:WindowsServiceProfilesNetworkService