SQL Server Q&A

Symptoms
Web applications running on IIS 7.5 and that rely on SQL Server Express user instancing will fail to run using the default IIS 7.5 security configuration on both Windows 7 Client and Windows Server 2008 R2. Developers will encounter problems developing web applications using Visual Studio 2005 + SQL Server Express 2005, Visual Studio 2008 + SQL Server Express 2008, or Visual Studio 2010 + SQL Server Express 2008 on both Windows 7 Client and Windows Server 2008 R2.
Developers will encounter similar problems attempting to develop web application projects (WAP) or websites hosted under IIS6/IIS7/IIS7.5 that rely on SQL Server Express user instances where the WAP project structure or website folder structure exists in a user’s Documents folder.  This issue exists for all versions of Visual Studio regardless of the underlying operating system version.  A web application that attempts to create a database or read/write to a database using SQL Server Express user instance mode can encounter any of the following errors:
An attempt to attach an auto-named database for file c:\Users\[YourUserAccountName]\Documents\Visual Studio 20XX\Projects\[YourSolutionName]\[YourProjectnName]\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
–or–
Failed to generate a user instance of SQL Server due to failure in retrieving the user’s local application data path.  Please make sure the user has a local user profile on the computer. The connection will be closed.
 
NOTE: A web application relies on SQL Server Express’ user instance mode if either of the following is true: The application relies on the default “LocalSQLServer” connection string defined in machine.config The application uses a connection string that contains the following attributes:
            “AttachDBFilename=|DataDirectory|xxxxxx.mdf;User Instance=true”
 
 
Resolution
For Windows Server 2008 R2 and Windows 7
The default security configuration for IIS 7.5 sets application pools to run as the “application pool identity”.  Running an application pool using this special identity was first introduced as an optional setting in Vista SP2 and Windows Server 2008 SP2.  On Windows 7 Client and Windows Server 2008 R2 this special identity is now the default.
 
Web applications built with Visual Studio 2005, Visual Studio 2008, or Visual Studio 2010 and that rely on user instancing with either SQL Server Express 2005 or SQL Server Express 2008 do not work with the new application pool identity.  These products were developed and tested against application pools running with the older NETWORK SERVICE account.
 
For Web Application Projects and Websites Located in a User’s Documents Folder Hosted in IIS
Web application projects (WAP) exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Projects” folder.  Website projects exist in a folder structure under a user’s “Documents\Visual Studio 20XX\Websites” folder.  SQL Server Express user instances require file access rights to the parent folders of the website or WAP project’s directory structure.  Because the IIS service account (NETWORK SERVICE) by default does not have these rights within the Visual Studio project folder structure, WAP projects and websites located in a user’s Documents folder and that are hosted in IIS will not be able to open SQL Server Express user instanced databases for read access.
 
WAPs that were originally created within a user’s Documents folder, but were subsequently changed to use IIS for a web server via the web tab of the project’s properties will encounter this file permissions problem.  Websites hosted in IIS where the website directory structure is located within a user’s Documents folder will also encounter the file permissions problem.  This behavior occurs for WAP projects and websites hosted with any IIS versions that run as NETWORK SERVICE (IIS6, IIS7 and IIS 7.5) where the project structure exists within a user’s Documents folder.
 

Symptoms
When you create a licensed user control in a project, and then add it to the Windows form in the same solution, you receive the following exception:

An exception occurred while trying to create an instance of ‘Projectname.Controlname’. The exception was “An instance of type ‘Projectname.Controlname’ was being created, and a valid license could not be granted for the type ‘Projectname.Controlname’. Please, contact the manufacturer of the component for more information.”However, when you create a new solution, and then add the licensed user control to the Windows form, the licensed user control is added.
Resolution
Any user control that is implicitly added to the toolbox refers the assembly file in the obj\Debug folder. Therefore, the licensed user control that is created refers to the WindowsControlLibrary1.dll file in the obj\Debug folder. Generally, the license (.lic) files are put in the Bin folder. When you add an instance of a licensed user control to the Windows form in the same solution, the license is validated. Because the .lic file is not located in the obj\Debug folder, you receive the exception.

Symptoms
When you call a Microsoft SQL Server 2005 Integration Services (SSIS) package from a SQL Server Agent job step, the SSIS package does not run. However, if you do not modify the SSIS package, it will run successfully outside SQL Server Agent.
Resolution
This problem occurs when one of the following conditions is true:The user account that is used to run the package under SQL Server Agent differs from the original package author.The user account does not have the required permissions to make connections or to access resources outside the SSIS package.The package may not run in the following scenarios: The current user cannot decrypt secrets from the package.This scenario can occur if the current account or the execution account differs from the original package author,and the package’s ProtectionLevel property setting does not let the current user decrypt secrets in the package.A SQL Server connection that uses integrated security fails because the current user does not have the required permissions. File access fails because the current user does not have the required permissions to write to the file share that the connection manager accesses. For example, this scenario can occur with text log providers that do not use a login and a password. This scenario can also occur with any task that depends on the file connection manager, such as a SSIS file system task.A registry-based SSIS package configuration uses the HKEY_CURRENT_USER registry keys. The HKEY_CURRENT_USER registry keys are user-specific. A task or a connection manager requires that the current user account has correct permissions.