moving security database from App_Data to SQL express - connection string [Answered]RSS

• • Reply
  SwainIT

  Member

  2 Points

  10 Posts

  moving security database from App_Data to SQL express - connection string

  Jun 23, 2015 12:32 PM|SwainIT|LINK

  I am using a VM instance of Server 2012 Datacenter and SQL Express 2012. Using VStudio I ported the auto-generated aspnetdb.mdf to the same instance of SQL Exp. 2012 as my site's data. The site data connection is working great. I need to add security to the site with roles/membership etc.

  VStudio generated the connection string using an abstracted path to App_Data as the default. I attempted to mimic the path change using my data connection.

    <connectionStrings>
      <add name="ApplicationServices"
           connectionString="data source=EOWYN\SQLEXPRESS2012;Integrated Security=SSPI;Initial Catalog=Intake_security;User Instance=true"
           providerName="System.Data.SqlClient" />
      <add name="Intake_be"
           connectionString="Data Source=EOWYN\SQLEXPRESS2012;Initial Catalog=Intake_dev;Integrated Security=True"
        providerName="System.Data.SqlClient" />  
    </connectionStrings>
  
  

  Here is my error:

  Cannot open database "Intake_security.mdf" requested by the login. The login failed.
  Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

  Using SQL Studio I added Network Service to the mapping in SQL Express. In IIS I have the site application pool as NetworkService.

  Besides having a working connection to Intake_security.mdf , I may need advice on the security model. Will it be robust with the included parameters of these two connections? The site data includes PII of social worker clients.

  I read some threads but they were old and much has changed in that time frame.

  Thanks in advance.

  Kurt

• • Reply
  SwainIT

  Member

  2 Points

  10 Posts

  Re: moving security database from App_Data to SQL express - connection string

  Jun 24, 2015 02:50 PM|SwainIT|LINK

  I discovered that my instance of SQLExpress 2012 was set for only Windows Authentication mode. I changed it to SQL Server and Windows Authentication mode and rebooted the server. It had no effect on the connection.

  Looking in the Intake_security SQL server properties, NETWORK SERVICE is granted Connection rights. The non-working and working connections are set up the same.

  Looking in the data store folder at the Intake_security.mdf file's active directory Security tab, it is similar to the Intake_dev.mdf. _security.mdf has  an additional group "OWNER RIGHTS", granted Full control.

  I can find no security differences between the working data connection and the non-working security connection. What have I overlooked?

  My server hosts both SQLExpress and IIS. Both instances are each mounted on an iscsi san storage, which the server VM "sees" as internal. VM hypervisor is Citrix.

• • Reply
  Li Wang

  Star

  9860 Points

  974 Posts

  Microsoft

  Re: moving security database from App_Data to SQL express - connection string

  Jun 25, 2015 04:10 AM|Li Wang|LINK

  Hi Kurt
  Thank you for your post.
  The network protocol for user instances must be local Named Pipes. A user instance cannot be started on a remote instance of SQL Server, and SQL Server logins are not allowed. So try to remove User Instance=true and try again.
  Hoping my reply could be helpful to you.
  Best Regards,
  Wang Li

• • Reply
  SwainIT

  Member

  2 Points

  10 Posts

  Re: moving security database from App_Data to SQL express - connection string

  Jun 25, 2015 12:53 PM|SwainIT|LINK

  Li Wang, you ROCK! Yes, it worked great. I have looked many times for a comprehensive configuration guide with step by step for this very process, without luck. Most tutorials assume you are using the native VStudio wizard for security setup. It seems the security connection is different than a data connection and not well documented.

  Thanks for sharing the secret handshake. The good folks at Social Services send their deep thanks for your help.

  Regards,

  Kurt