SQL Server数据库连接报错：不支持'data source'及无法登录/打开数据库

Let's break down your two connection problems one by one and walk through the fixes:

Issue 1: "Keyword not supported 'data source'" Error #

This error pops up because you're using a standard SQL Server connection string but setting System.Data.EntityClient as the provider. EntityClient requires a specific format that includes metadata for Entity Framework models—you can't just use the plain Data Source/Initial Catalog keys with it.

Solutions: #

• Option 1: Use EntityClient (for EDMX-based EF projects)
  If you're working with an Entity Framework EDMX model, wrap your SQL Server connection string inside an EntityClient container that includes metadata paths. Here's the correct format:

  <add name="ProductEntities" 
       connectionString="metadata=res://*/ProductModel.csdl|res://*/ProductModel.ssdl|res://*/ProductModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=**.**.***.**\MSSQLSERVER2017;Initial Catalog=product1_;User ID=product;Password=*****;MultipleActiveResultSets=True&quot;" 
       providerName="System.Data.EntityClient" />
  

  Replace ProductModel with the name of your EDMX file (without the .edmx extension). Note the escaped double quotes (") around the inner connection string—this is required for XML compatibility.

• Option 2: Switch to SqlClient (for direct SQL connections)
  If you don't need EntityClient (e.g., using SqlConnection directly), just change the provider name to System.Data.SqlClient and keep your original connection string structure:

  <add name="ProductEntities" 
       connectionString="Data Source=**.**.***.**\MSSQLSERVER2017;Initial Catalog=product1_;User ID=product;Password=*****;" 
       providerName="System.Data.SqlClient" />
  

Issue 2: Failed Login & "Database Cannot Be Opened" Error #

This is usually a mix of authentication conflicts, network blocks, or permission gaps. Let's troubleshoot each possible cause:

1. Fix Authentication Conflict #

One of your connection strings includes both User Id=product and integrated security=True—these are mutually exclusive:

• integrated security=True uses your Windows account to log in (Windows Authentication)
• User Id/Password uses SQL Server Authentication

Pick one approach and clean up your connection string:

• For SQL Auth: Remove integrated security=True
• For Windows Auth: Remove User Id and Password, keep integrated security=True

2. Verify Server/Instance & Network Access #

• Double-check the server IP (**.**.***.**) and instance name (MSSQLSERVER2017). Note: Default SQL Server instances use MSSQLSERVER—confirm the remote server is actually using the named instance you specified.
• Since you used Network Library=DBMSSOCN (TCP/IP protocol):
  • Ensure TCP/IP is enabled for the SQL Server instance (check in SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [Instance Name])
  • Open the SQL Server port in the remote server's firewall (default is 1433; for named instances, confirm the port number in SQL Server Configuration Manager and open it)
  • Test connectivity with telnet **.**.***.** 1433 (if telnet is enabled on your machine) to make sure the port is reachable.

3. Check User Permissions #

• Log into the remote SQL Server via SSMS and confirm:
  • The product user is mapped to the product1_ database (go to Security > Logins > product > User Mapping, then check product1_ and assign roles like db_datareader/db_datawriter for basic access)
  • The product user is allowed remote login (in the login's properties, ensure "SQL Server Authentication" is selected and "Deny login" is unchecked)

4. Confirm Database Availability #

• Ensure the product1_ database exists on the remote server and is in an online state (not offline, restoring, or marked as suspicious). You can check this in SSMS by looking at the database's status icon.

内容的提问来源于stack exchange，提问作者omer1596