Using SQL Localdb in a Windows Service

Using SQL LocalDB in a Windows Service

I was able to solve similar issue in our WiX installer recently. We have a Windows service, running under SYSTEM account, and an installer, where LocalDB-based storage is one of the options for database configuration. For some time (a couple of years actually) product upgrades and service worked quite fine, with no issues related to LocalDB. We are using default v11.0 instance, which is created in SYSTEM profile in C:\Windows\System32\config tree, and a database specified via AttachDbFileName, created in ALLUSERSPROFILE tree. DB provider is configured to use Windows authentication. We also have a custom action in installer, scheduled as deferred/non-impersonate, which runs DB schema updates.

All this worked fine until recently. After another bunch of DB updates, our new release started to fail after having upgraded over the former - service was unable to start, reporting infamous "A network-related or instance-specific error occurred while establishing a connection to SQL Server" (error 50) fault.

When investigating this issue, it became apparent that the problem is in a way WiX runs custom actions. Although non-impersonated CA-s run under SYSTEM account, the registry profile and environment remain that of current user (I suspect WiX loads these voluntary when attaching to user's session). This leads to incorrect path being expanded from the LOCALAPPDATA variable - the service receives SYSTEM profile one, but the schema update CA works with the user's one.

So here are two possible solutions. The first one is simple, but too intrusive to user's system - with cmd.exe started via psexec, recreate broken instance under the SYSTEM account. This was not an option for us as the user may have other databases created in v11.0 instance, which is public. The second option assumed lots of refactoring, but wouldn't hurt anything. Here is what to do to run DB schema updates properly with LocalDB in WiX CA:

1. Configure your CA as deferred/non-impersonate (should run under SYSTEM account);

2. Fix environment to point to SYSTEM profile paths:

   var systemRoot = Environment.GetEnvironmentVariable("SystemRoot");
   Environment.SetEnvironmentVariable("USERPROFILE", String.Format(@"{0}\System32\config\systemprofile", systemRoot));
   Environment.SetEnvironmentVariable("APPDATA", String.Format(@"{0}\System32\config\systemprofile\AppData\Roaming", systemRoot));
   Environment.SetEnvironmentVariable("LOCALAPPDATA", String.Format(@"{0}\System32\config\systemprofile\AppData\Local", systemRoot));
   Environment.SetEnvironmentVariable("HOMEPATH", String.Empty);
   Environment.SetEnvironmentVariable("USERNAME", Environment.UserName);
   

3. Load SYSTEM account profile. I used LogonUser/LoadUserProfile native API methods, as following:

   [DllImport("advapi32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
   [return: MarshalAs(UnmanagedType.Bool)]
   private static extern bool LogonUser(
       string lpszUserName,
       string lpszDomain,
       string lpszPassword,
       int dwLogonType,
       int dwLogonProvider,
       ref IntPtr phToken);
   
   [StructLayout(LayoutKind.Sequential)]
   struct PROFILEINFO
   {
       public int dwSize; 
       public int dwFlags;
       [MarshalAs(UnmanagedType.LPWStr)] 
       public String lpUserName; 
       [MarshalAs(UnmanagedType.LPWStr)] 
       public String lpProfilePath; 
       [MarshalAs(UnmanagedType.LPWStr)] 
       public String lpDefaultPath; 
       [MarshalAs(UnmanagedType.LPWStr)] 
       public String lpServerName; 
       [MarshalAs(UnmanagedType.LPWStr)] 
       public String lpPolicyPath; 
       public IntPtr hProfile; 
   }
   
   [DllImport("userenv.dll", SetLastError = true, CharSet = CharSet.Unicode)]
   [return: MarshalAs(UnmanagedType.Bool)]
   static extern bool LoadUserProfile(IntPtr hToken, ref PROFILEINFO lpProfileInfo);
   
   var hToken = IntPtr.Zero;
   var hProfile = IntPtr.Zero;
   bool result = LogonUser("SYSTEM", "NT AUTHORITY", String.Empty, 3 /* LOGON32_LOGON_SERVICE */, 0 /* LOGON32_PROVIDER_DEFAULT */, ref token);
   if (result)
   {
       var profileInfo = new PROFILEINFO();
       profileInfo.dwSize = Marshal.SizeOf(profileInfo);
       profileInfo.lpUserName = @"NT AUTHORITY\SYSTEM";
       if (LoadUserProfile(token, ref profileInfo))
           hProfile = profileInfo.hProfile;
   }
   

   Wrap this in an IDisposable class, and use with a using statement to build a context.

4. The most important - refactor your code to perform necessary DB updates in a child process. This could be a simple exe-wrapper over your installer DLL, or stand-alone utility, if your already have one.

P.S. All these difficulties could be avoided, if only Microsoft let uses choose where to create LocalDB instances, via command line option. Like Postgres' initdb/pg_ctl utilities have, for example.

how to access localDB from Windows Service

So i found out solution myself, how to connect instance of (localdb) from Windows Service which runs as Local System:

I used this articles:
http://technet.microsoft.com/pl-pl/library/hh212961.aspx
http://dba.fyicenter.com/faq/sql_server_2/Verifying_a_Login_Name_with_SQLCMD_Tool.html
http://social.technet.microsoft.com/wiki/contents/articles/4609.troubleshoot-sql-server-2012-express-localdb.aspx

So i shared my localdb (as in 1st article) the problem was I couldn't connect with sqlcmd named pipe np://. I found anwer in 3rd article:

When using sqlcmd, ensure that you are using the SQL Server 2012
version (found in %Program Files%\Microsoft SQL
Server\110\Tools\Binn). If you have previous versions of sqlcmd
installed, calling sqlcmd alone from the command line will most likely
use the old version (which isn't localdb-aware) since the older path
appears first in your PATH environment variable. It may be a good
idea, in general, to manually adjust your PATH environment variable so
that the 110 versions are picked up first.

This small information in fact was crucial;)

so i created user: user with password: pass321!@.

In my windows service my Sql connectionString looks:

"Data Source=(localdb)\\.\\MyInstanceShared;Integrated Security=false;User Id=user;Password=pass321!@"

Integrated security set to false value is also important.

Maybe it will help somebody.

Can't connect to SQL LocalDB from Windows Service, WPF app & SSMS work fine?

I'm not a professional in windows services.

I think the fault is in the windows service you have written. With the session zero isolation you may have to run the windows service in the specified user account.

It would be helpful if you can give the user account which the windows service runs. In default the service runs in a different user account called SYSTEM account. This account may not have permission to access the database. This might cause the problem.

LocalDb - Windows Service - Cannot open database requested by the login. The login failed. Login failed for user 'NT AUTHORITY\LOCAL SERVICE'

Local Service account is not supported for the SQL Server or SQL Server Agent services. Instead, use a domain account or local account with the most restrictive permission set.

See this.

---

Related Topics