连接字符串无效或错误定位服务器/实例 [英] Connection string is not valid or Error Locating Server/Instance

问题描述

我重新安装了PC，现在我的SQL连接字符串或SQL Server 2012 Enterprise出现了问题
错误提示:

I reinstaled my PC and now I have problems with my SQL connection string or with SQL Server 2012 Enterprise
Error I get:

"... (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)"  or 

"... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

在SQL Server Management Studio服务器实例中，如下所示:

In SQL Server Management Studio server instance looks like this:

TOM-PC (SQL Server 11.0.3128 - Tom-PC\Tom)

以及ASP.NET XML配置中的My SQL连接字符串.文件:

And My SQL connection string in ASP.NET XML config. file:

<add name="ApplicationServices" connectionString="Data Source=.\TOM-PC;Database=D:\Programming\InvoiceManager\invocemanagerSQL.mdf;" providerName="System.Data.SqlClient" />

尝试所有操作，在防火墙中添加了端口1434或1433，这两个端口均经过测试，无法正常工作，"SQL Server Browser" 服务正在运行，已启用TCP/IP，允许远程连接到该服务器，甚至尝试了4不同的连接字符串，无济于事.在它像魅力一样起作用之前，还没有完全发挥作用，但是起作用了.为什么不充分?因为我无法同时在两个工作室"VS2010" 和"SQL Management studio" 的本地计算机上工作，所以如果我需要手动更改数据库中的某些内容，我需要停止或重新启动SQL Server进程和服务，然后进入数据库并读取表.现在，什么都没有起作用！我做错了什么?问题似乎在哪里?为什么SQL Server及其连接字符串总是有问题?

Tried everything, in firewall added port 1434 or 1433 tested both, not working, "SQL Server Browser" Service is running, TCP/IP Enabled, Allow remote connections to this server, even tried 4 different connection strings, nothing worked. Before it worked like a charm, not fully but worked. Why not fully? Because I couldn't work on my local machine with both studios "VS2010" and "SQL Management studio" at the same time, if I needed change something in database manually, I needed to stop or restart SQL Server process and service, then I get in database, and read tables. Now, nothing is working! What I do or did wrong? Where seems to be the problem? Why always are problems with SQL servers and it's connectionstrings?

推荐答案

在Management Studio中看到的内容如下:

What you are seeing in Management Studio is as follows:

Instance Name (SQL Server version - Domain\LoggedInUser)

您使用集成身份验证(即Windows登录帐户)登录，但未在连接字符串中指定该身份.这将尝试使用ASP.Net应用程序的当前登录用户，该应用程序几乎可以肯定不会以Windows帐户的身份运行.它将是本地或网络服务帐户之一，或者是内置的ASP.Net用户.

You are logged in using integrated authentication (i.e. your windows logon account) but not specifying it in the connection string. This will try to use the currently logged on user for the ASP.Net application, which will almost certainly not be running as your windows account. It will either be one of the local or network service accounts or a built-in ASP.Net user.

如果实际上是您想要的，那么您还会使用错误的变量名来附加数据库文件，而不是连接到服务器上的实际数据库.

You're also using the wrong variable name to attach the database file, if this is actually what you want rather than connecting to an actual database on the server.

我建议您做的是在实例上启用SQL Server身份验证，创建一个新用户，并根据需要授予它对数据库的权限.

What I suggest you do is enable SQL Server authentication on your instance, create a new user and give it permissions to the database as required.

然后您将使用以下格式登录:

You would then log in using the following format:

Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword;

其中 myServerAddress 是 localhost 或 TOM-PC 的地方；数据库将是出现在Management Studio中的数据库的名称；用户ID 将是您刚刚创建的用户的用户名，而 Password 将是您在创建过程中为其设置的密码.

Where myServerAddress would be localhost or TOM-PC; Database would be the name of the database as it appears in Management Studio; User Id would be the username for the user you just created and Password would be the password you set for them during creation.

如果您确实想附加数据库文件，则您的连接字符串将更改为使用以下格式:

If you do actually want to attach a database file then your connection string changes to use this format:

Server=myServerAddress; AttachDbFilename=C:\Path\To\Database\File.mdf; Database=myDataBase; Trusted_Connection=Yes;

通常，您可以从以下位置获得所需的大多数连接字符串变体: http://www.connectionstrings.com/sql-server/

Typically, you can get most connection string variants that you will need from: http://www.connectionstrings.com/sql-server/

这篇关于连接字符串无效或错误定位服务器/实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！