SQL Express supports per-user instances (User Instance) where each user's data is stored in their own user profile. This feature goes hand in hand with Cameyo's data persistence. However, some issues prevent SQL Express from working properly in Cameyo's environment where sessions are frequently logged into and out from.
Symptom
While SQL Express sessions generally work well with Cameyo, after a few session logouts + logins the SQL Express engine may start issuing a connection error, specifically SQL error 15372.
Cause
The issue comes from a bug in SQL Express. After a few logouts + logins, the main SQLServr.exe process fails to run session-specific SQLServr.exe process using CreateProcessAsUser. A closer look at SQLServr's inner use of this API shows that it tries to reuse the user token (hToken) using the same handle number across logged out / in sessions.
A recurring pattern can be observed in this case, for example:
- Session started as User1
- CreateProcessUser(hToken=0x123) => success
- Session logged out
- Another session started as User1
- CreateProcessUser(hToken=0x123) => error: invalid token
This token reuse is a Microsoft SQL Express bug.
Resolution
It is recommended to use LocalDB -- a more recent variation of SQL Express which gives more flexibility and control around user sessions. For this, two changes need to be made:
1. Connection string
The database connection string needs to be changed from something like this:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SomeDB.mdf;Integrated Security=True;User Instance=True
to something like this:
Data Source=(LocalDb)\MyDB;AttachDbFilename=X:\Documents\SomeDB.mdf;Integrated Security=True
2. Session initialization script
Upon session start (i.e. using C:\ProgramData\Cameyo\StartupBefore.bat), execute:
SqlLocalDb create "MyDB" -s
This will create the (LocalDB)\MyDB instance indicated in step 1.