locked
"The underlying provider failed on Open" - SQL Server and Entity Framework RRS feed

  • Question

  • Hi,

    I'm creating a Windows Service which works on a SQL Server DB. I'm using EF.

    In the OnStart() method I've an instruction which call a method which contains some DB operation. But when I start the service, in a logFile I obtain the following exception:

    The underlying provider failed on Open. in System.Data.Entity.Core.EntityClient.EntityConnection.Open()
       in System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
       in System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
       in System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
       in System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       in System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       in System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       in MyProject.MyClass.setSomeTables() 

    The connection string in App.config file is:

      <add name="MyDBEntities" connectionString="metadata=res://*/Models.MyDB.csdl|res://*/Models.MyDB.ssdl|res://*/Models.MyDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\mssqllocaldb;initial catalog=MyDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

    Before asking for help, I searched a solution through Internet and I read so many blog posts or forum replies, but everything I found seems not to be useful for my problem.

    • I'm sure that the instance name is correct.
    • I've tried to replace "App=EntityFramework" with "Application Name=EntityFramework"
    • I've tried to create a ad hoc user and I entered "user id" and "password" fields
    • I've tried to insert "persist security info=True" field
    • I've tried to insert "persist security info=False" field
    • I've tried to re-import the model
    • I've tried to force the db open in MyDBEntities : DbContext class constructor (this.Database.Connection.Open())

    but nothing changed!

    How I can solve this?

    Thank you



    Friday, October 11, 2019 1:51 PM

Answers

  • Hi,

    1. Is your SQL Server installed on the same machine as your application?

    2. Can you connect the server using SSMS?
    If not, then this is the first step we should do.

    3. Which version of SQL Server are you using?

    4. Are you connect the server using SQL Server User or Windows User?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Hi, sorry for the late response. I solve this problem doing that:

    • Open the Windows Services
    • I found my services
    • right click > properties
    • connection 
    • I deselect the local system account and I choose my domain account

    Monday, October 14, 2019 2:09 PM

All replies

  • Hi,

    I'm creating a Windows Service which works on a SQL Server DB. I'm using EF.

    In the OnStart() method I've an instruction which call a method which contains some DB operation. But when I start the service, in a logFile I obtain the following exception:

    The underlying provider failed on Open. in System.Data.Entity.Core.EntityClient.EntityConnection.Open()
       in System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
       in System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
       in System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
       in System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       in System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       in System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       in MyProject.MyClass.setSomeTables() 

    The connection string in App.config file is:

      <add name="MyDBEntities" connectionString="metadata=res://*/Models.MyDB.csdl|res://*/Models.MyDB.ssdl|res://*/Models.MyDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\mssqllocaldb;initial catalog=MyDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

    Before asking for help, I searched a solution through Internet and I read so many blog posts or forum replies, but everything I found seems not to be useful for my problem.

    • I'm sure that the instance name is correct.
    • I've tried to replace "App=EntityFramework" with "Application Name=EntityFramework"
    • I've tried to create a ad hoc user and I entered "user id" and "password" fields
    • I've tried to insert "persist security info=True" field
    • I've tried to insert "persist security info=False" field
    • I've tried to re-import the model
    • I've tried to force the db open in MyDBEntities : DbContext class constructor (this.Database.Connection.Open())

    but nothing changed!

    How I can solve this?

    Thank you

    Good day,

    Please check the following document regarding the connection string which you should use:
    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/creating-a-connection-string


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, October 11, 2019 2:46 PM
  • Hi,

    I'm creating a Windows Service which works on a SQL Server DB. I'm using EF.

    In the OnStart() method I've an instruction which call a method which contains some DB operation. But when I start the service, in a logFile I obtain the following exception:

    The underlying provider failed on Open. in System.Data.Entity.Core.EntityClient.EntityConnection.Open()
       in System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
       in System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
       in System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       in System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
       in System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       in System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       in System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       in MyProject.MyClass.setSomeTables() 

    The connection string in App.config file is:

      <add name="MyDBEntities" connectionString="metadata=res://*/Models.MyDB.csdl|res://*/Models.MyDB.ssdl|res://*/Models.MyDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\mssqllocaldb;initial catalog=MyDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

    Before asking for help, I searched a solution through Internet and I read so many blog posts or forum replies, but everything I found seems not to be useful for my problem.

    • I'm sure that the instance name is correct.
    • I've tried to replace "App=EntityFramework" with "Application Name=EntityFramework"
    • I've tried to create a ad hoc user and I entered "user id" and "password" fields
    • I've tried to insert "persist security info=True" field
    • I've tried to insert "persist security info=False" field
    • I've tried to re-import the model
    • I've tried to force the db open in MyDBEntities : DbContext class constructor (this.Database.Connection.Open())

    but nothing changed!

    How I can solve this?

    Thank you

    Good day,

    Please check the following document regarding the connection string which you should use:
    https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/creating-a-connection-string


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    still not working! :(
    Friday, October 11, 2019 3:13 PM
  • Hi,

    1. Is your SQL Server installed on the same machine as your application?

    2. Can you connect the server using SSMS?
    If not, then this is the first step we should do.

    3. Which version of SQL Server are you using?

    4. Are you connect the server using SQL Server User or Windows User?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, October 12, 2019 12:38 AM
  • Hi,

    1. Is your SQL Server installed on the same machine as your application?

    2. Can you connect the server using SSMS?
    If not, then this is the first step we should do.

    3. Which version of SQL Server are you using?

    4. Are you connect the server using SQL Server User or Windows User?


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Hi, sorry for the late response. I solve this problem doing that:

    • Open the Windows Services
    • I found my services
    • right click > properties
    • connection 
    • I deselect the local system account and I choose my domain account

    Monday, October 14, 2019 2:09 PM
  • I this moment doing this change is ok, but in future I must take other ways. 
    Monday, October 14, 2019 2:10 PM
  • I am glad to hear that you found a procedure that fits your needs👍

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, October 14, 2019 4:40 PM