Administering your Windows Internal Database MICROSOFT##SSEE instance

By:   |   Comments (27)   |   Related: More > Database Administration


Problem

Microsoft products such as Windows Server Update Services (WSUS) 3.0 and Windows Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition. Now called the Windows Internal Database, more and more system administrators charged with managing WSUS and WSS are faced with the challenge of managing these databases. Since most of these system administrators are not full-fledged DBAs, how do they manage the Windows Internal Database?

Solution

The Windows Internal Database is an embedded data service that can only be used by a handful of Windows Services. It is designed in such a way that you are not allowed to connect to and use this particular database service for non-Microsoft products. By default, installing either WSUS 3.0 or WSS 3.0 will create the databases on the C:\ partition and will cause administration issues such as insufficient disk space. It is quite confusing to manage this SQL Server instance as it does not appear as a SQL Server service nor are there management tools included with the products. The easiest way is to use SQL Server Management Studio Express. You can install a copy of SQL Server Management Studio Express on the server running your WSUS 3.0 or WSS 3.0. You then register this instance using Named Pipes as this is the only configuration for connectivity. Use this server name when you register this instance 

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
management studio express

Once you've managed to register this instance in SQL Server Management Studio Express, you can now administer the WSUS 3.0 and WSS 3.0 databases like shrinking the database files or moving them to a different disk partition dedicated for them to avoid insufficient disk space issues.

studio express

If you prefer to use scripts to manage these databases, you can download and install the SQL Server 2005 Command Line Query Utility - sqlcmd . This tool will be installed, by default, on this directory C:\Program Files\Microsoft SQL Server\90\Tools\binn.

To connect to the database instance, you need to run the sqlcmd.exe utility, passing the instance name and your credentials 

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -E

You can then run your administrative T-SQL scripts once connected to this database instance.

Considerations

While these are possible reasons to administer the Windows Internal Database, it is not recommended to do anything beyond performing backups, moving or shrinking the database files. Modifying database schema or database properties would break supportability of these products. Plus, any changes made to these databases can be overwritten by the products' service packs or cumulative updates.

Next Steps
  • Install SQL Server Management Studio Express on your WSUS 3.0 and WSS 3.0 servers
  • Take control of your WSUS 3.0 and WSS 3.0 databases


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, March 15, 2017 - 3:24:51 AM - Matt Back To Top (51114)

 Hi Edwin, I'm trying to research failover solutions for AD FS on WID database. From the application perspective, I know I can issue a PS command to promote a secondary AD FS server to Primary, but I am not sure if the WID database can be failed over, if so, how? So my question is, can a read only WID database copy, be made into a R/W WID database? Are synchronised WID databases highly available?

 

Thursday, January 26, 2017 - 4:53:25 PM - anon Back To Top (45600)

 sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -E

 

this does not work

 

 

Thursday, May 19, 2016 - 6:09:57 AM - v-juanm Back To Top (41516)

 WSUS 4 version DB on Window 2012 string to connect

\\.\pipe\MICROSOFT##WID\tsql\query

You can got the new string from window service

Display name: Windows Internal Database services

Service Name: MSSQL$MICROSOFT##WID

path to Executable: C:\Windows\WID\Binn\sqlservr.exe -SMSWIN8.SQLWID -sMICROSOFT##WID

 

Monday, April 11, 2016 - 10:41:14 PM - zai Back To Top (41196)

 Is the Windows Internal database a paid product? or is it a free because it is pre-installed? 

does it require any license?

 

Saturday, December 20, 2014 - 7:40:07 AM - Suresh Ramasamy Back To Top (35699)

WSUS 3.0 / Windows Internal Database

Currentlying running WSUS 3.0 using the Windows Internal Database (MICROSOFT##SSEE) on Windows Server 2003 R2 SP2.

I'm expierencing numerious events like the following:

Source: MSSQL$MICROSOFT##SSEE
Catagory: (4)
Event ID: 18456
Type: Failure Audit
User: NT AUTHORITY\NETWORK SERVICE
Description: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: ]

Source: Windows Server Update
Catagory: Update Services Service
Event ID: 507
Type: Information
User: N/A
Description: Update Services failed its initialization and stopped.

WSUS 3.0 is configured using the default web site on ports 80/443.
The IIS and WSUS files are located on a second partition E:\

I tried chaning the account for the "Update Services" service to "Local System", but as far as I could tell that wasn't working so it was changed back to "Network Service".

I tried the following commands form a command prompt:
>  osql -E -S \MICROSOFT##SSEE
1)  sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE'
2)
This didn't seem like it was working as it just dropped me to line 2 I hit enter and it dropped me at 3) so I typed exit to return to the command prompt.

I have spent the last day and a half looking for an answer to this on Google but I haven't found a solution yet.

This issue ended up being a damaged/corrupt database. I was able to fix the problem with the Microsoft SQL Server Management Studio Express with the following query:

Finally I got a solution ,thanks for your screenshots which helped me a lot.

Connect the SUSDB Database and execute the below query.


 
EXEC sp_resetstatus 'SUSDB'; ALTER DATABASE SUSDB SET EMERGENCY DBCC checkdb('SUSDB') ALTER DATABASE SUSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('SUSDB', REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE SUSDB SET MULTI_USER

Monday, September 8, 2014 - 3:25:13 AM - vivek Back To Top (34433)

Hi,

Can anyone confirm how to do backup of Windows Internal Databse for AD RMS?

We have EMC Networker but i am not sure if we can use it to do backup because Windows internal db does not support remote connection.

I would appreciate if anyone can throw some light on how to backup of Windows internal db.

Vivek

Tuesday, May 20, 2014 - 1:26:54 PM - Jerry Smith Back To Top (30855)

 

Never mind ... figured it out... have to run the program "as administrator"

 

thanks!

Tuesday, May 20, 2014 - 1:25:09 PM - Jerry Smith Back To Top (30854)

Hello,

We are running the "free" version of Sharepoint 2007 and trying to troubleshoot the situation but first we want to try and log in to the database (the SP person left) as we do not have any SP knowledge

Anyway, when we tried to login with an account with admin privileges on our domain, we cannot login to the sql studio manager.

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

We get 

 


Cannot connect to \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query.


------------------------------

ADDITIONAL INFORMATION:


Login failed for user 'ourdomain\admin_jsmith'. (Microsoft SQL Server, Error: 18456)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

 

------------------------------

How can we login?

 

Thursday, January 9, 2014 - 8:38:40 PM - Hossam Elmosallamy Back To Top (28010)

Thank you Stephan very much ....it was a nightmare :)) you helped me a lot with this hint: 

\\.\pipe\MICROSOFT##WID\tsql\query

Tuesday, March 26, 2013 - 3:33:23 PM - bass_player Back To Top (23025)

Hi Neil,

I don't think Windows Search Server uses SQL Server under-the-covers. I couldn't find anything in the documentation on TechNet that specifically points to a SQL Server database. So, the steps outlined above will not work. I might also be wrong in my assumptions

Tuesday, March 26, 2013 - 1:59:46 PM - Neil Back To Top (23023)

A shot in the dark here but I'm trying to connect to the Windows Search Server 4.0 'database' and can't with the above examples.  So I'm either barking up the wrong tree or WSS4.0 has a completely different connection model.  Anyone offer anything in the way of enlightenment?

Friday, March 8, 2013 - 2:59:34 PM - Tolits Back To Top (22671)

Thanks Edwin! After so many hours of searching for solution, good thing i found this website. It works like a charm!! 

Friday, March 1, 2013 - 4:08:05 AM - Stephan Back To Top (22499)

Just if someone runs into the same problem: I've tried to use your connector: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query on a Windows Server 2012 internal database and it doesn't work.

Now it seems to be a new connector string you have to use with 2012: \\.\pipe\MICROSOFT##WID\tsql\query


best regards

Thursday, February 21, 2013 - 10:30:24 AM - Gene Back To Top (22340)

When trying to do a backup of the WSUS DB, it defaults to a non-existent disk location at D:\WSUS\{2AE3D45E-09F4-4B90-B694-22AAFE208AD1}\SUSDB.bak.  When I create another backup location and try to run the backup if fails with an error pointing to D:\WSUS\{2AE3D45E-09F4-4B90-B694-22AAFE208AD1}\SUSDB.bak as not being found.  Is it safe to delete this default location?

We use a centralized backup solution (Tivoli Storage Manager) that cannot do a normal SQL backup of the WSUS DB remotely.  The file backup fails as being in use leaving us with no way to backup the WSUS DB.  I'd like to script the backup within SQL and then capture a backup of the backup file with Tivoli.

Any ideas?

Wednesday, February 6, 2013 - 7:10:39 PM - bass_player Back To Top (21955)

If you are a local Administrator on the machine running these SQL Server instances, you can still regain access to them even when the sa account is disabled or even when you do not have access. Here's how you can do it

http://msdn.microsoft.com/en-us/library/dd207004(v=sql.100).aspx

Wednesday, February 6, 2013 - 8:31:27 AM - Mike Hinds Back To Top (21948)

I have two of these for SharePoint, and four for VMM management. I am unable to make myself SysAdmin on these, as the only two logins are "sa" which is disabled, and BUILTIN\Users, which is crippled. Not being a SysAdmin, it's not possible to keep these patched.

Friday, January 4, 2013 - 6:36:17 AM - Stanislav Back To Top (21257)

Thank you for this post, it was a solution for our problem!

Monday, December 17, 2012 - 10:32:43 AM - bass_player Back To Top (20985)

You are correct. However, from a licensing and support perspective, you are not allowed to connect any line-of-business application to the WID aside from those sanctioned by Microsoft.

Monday, December 17, 2012 - 7:34:17 AM - EdVassie Back To Top (20982)

It is designed in such a way that you are not allowed to connect to and use this particular database service for non-Microsoft products

I am not sure this is true.  Any appilcation that can connect using Named Pipes can use the Windows Internal Database.  It has the same operational characteristics as SQL Express (1 CPU, limited memory, etc), except that database size is unlimited.

Before you think you can run your 1TB database on WID and avoid the Standard Edition license, remember that the CPU cores and memory avalable to Standard Edition are there to give performance, so don't expect WID to be fast if your DB is large.

Friday, November 30, 2012 - 10:10:23 PM - Ram Back To Top (20682)

Thanks a ton, you have provided very clear instructions, i was searching for it for a while. Thanks agian.

 

Tuesday, March 27, 2012 - 7:51:48 AM - Alwin Back To Top (16632)

Hi Edwin,

is it possible to create a linked server in Windows Internal Database

Saturday, March 12, 2011 - 3:50:08 AM - bass_player Back To Top (13189)

Hi Wally,

Be aware that shrinking SQL Server databases - including SharePoint-related ones - is not recommended due to internal fragmentation both for the data files and the log file. If you are concerned about your log file growing and are not concerned about point-in-time recoverability for your SharePoint databases, set the database RECOVERY model to SIMPLE, shrink the log file to the smallest size possible and resize it to a size that you think would be enough for it to contain the active transactions without growing unexpectedly. You can resize your log file to 4GB and monitor accordingly. This process minimizes the amount of virtual log files while the recovery model truncates your log, thus, keeping it from growing 

Thursday, March 10, 2011 - 12:38:35 AM - Wally Back To Top (13164)

This is brilliant - I have been stuck trying to connect to the instance of Sharepoint and couldn't work out why I couldn't connect.

I am now in and can shrink the DB - many thanks for such a useful post..shame I couldn't find this on MS this easily.

Wally

Wednesday, October 27, 2010 - 7:38:12 PM - Joel Back To Top (10306)

Edwin,

Thank you!  You've brought an answer to my issue after hours of searching!

Sunday, September 7, 2008 - 12:20:56 PM - DDS Back To Top (1763)

Got it.  The instructions were missing one leading "\".     \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

Sunday, September 7, 2008 - 12:03:18 PM - DDS Back To Top (1762)

Great Tip but I was unable to connect with (\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query) and result was:   "An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)"

Sunday, September 7, 2008 - 9:18:15 AM - Preethi Back To Top (1760)

Very good topic. Good to know the internals and the fact that Micorsoft has increased the usage of SQL Server in many of its products.















get free sql tips
agree to terms