Wednesday, February 29, 2012

Lightswitch needs SQL Express 2008 but no sysadmin rights


The PC I am using for my current assignment was given to me by my predecessor, it runs on Windows 7. He had installed VS2008 with SQL Express 2005. I needed to create a lightswitch app and therefore I installed VS2010, and then VS Lightswitch 2011.

The VS2010 SQL express installation failed, maybe because a SQL Express 2005 had been installed by my predecessor.

Since the SQL Express 2005 was there, I tried to develop a Lightswitch app with security. To use the security features on my developer PC, VS Lightswitch wants to use SQLEXPRESS. when you debug the app and try to create Roles under the Administration tab, the roles cannot be saved. I tried to make it use a named Developer version of SQL 2008 that was also installed on this PC, but to no avail (how is for the next topic). When trying to save a role on the SQL Dev version, I got the error message 'The user instance login flag is not supported on this version of SQL Server. The connection will be closed'.

Lightswitch needs SQL Express 2008 on the developer pc and to create databases in SQLEXPRESS I need admin rights on the SQLEXPRESS instance.

Solution:
  • Download the SQL Express 2008 R2.
  • Uninstalling the SQL 2005 express failed. Install as an upgrade on the SQL 2005 express worked.
  • Run SQL server management studio to add my windows daomin account as sysadmin. Since my predecessor had done the initial installation, I did not have admin rights, so that did not work.
  • Final Solution: run SQL server Management studio as Administrator, then add my account as sysadmin, this worked.

Finally: tested my Lightswitch project, added a role and saved it. Problem solved