CODEWITHSUNDEEP
c#asp.netsql-serverentity-framework
anapi
anapi

Reputation: 31

Entity Framework code first approach connection string configuration

I've implemented Identity & OWIN authentication to my existing asp.net web form application. I referred to the site: https://learn.microsoft.com/en-us/aspnet/identity/overview/getting-started/adding-aspnet-identity-to-an-empty-or-existing-web-forms-project

I am having problem with connection string for entity framework code first approach. I referred to the site: http://odetocode.com/Blogs/scott/archive/2012/08/14/a-troubleshooting-guide-for-entity-framework-connections-amp-migrations.aspx for help. But none of them is working.

I use VS 2015, SQL Server 2014 & Windows 10 OS.

For SQL Server authentication (Windows authentication) my server name is : DESKTOP-07C2G55.

I changed my connection string as told in the sites but none of them worked. My first connection string is::

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=DESKTOP-07C2G55;Initial Catalog=WebFormsIdentity;AttachDbFilename=|DataDirectory|\WebFormsIdentity.mdf;Trusted_Connection=Yes;Integrated Security=True" 
         providerName="System.Data.SqlClient" />
</connectionStrings>
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
        <parameters>
            <parameter value="v13.0" />
        </parameters>
    </defaultConnectionFactory>
    <providers>
        <provider invariantName="System.Data.SqlClient" 
                  type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>

On using this connection string when I try to register any user I am getting error as:

Directory lookup for the file "c:\users\myPC\documents\visual studio 2015\Projects\RENTAL\RENTAL\App_Data\WebFormsIdentity.mdf" failed with the operating system error 5(Access is denied.).

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Directory lookup for the file "c:\users\myPC\documents\visual studio 2015\Projects\RENTAL\RENTAL\App_Data\WebFormsIdentity.mdf" failed with the operating system error 5(Access is denied.).

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Then I run the following commands in my VS console. I get:

PM> Get-Service | Where-Object {$_.Name -like '*SQL*'}

Status   Name               DisplayName                           
------   ----               -----------                           
Running  MSSQLFDLauncher    SQL Full-text Filter Daemon Launche...
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)              
Running  MSSQLServerOLAP... SQL Server Analysis Services (MSSQL...
Stopped  SQLBrowser         SQL Server Browser                    
Stopped  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)        
Running  SQLWriter          SQL Server VSS Writer 

PM> SqlLocalDb info
MSSQLLocalDB

Then I changed my connection string, chaned the datasource to Data Source=MSSQLLocalDB:

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=MSSQLLocalDB;Initial Catalog=WebFormsIdentity;AttachDbFilename=|DataDirectory|\WebFormsIdentity.mdf;Trusted_Connection=Yes;Integrated Security=True"  
         providerName="System.Data.SqlClient" />
 </connectionStrings>

I get the error

Invalid value for key 'attachdbfilename'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Invalid value for key 'attachdbfilename'.

Again I changed my connection string to Data Source= .\SQLEXPRESS

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=WebFormsIdentity;AttachDbFilename=|DataDirectory|\WebFormsIdentity.mdf;Trusted_Connection=Yes;Integrated Security=True"  
         providerName="System.Data.SqlClient" />
 </connectionStrings>

I got the error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I can not figure out what is the problem whether the cause of the problem is connection string or any where else in the code. In other solutions, it is mentioned to edit DBContext Class, but in the tutorial there is no such class created. I am not using MVC, simple web form application trying to implement Identity and OWIN module. Please help me.

Upvotes: 2

Views: 2633

Answers (1)

marc_s
marc_s

Reputation: 755207

The output from your PowerShell script shows that you have a default instance (with the "instance" name of MSSQLSERVER) of SQL Server running:

Running  MSSQLSERVER        SQL Server (MSSQLSERVER)

This means, you can use Data Source=. (or Data Source=(local)) for your server name (you must not define an instance name to connect to the default instance), define the database name in Initial Catalog, and drop that AttachDbFileName crap once and for all (and let SQL Server handle all the file-related ins and outs - don't mess around with .mdf database files yourself!):

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=.;Initial Catalog=WebFormsIdentity;Trusted_Connection=Yes;Integrated Security=True"  
         providerName="System.Data.SqlClient" />
 </connectionStrings>

Upvotes: 4

Related Questions