MSSQL 오류 '기본 공급자가 열리지 못했습니다.'

MSSQL 오류 '기본 공급자가 열리지 못했습니다.'

---

나는 사용하던 .mdfA를 연결 database하고 entityClient. 이제 .mdf파일 이 없도록 연결 문자열을 변경하고 싶습니다 .

다음이 connectionString맞습니까?

<connectionStrings>
   <!--<add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQL2008;AttachDbFilename=|DataDirectory|\NData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />-->
   <add name="conString" connectionString="metadata=res://*/conString.csdl|res://*/conString.ssdl|res://*/conString.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQL2008;Initial Catalog=NData;Integrated Security=True;Connect Timeout=30;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />

항상 오류가 발생하기 때문에 :

기본 공급자가 Open에서 실패했습니다

---

이 오류가 발생하여 몇 가지 해결책을 찾았습니다.

연결 문자열을 보면 유효 해 보입니다. 이 블로그 게시물을 찾았 는데 여기서 문제는 통합 보안 을 사용하고 있다는 것 입니다. IIS에서 실행중인 경우 IIS 사용자는 데이터베이스에 액세스해야합니다.

Transactions와 함께 Entity Framework 를 사용하는 경우 Entity Framework 는 각 데이터베이스 호출과의 연결을 자동으로 열고 닫습니다. 따라서 트랜잭션을 사용할 때 여러 연결을 통해 트랜잭션을 분산 시키려고합니다. 이것은 MSDTC로 상승합니다 .

( 자세한 내용은이 참조를 참조하십시오. )

내 코드를 다음과 같이 수정하면 수정되었습니다.

using (DatabaseEntities context = new DatabaseEntities())
{
    context.Connection.Open();
    // the rest
}

---

context.Connection.Open() 내 문제를 해결하는 데 도움이되지 않았으므로 DTC 구성에서 "원격 클라이언트 허용"을 활성화하려고 시도했지만 더 이상 오류가 없습니다.

Windows 7에서는 dcomcnfg, 구성 요소 서비스-> 컴퓨터-> 내 컴퓨터-> 분산 트랜잭션 코디네이터-> 로컬 DTC-> 보안을 마우스 오른쪽 단추로 실행하여 DTC 구성을 열 수 있습니다.

---

당신은 볼 수 의 InnerException가 오류로 던지는의 내부 원인이 무엇인지 확인합니다.

필자의 경우 원래 오류는 다음과 같습니다.

실제 파일 "D : \ Projects2 \ xCU \ xCU \ App_Data \ xCUData_log.ldf"를 열 수 없습니다. 운영 체제 오류 5 : "5 (액세스가 거부되었습니다." "). 파일 D : \ Projects2 \ xCU \ xCU \ App_Data \ xCUData.mdf에 대해 자동 명명 된 데이터베이스를 연결하지 못했습니다. 이름이 같은 데이터베이스가 있거나 지정된 파일을 열 수 없거나 UNC 공유에 있습니다.

파일 속성을 사용하여 관련 mdf및 ldf파일 에 액세스하기 위해 현재 사용자에게 모든 권한을 부여하여 해결되었습니다 .

---

문제는 다음 변형 중 하나의 연결 문자열 내에 서버 경로가 있다는 것입니다.

SERVER\SQLEXPRESS
SERVER

내가 정말로해야 할 때 :

.\SQLEXPRESS

어떤 이유로 든 SQL 인스턴스를 찾는 데 어려움이있을 때마다 오류가 발생했습니다.

---

이것은 일반적인 문제입니다. 심지어이 문제에 직면했습니다. Windows 인증으로 구성된 개발 시스템에서 완벽하게 작동합니다.

<add name="ShoppingCartAdminEntities" connectionString="metadata=res://*/ShoppingCartAPIModel.csdl|res://*/ShoppingCartAPIModel.ssdl|res://*/ShoppingCartAPIModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQlExpress;initial catalog=ShoppingCartAdmin;Integrated Security=True;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

동일한 구성으로 IIS에서 호스팅하면이 오류가 발생합니다.

기본 공급자가 Open에서 실패했습니다

connectionString구성 파일에서 변경 되어 해결 되었습니다.

<add name="MyEntities" connectionString="metadata=res://*/ShoppingCartAPIModel.csdl|res://*/ShoppingCartAPIModel.ssdl|res://*/ShoppingCartAPIModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MACHINE_Name\SQlExpress;initial catalog=ShoppingCartAdmin;persist security info=True;user id=sa;password=notmyrealpassword;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

다른 일반적인 실수는 다음과 같습니다.

1. 데이터베이스 서비스가 중지 될 수 있습니다
2. Windows 인증을 사용하고 IIS에서 호스팅되는 로컬 데이터베이스를 가리키는 데이터 소스 속성
3. 사용자 이름과 비밀번호가 잘못되었을 수 있습니다.

---

When you receive this exception, make sure to expand the detail and look at the inner exception details as it will provide details on why the login failed. In my case the connection string contained a user that did not have access to my database.

Regardless of whether you use Integrated Security (the context of the logged in Windows User) or an individual SQL account, make sure that the user has proper access under 'Security' for the database you are trying to access to prevent this issue.

---

I had a similar issue with the SQL Server Express Edition on Windows Server 2003. I simply added the network service as a user in the database security.

---

The SQL Server Express service were not set tostart automatically.

1) Go to control panel 2) Administrative Tools 3) Service 4) Set SQL Server express to start automatically by clicking on it 5) Right click and start the service

I hope that will help.

---

This can also happen if you restore a database and the user already exists with different schema, leaving you unable to assign the correct permissions.

To correct this run:

USE your_database
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'cf'
GO
EXEC sp_change_users_login 'update_one', 'user', 'user'
GO

---

I posted a similar issue here, working with a SQL 2012 db hosted on Amazon RDS. The problem was in the connection string - I had "Application Name" and "App" properties in there. Once I removed those, it worked.

Entity Framework 5 and Amazon RDS - "The underlying provider failed on Open."

---

Make sure that each element value in the connection string being supplied is correct. In my case, I was getting the same error because the name of the catalog (database name) specified in the connection string was incorrect.

---

I had a similar issue with exceptions due to the connection state, then I realized I had my domain service class variable marked as static (by mistake).

My guess is that once the service library is loaded into memory, each new call ends up using the same static variable value (domain service instance), causing conflicts via the connection state.

I think also that each client call resulted in a new thread, so multiple threads accessing the same domain service instance equated to a train wreck.

---

I had a similar error with the inner exception as below:

operation is not valid for the state of the transaction

I could resolve it by enabling DTC security settings.

Go To Properties of DTC, under Security Tab, check the below

• Network DTC Access
• Allow RemoteClients
• Transaction Manager Communication
• Allow Inbound
• Allow Outbound

---

If you happen to get this error on an ASP.NET web application, in addition to other things mentioned check the following:

1. Database User Security Permissions (which users are allowed access to your database.
2. Check your application pool in IIS and make sure it's the correct one that is allowed access to your database.

---

I got rid of this by resetting IIS, but still using Integrated Authentication in the connection string.

---

Defining a new Windows Firewall rule for SQL Server (and for port 1433) on the server machine solves this error (if your servername, user login name or password is not wrong in your connection string...).

---

I had the same problem but what worked for me was removing this from the Connection String:

persist security info=True

---

A common mistake that I did because I was moving application from once pc to another and none of the above worked was that I forgot to copy the connection string to both App.Config and Web.Config!

---

I had a similar problem: In my test-cases executions I always got this error. I found out, that my "Distributed Transaction Service" was not started (run: services.msc -> start "Distributed Transaction Service" (best to set it to start automatic)). After I did that, it worked like a charm...

---

I copied the database files (.mdf/.ldf) to the App_Data folder to get rid of this exception.

---

I was also facing the same issue. Now I have done it by removing the user name and password from the connection string.

---

For me it was just a simple mistake:

I used Amazon EC2, and I used my elastic IP address in the connection string, but when I changed IP addresses I forgot to update my connection string.

---

I had this error suddenly happen out of the blue on one of our sites. In my case, it turned out that the SQL user's password had expired! Unticking the password expiration box in SQL Server Management Studio did the trick!

---

I had the same issue few days ago, using "Integrated Security=True;" in the connection string you need to run the application pool identity under "localsystem" Sure this is not recommended but for testing it does the job.

This is how you can change the identity in IIS 7: http://www.iis.net/learn/manage/configuring-security/application-pool-identities

---

In IIS set the App Pool Identity As Service Account user or Administrator Account or ant account which has permission to do the operation on that DataBase.

---

In my case I had a mismatch between the connection string name I was registering in the context's constructor vs the name in my web.config. Simple mistake caused by copy and paste :D

    public DataContext()
        : base(nameOrConnectionString: "ConnStringName")
    {
        Database.SetInitializer<DataContext>(null);
    }

---

i have the same error i found thats when i change my connectionString to new Data Source i forget to change the Username and passowrd for the new database

---

I have also had this error happen if the SQL Server Instance name isn't specified and the SQL host has multiple SQL instances installed. Here's a couple examples to clarify:

The connection string below results in the exception "The underlying provider failed on Open" with no inner exception in a .NET WebForms app:

 connectionString="metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=System.Data.SqlClient;provider connection string="Data Source=localhost;Initial Catalog=Entities;User ID=user;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"

The following connection string executes as expected in a .net WebForms app where the SQL environment has multiple instances. Rare I know, but I have a few different SQL instances on my dev box to accommodate different projects:

 connectionString="metadata=res://*/Entities.csdl|res://*/Entities.ssdl|res://*/Entities.msl;provider=System.Data.SqlClient;provider connection string="Data Source=localhost\SQLSERVER2014;Initial Catalog=Entities;User ID=user;Password=password;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient"

---

in my case server address have been changed by server admin so i had to change connection string to new server address

---

I had this problem because the Application Pool login this app was running under had changed.

In IIS:

• Find the Application pool by clicking on your site and going to Basic Settings.

• Go to Application Pools.

• Click on your site's application pool.

• Click on Advanced Settings.

• In Identity, enter account login and password.

• Restart your site and try again.

참고URL : https://stackoverflow.com/questions/2475008/mssql-error-the-underlying-provider-failed-on-open