Missing Path to SQL Server Express User Instance, can't see all databases in SQL Server Management Tool

Question

I have a WPF application deployed via ClickOnce. This application uses a SQL Server Express database. I am trying to access my user instance of the database so that I can access the database that I am currently using when debugging so that I can verify writes to the DB made by entity framework. However, when I try to open the user instance using the direct pipe name: "\.\pipe\3FCB3BEB-9ACF-4E\tsql\query". I connect fine but when I expand the "Databases" node I get the following error:

TITLE: Microsoft SQL Server Management

Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

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

------------------------------ ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

---

Unable to open the physical file "C:\Users\mark.ti\AppData\Local\Apps\2.0\Data\RJ9HCXPL.ZWT\7VHVB97B.EB2\abcd..tion_b144089e1fe9cf01_0001.0000_b03f40a7888c1e15\Data\Resources\Data\MyDatabase.mdf". Operating system error 3: "3(The system cannot find the path specified.)". (Microsoft SQL Server, Error: 5120)

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

------------------------------ BUTTONS:

OK

The path does exist, its where the ClickOnce application was deployed to after install. After uninstalling the application, I still get this error, although, now the path truly is gone.

I have tried re-starting my SQL Server Express service to see if that will clear our the user instances but that didn't seem to work.

Has anybody else encountered this error when working with SQL Server Express? What is the problem here caused by? How do I reset the user instance database refereces?

Answer 1

Could be an an 'access denied error'.

1. Make sure the account the SQLSERVER service is running under is member of the local windows group SQLServer2005DTSUser$[instancename]. Where [instancename] refers to the name of the SQL Server instance in question.
2. Maks sure the the windows group has Modify permissions (NTFS) on the folder and the database files within this folder (files typically have .mdf and .ldf extensions).
3. If still in trouble make sure the SID of the Windows group mathces the one in the registry for the current SQL Server instance:

Go to SQL Server registry key to replace the obsolete SID with the current one. Those registry keys can be found like following example (you might need to replace \MSSQL.1\ with the one matching your instance number).

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

The group your're looking for is: SQLGroup