First published on MSDN on May 04, 2017
If you ever considered using the Windows Internal Database (WID) as a regular SQL Server service, consider the following:
Windows Internal Database is a relational data store used by the following applications and services (list updated 04/2023 with the help of Nick Tilton - Windows Escalation engineer).
- Active Directory Rights Management Services (AD RMS)
- Windows Server Update Services
- Windows System Resource Manager
- Routing and Remote Access (RRAS)
- IP Address management (IPAM)
- Terminal Services (TermSrv)
- Active Directory Federation Services (ADFS)
WID is not supported beyond these or intended to be used for any other purposes.
If you are adventurous enough to create a user database in WID, you may find yourself with an unusable database. Here is what happens:
1. Create a database and a table
2. At one point you need to restart the WID service or the entire system (machine)
3. You connect back to WID and you notice that your user database is in "Recovery Pending"
4. You issue the "use test" command to switch context to the database and you get this error message:
Msg 945, Level 14, State 2, Line 1 Database 'test' cannot be opened due to inaccessible files or insufficient memory or disk space.
5. If you look at the Errorlog for the WID instance, you will find the following:
2017-05-03 19:48:26.17 spid20s Detected an unauthorized object in the schema
2017-05-03 19:48:26.17 spid20s Database verification script failed for database 'test'.
2017-05-03 19:48:26.17 spid20s Error: 3605, Severity: 16, State: 1.
2017-05-03 19:48:26.17 spid20s Schema verification failed for database 'test'.
Life lesson: WID is not intended for use as a general SQL Server instance and creating user databases on that instance is not supported. Only the services designed to use it are its rightful masters.
Namaste,
Joseph